csv export has line returns from a column on one server not the over

Question

Post reply

csv export has line returns from a column on one server not the over

Lee Hopkins

SSC Eights!

Points: 960
More actions
August 5, 2021 at 7:22 pm

#3917259
Ok i have a test server and a production server. (I did not set up) . On the test server when I export the query results to a csv vile one of the column (workinst) has line returns so the sheet is messed up with line returns and the following columns are no longer on the first row.
but the production server when I export to csv each record is on one row including the col (workinst)
is there a setting in SQL causing this??
The server acting funky is 14 where my prod is 16
- This topic was modified 3 years, 3 months ago by Lee Hopkins.

Viewing 6 posts - 1 through 5 (of 5 total)

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

how are you exporting the data?

and where/how are you opening those files?

line returns on a valid csv file are not a problem it the software reading that file correctly handles fully conformed csv files.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

What is the type of Source file ? Direct export from query results ?

Is the data the same ? Maybe different results, cause different CR-LF ? Something like an embedded comma in the data.

This reply was modified 3 years, 3 months ago by homebrew01.

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 3

I have done to methods of getting the data from SQL

Right clicking on the grid and copy with headers then pasted in to excel.
Right Clicking on the grid and save as csv.

The Sql 2014 results is messed up with the line feeds in one of the columns, But if i do 1 or 2 in the Sql 2016 it is formatted correctly.

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 4

I am using excel to open or past the sql data.

There are line feeds in one of the columns causing the issue.

I have found a work around by doing a

replace(replace(WorkInst, char(10), ''), char(13), '')

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

are you using SSMS on your own PC to do this or rdping to the server and using the SSMS on each individual server?

if on the server then the issue is likely to be related to different settings on SSMS. or potentially verison of SSMS as I don't know if this is available in all of them.

go to options -> Query Results -> Sql Server -> Results to grid.

option "regain CR/LF on copy or save". If available on both SSMS's then in one it is likely unticked.

But this is not the way to generate CSV files - so this is likely creating invalid CSV and hence your problem.

And copy and paste has the same issue.

Now whether or not those cr/lf are important only you can now - but by removing them you are changing the data so verify if it is valid to do so before using the data like that.