Problem extracting db data to CSV file.

Experts,

I am extracting data from a db table into a CSV file to import to Excel.
I'm using OSQL to create a *.OUT file that's comma delimited, then run a vbscript file to append the current date to the title and change the extension to .csv.

This is working ok, my problem is that I need to somehow handle line breaks within the data.
Some of the fields are text fields with breaqks stored in them and in the csv format cause a new line where there should not be one created.

How can I correct this?

Any ideas?

Thanks.

H.
Homer2040Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveH_UKCommented:
Excel supports embedded newlines in CSV files.  So long as the field is wrapped by double quote characters, so this is correct CSV output.  If OSQL isn't wrapping fields in quotes, then you need to adjust your query accordingly.

See http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm for a good article on how to format CSV correctly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Homer2040Author Commented:
Seems I may be able to use DTS to solve this one.
Will be getting back to the issue next week, so I would like to leave this open and pending until then.

H.
0
SteveH_UKCommented:
Yes, you can use SSIS.  Remember, DTS was the name in 2000.  You may need to install SSIS if it is not already installed.  It can do export to CSV.
0
Homer2040Author Commented:
SteveH_UK,

Sorry this question was open so long.
As I stated earlier, I got retasked and finally got back to this...

Thanks for your patience and input.

I ended up adjusting the query, sort of as you suggested.
I replaced commas and line break/feeds on the fields where they may be entered as I extracted them.

Thanks again for the suggestions.

H.
0
SteveH_UKCommented:
Glad to help, and thank you for the points :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.