SQL comma problem

vpopp · 12-04-2009, 12:48 AM

I am new to SQL and seem to be having a major problem. I have a description field in my sql database that contains at least one common on SOME of the items. A few lines may look as follows:
(Item) (Descr) (UOM)
|1234| |cath, 12*2| |ea|
|1145| |cath 12*2| |ea|
|2235| |needle, 10*2| |ea|

When I export as a txt or scv file, in comma delimited (my only option I am told) some of the descriptions get separated out into separate columns. In the above example, the first and third description. Thus, the data is all over the place. I have 1.18 million lines so manual correction is tough.
Is there somehting that I can write into the script to tell the query to eliminate or change the comma to another character in the Description field??? I don't want to change it in the database, only for my saved file for export.

Help is truly appreciated.
van.popp@yahoo.com

Thanks

maeltar · 12-04-2009, 06:53 AM

What you can use is :

Code:

fields terminated by ','

change the comma to anything you want that is not used in the description..

e.g.

Code:

fields terminated by '"'

Not 100% sure if it needs to be escaped, maybe someone will verify that..

Am guessing you are using something like

Code:

select * from <table name> 
order by by item into outfile '/<writeable path>/outfile.csv' 
fields terminated by '"';

vpopp · 12-04-2009, 01:01 PM

Thanks Maeltar,

I am nt sure how to incorp your code into my script. Here is my script. Your help is TRULY appreciated.

SELECT
dbo.ITEM.ITEM_NO
,dbo.ITEM.DESCR
,dbo.ITEM.CTLG_NO
,dbo.ITEM.COMDTY_CD
,dbo.ITEM.STAT
,dbo.ITEM_VEND.ORDER_UM_CD
,dbo.ITEM_VEND.CORP_ID
,dbo.ITEM_VEND.CONTRACT_ID
,dbo.ITEM_VEND_PKG.PRICE
,dbo.MFR.NAME

FROM dbo.ITEM INNER JOIN
dbo.ITEM_VEND ON dbo.ITEM.ITEM_ID = dbo.ITEM_VEND.ITEM_ID AND dbo.ITEM.ITEM_IDB = dbo.ITEM_VEND.ITEM_IDB INNER JOIN
dbo.ITEM_VEND_PKG ON dbo.ITEM_VEND.ITEM_VEND_ID = dbo.ITEM_VEND_PKG.ITEM_VEND_ID AND
dbo.ITEM_VEND.ITEM_VEND_IDB = dbo.ITEM_VEND_PKG.ITEM_VEND_IDB INNER JOIN
dbo.MFR ON dbo.ITEM.MFR_ID = dbo.MFR.MFR_ID AND dbo.ITEM.MFR_IDB = dbo.MFR.MFR_IDB

WHERE ITEM.STAT=1

maeltar · 12-04-2009, 01:39 PM

Simple really, just add it to the end...

Code:

SELECT dbo.ITEM.ITEM_NO ,dbo.ITEM.DESCR ,dbo.ITEM.CTLG_NO ,dbo.ITEM.COMDTY_CD ,dbo.ITEM.STAT ,dbo.ITEM_VEND.ORDER_UM_CD ,dbo.ITEM_VEND.CORP_ID ,dbo.ITEM_VEND.CONTRACT_ID ,dbo.ITEM_VEND_PKG.PRICE ,dbo.MFR.NAME 

FROM dbo.ITEM INNER JOIN
dbo.ITEM_VEND ON dbo.ITEM.ITEM_ID = dbo.ITEM_VEND.ITEM_ID AND dbo.ITEM.ITEM_IDB = dbo.ITEM_VEND.ITEM_IDB INNER JOIN dbo.ITEM_VEND_PKG ON dbo.ITEM_VEND.ITEM_VEND_ID = dbo.ITEM_VEND_PKG.ITEM_VEND_ID AND dbo.ITEM_VEND.ITEM_VEND_IDB =  dbo.ITEM_VEND_PKG.ITEM_VEND_IDB INNER JOIN dbo.MFR ON dbo.ITEM.MFR_ID = dbo.MFR.MFR_ID AND dbo.ITEM.MFR_IDB = dbo.MFR.MFR_IDB

