April 14, 2010 at 2:17 am
Hi, Wondering if somebody is able to help me. I am sending a text file via ftp as ASCII and trying to use BULK IMPORT via SQL to import the data into SQL.
I'm struggling with the ROW TERMINATOR. I have opened the file in a Hex Viewer and I see that the line feeds are displayed as 0a 0d. When I use this below script for my BULK INSERT it skips some line feeds.
BULK INSERT temp
FROM 'D:\test\Backup\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '')
GO
There is quite a lot of data, instead of having each record on each line, i'm getting some lines with 2 or 3 rows of data onto one line. Column 1 looks correct, it shows the first field of each line, but as I continue through the columns I see that its added another line.
Has anybody come across this before?
Thanks and really appreciate your help on this matter.
rosmiq
April 14, 2010 at 5:23 am
Try using single quotes. Also i think your row terminator is new line character followed by comma (,). I added comma in the row terminator. check that one also.
BULK INSERT temp3 FROM file_name1 WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = ',',FIRSTROW = 2)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 14, 2010 at 5:28 am
Hi I tried that I got the below error:
Msg 4832, Level 16, State 1, Line 181
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 181
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 181
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I have tried '' and it looks like it worked but when i went to look at the data i saw that rows were following on from each other, i.e. 3 rows on 1 row. The more columns I added the more the data was stretched out.
I should have over 100000 lines of data but instead I was getting only 14000, the more columns I added the less rows I was getting.
'\r' only returned 1 row
April 14, 2010 at 5:29 am
noticed above it doesn't seem to be showing my text properly. \ n is what I was trying to say not " like it shows above.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply