Category
Report Builder
end of line for csv output?
Answered
I'm trying to import a csv file that was output from YF into a sql table through MS SQL Bulk Insert.
Typically for text files, I specify the ROWTERMINATOR='\n'
However, that's not working for csv files output from YF. When opening in Notepad, there is no line break - just looks like one long string. Textpad, on the other hand (as well as Excel) opens the file and displays lines accordingly.
Any ideas on what to specify as the line terminator? I've also tried \r
Thanks very much for any help!
-Steve
The same question
Hi Steve,
In my tests, the csv exports use a line feed ascii character to denote end of line rather than either \n or \r. I am not terribly familiar with the bulk insert method for MS SQL, but I would imagine that you could get away with not specifying a line break character.
Is that an option here?
Regards,
Nathan
Hi Steve,
In my tests, the csv exports use a line feed ascii character to denote end of line rather than either \n or \r. I am not terribly familiar with the bulk insert method for MS SQL, but I would imagine that you could get away with not specifying a line break character.
Is that an option here?
Regards,
Nathan
Thanks for the thought Nathan. I tried leaving out the ROWTERMINATOR and run into the same trouble. I've also tried '\r\n' as well. I'm pretty confident that the issue is however the end of line is represented. I found that if I opened up the YF csv output in Excel and simply saved the file (as csv of course) then I was able to do my sql BULK INSERT. So Excel is able to correctly read in the row terminator (whatever that is from YF) and then set the default row terminator, '\n'.
I'd love to know how to specify it myself without having to open/save the csv through Excel.
Thanks again for the response,
Steve
Thanks for the thought Nathan. I tried leaving out the ROWTERMINATOR and run into the same trouble. I've also tried '\r\n' as well. I'm pretty confident that the issue is however the end of line is represented. I found that if I opened up the YF csv output in Excel and simply saved the file (as csv of course) then I was able to do my sql BULK INSERT. So Excel is able to correctly read in the row terminator (whatever that is from YF) and then set the default row terminator, '\n'.
I'd love to know how to specify it myself without having to open/save the csv through Excel.
Thanks again for the response,
Steve
Hi Steve,
How about trying:
ROWTERMINATOR = '0x0a'
It seems other people have this problem as well with the bulk insert:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cc0d3f79-3b1e-4613-881e-ba54289a5f02/bulk-insert-row-terminator-issues?forum=transactsql
Nathan
Hi Steve,
How about trying:
ROWTERMINATOR = '0x0a'
It seems other people have this problem as well with the bulk insert:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cc0d3f79-3b1e-4613-881e-ba54289a5f02/bulk-insert-row-terminator-issues?forum=transactsql
Nathan
Fantastic!! '0x0a' did the trick for me. Thanks so much for hunting that down!!
Fantastic!! '0x0a' did the trick for me. Thanks so much for hunting that down!!
Hi Steve,
Glad to hear it worked, and no problem! I am going to close this ticket for now, but let me know if you run into any other problems.
Also, if you have a few seconds we are doing Closed Request Surveys this month.
Regards, Nathan
Hi Steve,
Glad to hear it worked, and no problem! I am going to close this ticket for now, but let me know if you run into any other problems.
Also, if you have a few seconds we are doing Closed Request Surveys this month.
Regards, Nathan
Replies have been locked on this page!