I am trying to use the MS SQL Server 2005 Import/Export tool to export a table so I can import it into another database for archival. One of the columns is text so if I export as comma-delimited, when I try to import it into the archive table, it doesn't work correctly for rows with commas in that field. What options should I choose to ensure my import will work correctly?
A:
Use quotes as text qualifier
Text qualifier: Type the text qualifier to use. For example, you can specify that each text column be surrounded with quotation marks.
Abel
2009-07-24 14:30:29
I tried using quotes as text qualifier too but most of my rows have a " in it anyways. From what I can tell, the exported data can't escape the existing "s
qntmfred
2009-07-24 14:46:56
+1
A:
I never use the comma delimter unless the client requires it. Try using | as a delimter. YOu can also use the text qualifier if need be.
HLGEM
2009-07-24 14:41:34
this will have the same problem as using comma-delimited. if any row has a | in it, it won't import correctly. this case is much less common than having a , but still common enough to not work correctly
qntmfred
2009-07-24 14:45:48
I do hundreds of imports a week from 50 or sixty different clients and only a couple of times have we had data with a | in it. In those cases we used ~ as the delimter or added the text qualifier. And in one case we asked them to fix the one offending record in a 20 million record file to avoid having to change the process. If you are exporting, you have control of the data and can find the records with a | and fix them to use some other character instead before you do the export. Much harder to do this with comma because of the meaning of the data when commas are used vice when |s are used.
HLGEM
2009-07-24 16:53:07
A:
I just found this, and it's describing my exact problem, that commas are breaking up the data when I export from MSSQL to a text file. Actually, it's a CSV file, if that makes any difference. But even when I change the Column Delimeter to a | or anything, when I look at the file afterwards, in each instance in my text field where a comma exists, the CSV file splits it into a whole new column. The new delimeters are there for other fields, but why can't I make commas not break everything?
Thanks,
-Rick
Rick
2009-11-10 16:37:09
A:
Over a year later, I now have an ideal solution to my data export needs, thanks to http://stackoverflow.com/questions/20363/
bcp "SELECT * FROM CustomerTable" queryout "c:\temp\CustomerTable.bcp" -N -S SOURCESERVERNAME -T
bcp TargetDatabaseTable in "c:\temp\CustomerTable.bcp" -N -S TARGETSERVERNAME -T -E
- -N use native types
- -T use the trusted connection
- -S ServerName
- -E Keep identity values specified in the data file
Very quick and easy to embed within code.
qntmfred
2010-09-28 17:57:31