Export to csv file crashes EM

Question

Export to csv file crashes EM

juliekenny

SSCommitted

Points: 1558
More actions
July 22, 2004 at 10:38 am

#60202

I am trying to export a table from SQL 2000 to a csv (required by an external application). When I try to construct the transform and get to the part where the csv file should get defined by the transform the whole thing crashes out of EM.
I have tried using bcp but this seemed complicated to set up for not very many records. I haven't had much experience of bcp though.
What is the best way to do this job? Although there aren't many records there are a lot of columns and they are likely to change a few times before I'm done.
Julie

Viewing 8 posts - 1 through 7 (of 7 total)

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

Glen G SSC Rookie Points: 36 More actions · Answer 1

Hi,

I've just been having the same type of problem. I got around the problem by using the DTS Export Wizard, specifying the Destination = Text File (and the file name as the .csv file you want to create), and then you get to specify the csv file format you want, with the right delimiters etc). If you then schedule the DTS package for later execution, it is saved, and you can run it again.

When you edit the saved package, using the design package module, I was getting a strange error - on the transformations tab, but you can ignore this, and cancel out - any changes you make to the Source or Destination tabs seem to be saved.

I dont know if this will help with what you want?

theBainster Old Hand Points: 343 More actions · Answer 2

I just had to do this recently. Don't let bcp scare you. It's super fast.

Try pasting this into QA:

EXEC master..xp_cmdshell 'bcp DataBaseName..tblMyTablename out d:\Output.txt -c -q -t","', NO_OUTPUT

Change your database name and table name to match. You can even replace the table name with a view.

Bryan

juliekenny SSCommitted Points: 1558 More actions · Answer 3

Thanks for this. The output is great but I need column headings to be on the first line and a couple of fields hold commas so I think I will need to get these fields quoted (unless the target people will take pipes as separators).

Any ideas on how I achieve this?

Julie

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

Are you running sp3?

I recall a bug in SP3 that caused SQL to bomb out when trying to define columns in an export. Go to sp3a and you shouldn't get the same problem.

Shamless self promotion - read my blog http://sirsql.net

juliekenny SSCommitted Points: 1558 More actions · Answer 5

I'm fairly certain that we went straight to SP3a on all servers.

theBainster Old Hand Points: 343 More actions · Answer 6

Julie,

Another quick and dirty way to do this is to change the output type in Query Analyzer from "Results to Grid" to "Results to file." It's an icon on the toolbar of QA.

Then paste this into QA:

set nocount on

select * from myTableOrView

This will give you the column names. You can change the delimiter by choosing options form the QA toolbar (to something such as pipes), then the Results tab. Don't forget to change the output extention to something other than .rpt when you execute.

However, if you want quotes around your data, I can only think of DTS.

Bryan

juliekenny SSCommitted Points: 1558 More actions · Answer 7

Unfortunately this solution must be automatic so QA is not an answer, fortunately there are only a couple of fields that need quotes so I've managed to include them as part of the string.

The headers I'm just keeping in a flat file and appending the data - I forgot that sometimes the answer is just too simple to see.

Julie