Questions About Exporting And Importing Flatfiles (txt, Csv) In Sql Server 2005

What's the best delimiter to use when exporting data? What's the best way to work with NULL data if you have an INT field in the table?

Solution 1:

I would do whatever bcp with the -c option (character instead of binary) does by default, overriding it only if you see a specific problem with your data. I'd even try to use bcp, if possible.

To do this from MSSQL itself you'd need to enable xp_cmdshell support, which may not be a possibility due to security concerns. But if you want to do it in an external tool, there should be no problem.

Directly answering your questions:

1. The best delimiter is the character that does not appear in your data
2. Use a blank in those columns, any integer will have a character or even a null character

All this and more is why you should use the ready made tool, all the hard choices have been thought out and made already for you.

Solution 2:

1. If you have data with embedded control characters you may want to do a prefix based export using bcp instead of delimited text. The prefix prepends a column with a length indicator and the import reads this rather than looking for the delimiter.

2. For some reason that I cannot fathom and cannot find documentation of, bcp will not import null values into numeric columns, even if they are nullable. You need to make a staging table with all varchar columns and then postprocess the data.