CODEWITHSUNDEEP
sqlsql-serverexportexport-to-csv
Sal
Sal

Reputation: 305

SQL Server Export to flat file has unexpected row breaks

I have an interesting issue. I have a table that has 300,000 rows records in it. When I export to a flat file, regardless of delimiter, it does indeed say it's exporting 300,000 rows. However when I open in a text editor or try to re-import that flat file into another SQL Server table.. it added rows... like 50k worth.

Upon reviewing the flatfile in a text editor like Pilot Edit, I see the rows are not consistent. There is a weird row break that's happening.. and thus when I import into another servers table, it imports a broken file... with 50k extra rows.

Whats going wrong with the export?? How can I make it simply export exactly what is in the tables.. data that's all nice and clean ?

Also it should be noted I have the row delimiter set to {CR}{LF} and also set it to a pipe just to try.. same results...

Upvotes: 0

Views: 359

Answers (1)

Sal
Sal

Reputation: 305

solved it. had a hidden carriage return in a column 

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(COLUMN NAME, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

Upvotes: 1

Related Questions