Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
allend2010
Starting Member
28 Posts |
 Posted - 2003-07-10 : 10:30:28
|
| Hello:I am looking for some advice on using bulk insert to import a file into SQL. Currently I am using the default ROWTERMINATOR = '\n' but am getting an error: Server: Msg 4866, Level 17, State 66, Line 1Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.The statement has been terminated.After opening up the file, I saw that it was in Unix format so I then converted to Dos format and reran my sp and it worked ok. My question is this: Is there a way that BULK INSERT may be able to work with the original unix format (using lf) instead of converting to Dos Format (using crlf)as these are big files and it will take a while for a script to convert them. I also tried with '\r' but that did not work either on the unix file and I read somewhere that '\l' is not supported by SQL BULK INSERT (or bcp). Any help you can provide would be greatly apprecieated. Thanks, Al |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 11:07:53
|
| Hmmm.If you have a UNIX file, I take it you're using FTP to transfer it onto a Microsoft network. If so I would select ASCII transfer mode to eliminate the issue.Jonathan{0} |
 |
|
|
allend2010
Starting Member
28 Posts |
Posted - 2003-07-10 : 11:15:04
|
| Hello and thanks for your response. Unfortunetly, it is an FTP encrypted file that has to be downloaded as binary to be decrypted properly. I am now trying to work with getting BULK INSERT to recgonize 0x0A (LF) but am still having no luck :(Thanks,Al |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-10 : 11:23:48
|
| Have you tried DTS? You should be able to import UNIX files that way.Jonathan{0} |
|
|
|
|
|
|
|
|
BUKK INSERT and file formats