When SQL Server 2008 query results are exported to CSV file extra rows are added

0
votes

When I am exporting my query results from SQL Server 2008 to CSV or Tab Delimited txt format I always end up seeing extra records (that are not blank) when I open the exported file in Excel or import it into Access.

The SQL query results return 116623 rows

but when i export to csv and open with excel i see 116640 records. I tried importing the csv file into access and i also see extra records.

The weird thing is that if i add up the totals in excel up to row 116623 I have the correct totals meaning i have the right data up to that point but the extra 17 records after that are bad data which i don't know how it is being added.

Does anyone know what might be causing these extra records/rows to appear at the end of my CSV file?

The way i am exporting is by right clicking on the results and export to csv (comma delimited) or txt (tab delimited) files and both are causing the problem.

2
sql-server-2008csvexport
How are you exporting? SSMS? - SQLMason
The way i am exporting is by right clicking on the results and export to csv (comma delimited) or txt (tab delimited) files and both are causing the problem - Juan Velez
I just noticed something strange. I tried exporting by using the export wizard this time and the wizard said it exported 116623 records (which is the correct amount of records) but then when i opened the file in excel i see 116786 rows so I am thinking it must be a problem with excel maybe?? - Juan Velez
@JuanVelez do the extra records at the end exist in your source data set? - JNK
@JuanVelez as HLGEM has mentioned, you have some odd characters in your data which is making extra lines in Excel. - SQLMason

2 Answers

3
votes

I would bet that in those huge number of rows you have some data that had a carriage return internal to the record (such as an address record that includes a line break). look for rows that have empty data in some fo the columns you would expect data in. I ususally reimport the file to a work table (with an identity so you can identify which rows are near the bad ones) and then run queries on it to find the ones that are bad.

0
votes

Actually, there is a bug in the export results as feature. After exporting the results, open csv file in a Hex editor and look up unique key of last record. You will find it towards the end of the file. Find the OD OA for that record and delete everything else that follows. It's not Excel or Access. For some reason SQL just can't export a csv without corrupting the end of the file.