| Author |
Topic |
|
medtech26
Posting Yak Master
169 Posts |
 Posted - 2009-11-19 : 16:32:45
|
| Hello,I'm trying to execute bulk insert from a text file (rrf if it matters) that was create on Linux system. The problem is that Linux uses LF as a line separator and Windows (as well as SQL) recognize CRLF (\n) as line separator (AKA "ROWTERMINATOR"). I was wondering if I can get SQL to recognize LF without editing the text file? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-19 : 16:51:17
|
Hi Tara,I did verify and it shows as LF, also, tried with '\r', '\r\n' and '\n\r' with no success. The only solution that worked was to edit the file and replace all '\n' with '\r\n', then (and only then) SQL allowed me to bulk insert with ROWTERMINATOR='\n' (why '\n' if it's '\r\n'?).I did try:- ROWTERMINATOR ='\n'
- ROWTERMINATOR ='\r'
- ROWTERMINATOR ='\r\n'
- ROWTERMINATOR ='\n\r'
on the original file with no success. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-24 : 16:48:53
|
| Thanks but same like SQL Server Management Studio, no go. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-24 : 18:29:59
|
| Apparently not and currently with SP1. We're installing SP3 at the moment buy wont be able to restart the server 'till midnight. I'll keep you posted.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-24 : 18:32:12
|
| You also need to install it on the client machines since the service packs and hotfixes also update the client tools such as bcp.exe and SSMS. I'd recommend also installing CU5 after SP3 since SP3 has many bugs in it that are fixed in the later cumulative update packages. We went with build 4262 since CU5 had just been released. SP3 only puts you at build 4035.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-24 : 20:31:09
|
| Well, apparently SP3 restart SQL Server without any warnings, so it's there (and still no go). Then we installed CU4 but didn't restart the server since (yet). Should we restart before installing CU5 or go ahead with the new installation and restart once?Current version is 9.00.4226.00.I was wondering if I can send you the file to try on your system (it's a plain text file, about 220KB in size), if so, you can MP me your email address. I'll totally understand if you decline. BTW, only one machine is involved so I guess I'm covered(?). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
LF vs CRLF