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, 05:06 AM

thanks simon, it helped a lot.

dashixiong · 10-18-2012, 01:10 PM

Some conservatives have Coach Factory Outlet pushed that critique further, saying that Mr. Obama’s policies are too costly, often assist the wrong people Louis Vuitton Belts and could have the paradoxical effect of driving up college costs. The dispute turns not just on different Coach Factory Outlet assessments of how policies play out, but on differing philosophical views about the role of government. During Gucci Belts his time in office, Mr. Obama has sharply increased aid to low- and middle-income students, notably through the Pell Grant Coach Factory Outlet program, which grew from $14.6 billion given to 6 million students in 2008, to nearly $40 billion for Coach Factory Outlet almost 10 million students this year. His administration also made it easier to request aid, shortening the Coach Factory Online complex federal application and allowing people to transfer their financial information electronically from the Internal Coach Outlet Online Revenue Service database. But while many education experts laud his efforts, analysts of varying political Coach Outlet Online stripes have also questioned how much impact some of the president’s policies will have, noting that the prices Coach Online Outlet charged by colleges, and student borrowing, continue to climb.But behind the headlines about soaring costs, the Coach Factory Outlet Online reality is more complex and wildly uneven, because a growing number of students receive Coach Outlet Online financial aid, and only relatively high-income families pay those fast-rising sticker prices. Adjusted for Coach Factory Online inflation, the College Board calculates, the average net price changed little over the last decade at private Coach Factory Outlet schools, and rose only modestly at public ones.Defending federal spending, Arne Duncan, the secretary of Hermes Belts education, said that for more than 30 years, college prices had risen even when federal aid had not, leading him to believe Coach Factory Online there was zero correlation.

dashixiong · 10-22-2012, 09:13 AM

You’ve relativelyCoach Outlet recently arrived in New Delhi after living in two of Asia’s other great cities,Coach Outlet Store Online Tokyo and Hong Kong, for several years. Do these cities feel like they’re part of the same continent? Yes, and no. In terms Coach Factory Onlineof infrastructure, they couldn’t be more different. Getting regularCoach Outlet power and water at my house in New Delhi is never a sure thing, even though Coach Purse Outlet OnlineI’m paying the same rent that I paid in Tokyo and almost the same electricity prices. Both Hong Kong and Tokyo are also crowded places,Coach Factory Outlet Online but both cities are incredibly well planned and efficiently run. Efficient is not a word I would use to describe my Coach Bags Outlet Onlineday-to-day life in New Delhi. On the other hand, one thing that I think Hong Kong and New Delhi have in common isCoach Handbags Outlet a shared sense of optimism — a feeling that the best is yet to come. That’s definitely not the feeling you get in Tokyo,Coach Outlet Online or in the U.S. when I go home. It’s a big part of what I find addictive about living and working in this part of the world. You feel like you’re watching the future unfold.

abbot · 12-03-2012, 11:15 AM

this field terminated by ','

dashixiong · 01-29-2013, 12:27 PM

Organizers said Coach Outlet Online was opportune because the battle’s 150-year anniversary is in December, and Fredericksburg Coach Factory Outlet has been preparing to mark the sesquicentennial. in the new agreement is that Coach Outlet Online revolutionary councils from 14 Syrian provinces now each have a representative, though not all live Coach Online Outlet in Syria. The hope is that will bind the coalition to those inside the country. Perhaps Coach Bags Outlet the most important body the new group is expected to form is a Revolutionary Military Council Coach Factory Online to oversee the splintered fighting organizations and to funnel both lethal and nonlethal Coach Factory Outlet military aid to the rebels. It should unite units of the Free Syrian Army, various militias Coach Outlet Store Online and brigades in each city and large groups of defectors. Before the ink was even dry on the Coach Outlet Store final draft, negotiators hoped that it would bring them the antiaircraft missiles they crave to Coach Factory Stores take on the Syrian Air Force. The United States and Britain have offered only Coach Handbags Outlet nonmilitary aid to the uprising. A similar attempt by the Syrian National Council to Coach Factory Store supervise the military never jelled. Organizers said funding was too haphazard. Eventually foreign Coach Factory Online governments like Qatar and Saudi Arabia, which are financing and arming the rebels, found Coach Factory Online their own favorite factions to deal with. Foreign leaders notably including Secretary of State Coach Outlet Hillary Rodham Clinton urged this unification largely so they could coordinate their Coach Factory Outlet efforts and aid through a group of technocrats. Once it receives international recognition, the Coach Outlet Store Online coalition is supposed to establish a temporary Coach Outlet Online military never jelled.