Export all tables to CSV, seperate files

Question

Export all tables to CSV, seperate files

Steve Dingle

SSChasing Mays

Points: 601
More actions
December 11, 2009 at 6:55 am

#214674

Hello All,
As part of a data migration project I need to create individual CSV files for each of the tables in a database (about 100) and I was wondering what the best approach would be. I will need to define the field delimiter (;) and how strings are handles (not delimited with quotes)
I will need to run this many times for testing so am hoping to create an SP to do most of the work for me.
Can anyone point me in the right direction. I should add the choice of exporting to CSV is not mine to make so that anything else (i.e. xml) is not an option
Thanks Much,
Steve
Much Thanks,
Steve Dingle

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'd use SSIS to do it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sturner SSC-Insane Points: 22411 More actions · Answer 2

I wrote a C# .net program that runs from the command line so you can put it into batch file. I takes a query (like: select * from table) as a parameter and optional command line switches to specify delimters and quoted text and so forth. If you have visual studio 2005 I can email you the source code... or the exe if you don't.

The probability of survival is inversely proportional to the angle of arrival.

Del Lee SSCrazy Points: 2781 More actions · Answer 3

Hey Steve (are you the Steve Dingle that used to be a VFP programmer?):

I agree wtih Jack. I think it's pretty easy to create an SSIS package to do what you need to do, and you can run it as needed.

Del Lee

sturner SSC-Insane Points: 22411 More actions · Answer 4

SSIS is okay, if you have it available and want to take the time to set it up. My CSV generator runs on any box against any database or version of SQL server (even express) with nothing to set up.

The probability of survival is inversely proportional to the angle of arrival.

Steve Dingle SSChasing Mays Points: 601 More actions · Answer 5

Thanks for pointer to SSIS - will have to look into it, I do admit I know anything about it.

Sturner - yes I do have access to VS2005, and would appreciate the source if you are willing to share. Email address is: stevenpdingle[at]hotmail[dot]com

Much Thanks,

Steve Dingle

sturner SSC-Insane Points: 22411 More actions · Answer 6

email on the way

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

sturner (12/14/2009)
email on the way

It would be really cool if you'd attach it to this post. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sturner SSC-Insane Points: 22411 More actions · Answer 8

okay, for anyone interested the C# source is attached.

The probability of survival is inversely proportional to the angle of arrival.

RandMan-585256 Old Hand Points: 310 More actions · Answer 9

It would be really cool if you'd attach it to this post. Thanks.

That would be great if you could!

I could use a tool like this.

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers