[Webinar] Streamline your web hosting managementRegister Today


Problem extracting db data to CSV file.

Posted on 2008-02-06
Medium Priority
Last Modified: 2010-04-21

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?


Question by:Homer2040
  • 3
  • 2
LVL 19

Accepted Solution

SteveH_UK earned 2000 total points
ID: 20831587
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.

Author Comment

ID: 20883748
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.

LVL 19

Expert Comment

ID: 20883787
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.

Author Closing Comment

ID: 31428440

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.

LVL 19

Expert Comment

ID: 20958658
Glad to help, and thank you for the points :)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Introduction to Processes

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question