WHERE ITEM.STAT=1

into outfile '/<writeable path>/outfile.csv' fields terminated by '"';

Note.. my previous post had one or 2 typo's in !!

maeltar · 12-04-2009, 01:46 PM

an alternative to using visible delimiter you can use a tab, which excel recognises...

replace

Code:

fields terminated by '"'

With

Code:

fields terminated by '\t'

vpopp · 12-04-2009, 05:27 PM

GREAT - Thanks Simon. Last Issue can you help???

How do I exclude the Time from the date fields below? Can you help me with not only the script, but with where to place it?

PO_DATE REC_DATE
2008-07-01 00:00:00 2008-05-15 17:10:05

TRULY APPRECIATED!!!

maeltar · 12-04-2009, 05:59 PM

no probs at all...

What I quickly did was create a database with a table in it holding an id(int), timestuff(datetime) and randomjunk(varchar) ..

Threw some junk data in and used a query to format it..

Code:

mysql> select * from datetest;
+----+---------------------+---------------------+
| id | timestuff           | randomjunk          |
+----+---------------------+---------------------+
|  2 | 2009-12-04 00:00:00 | Random Junk in here |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

Above you can see the date/Time in a standard format..

Below, the field is reformatted, but the actual data is not changed..

Code:

mysql> select id, date_format(timestuff, '%d/%m/%Y'), randomjunk from datetest;
+----+------------------------------------+---------------------+
| id | date_format(timestuff, '%d/%m/%Y') | randomjunk          |
+----+------------------------------------+---------------------+
|  2 | 04/12/2009                         | Random Junk in here |
+----+------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>

You could reformat the date in php using something like:

PHP Code:


			


date("d:m:Y", strtotime($var_name_holding_date/time))

vpopp · 12-05-2009, 11:01 AM

Simon,

The code line that you wrote, this can be added to the end of the query? Will this keep the format (date only) so that it can be exported to a csv file?

Thanks so much for your help!

Van

maeltar · 12-05-2009, 01:04 PM

Hey Van,
It depends how you are exporting the data into a csv, is it with a query, or is a query extracting the data then some php to create the csv ???

If you are using a sql query to create the csv then you could use..

Code:

date_format(PO_DATE, '%d/%m/%Y'), date_format(REC_DATE, '%d/%m/%Y'), etc etc etc

If using php to create the csv then use the other method

PHP Code:


			
date("d:m:Y", strtotime($var_name_holding_date/time))

Without knowing more thats the best I can do really...

vpopp · 12-05-2009, 01:09 PM

I am exporting directly from Microsoft SQL Server Studio 2005 - Selecting the option, to file as csv.

Looks like you gave me the code below. I will give this a shot when I return to work on Monday.

Thanks!

Quote:

Originally Posted by maeltar

Hey Van,
It depends how you are exporting the data into a csv, is it with a query, or is a query extracting the data then some php to create the csv ???

If you are using a sql query to create the csv then you could use..

Code:

date_format(PO_DATE, '%d/%m/%Y'), date_format(REC_DATE, '%d/%m/%Y'), etc etc etc

If using php to create the csv then use the other method

PHP Code:


			
date("d:m:Y", strtotime($var_name_holding_date/time))

Without knowing more thats the best I can do really...

maeltar · 12-05-2009, 01:17 PM

Ahhh MSSQL !! not sure if date_format() works with that !!!

I'm mysql only am affraid, but will have a look into it

maeltar · 12-05-2009, 01:18 PM

Looks like I have found you a solution..

http://www.databasejournal.com/featu...nt-Formats.htm

infonama · 06-03-2010, 06:06 AM

thanks simon, it helped a lot.