Bulk Insert row terminator

Question

Hi

I am trying to insert a .CSV file with sectional division of data in it. i am unable to insert the row, which i want to pick and insert as the row numbers are not taken as in the .CSV file

Please advice if u have any suggestion or is it the case of row terminator not ending the rows

Thank you

Answer 1

Hello,

How exactly is your CSV file looking like? What for parameters have you used for you BULK INSERT command?

As it's best you use a format file to define columns + row delimiter; see Use a Format File to Bulk Import Data (SQL Server)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

You are using FIRSTROW and LASTROW options of BULK INSERT statement?

The only row you cannot skip this way is the header row. What are the values you use for FIRSTROW and LASTROW and what rows do you get inserted?

---

Regards, Dean Savović

Answer 3

Hi,

Could you please share us the sample data and the corresponding bulk insert commands here as we can analysis further?What are the values you use for FIRSTROW and LASTROW and what rows do you get inserted?

Please note that the FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

Please refer to the articles below for more information.

SQL Bulk Insert with FIRSTROW parameter skips the following line
http://stackoverflow.com/questions/1029384/sql-bulk-insert-with-firstrow-parameter-skips-the-following-line
BULK INSERT (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms188365.aspx
Specify Field and Row Terminators (SQL Server)
http://technet.microsoft.com/en-us/library/ms191485.aspx

Thanks

Candy Zhou

Answer 4

Hi,

Could you please provide a screenshot about the sample data located in excel? So we could clearly distinguish the line number. For example:

Thanks

Candy Zhou

Answer 5

http://imgur.com/delete/7ZXPrkITfm7T89s

Answer 6

Hi,

Please check the above link for the screen shot of my sample file.

thank you

Answer 7

Hi,

I need to insert sectional data into data base from a CSV file .

The CSV format is as follows

http://imgur.com/delete/7ZXPrkITfm7T89s

i am getting an error while picking the rows for insertion from the CSV file.

Thank you

Answer 8

http://i.imgur.com/vq2Eqz3.png?1

Answer 9

please check the image for the CSV format

http://i.imgur.com/vq2Eqz3.png?1

Answer 10

Hi,

So we can use the bulk insert commands with

BULK INSERT DatabaseName.SchemaName.TableName
FROM ''Driver:\path\the filename.csv''
WITH (
           FIRSTROW = 7
	  ,LASTROW = 9
	  ,ROWTERMINATOR = ''\n''
)

to bulk insert data properly, don't use FIELDTERMINATOR,because there is no "," for FIELDTERMINATOR.

Thanks

Candy Zhou

Answer 11

Hi,

Does bulk insert check for the rows from the CSV while insertion, or uses any other method for detecting rows 

I have got an issue with row numbers.

Firstrow and lastrow are detected incorrectly while insertion

thank you

Answer 12

Hi,

If i leave the field terminator, i get the below message while insertion.

(0 row(s) affected)

Thank you

Answer 13

Hi,

my file looks like this in csv format.

Section::Test
Column1,Column2
1,2

Section::Test2
Sec2Col1,Sec2Col2,sec3col3,sec4col4,sec5col5
1,2,3,4,2
2,3,4,5,3
4,5,6,5,6

Section::Test3
newCol1,newCol2,newcol3,newcol4
2,3,4,2
3,4,5,3

Section::Test4
Col1,col2,col3
2,2,2
3,4,5
5,4,6

Thank you

Answer 14

Hi,

My CSV file looks like as below. even using the format file is causing the same problems

Section::Test
Column1,Column2
1,2

Section::Test2
Sec2Col1,Sec2Col2,sec3col3,sec4col4,sec5col5
1,2,3,4,2
2,3,4,5,3
4,5,6,5,6

Section::Test3
newCol1,newCol2,newcol3,newcol4
2,3,4,2
3,4,5,3

Section::Test4
Col1,col2,col3
2,2,2
3,4,5
5,4,6

thank you

Answer 15

Praveen,

Please attach the image directly here, some of us are working where opening any Network storage apart from ours is an IS Violation. Plus these things are blocked too. If I try to open this, that would again be a Violation.

Please share the image in the thread.

---

Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.

Answer 16

Hi,

I have problem pasting the image in this window. so i have provided the link.

Thank you

Answer 17

Bulk Insert makes use of 'rowterminator' two differentiate two rows.

Verify  whether proper row terminator exists in first and last row of the CSV file. 

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 18

Go through below link it will help you..

http://technet.microsoft.com/en-us/library/ms178129.aspx

Answer 19

Hi,

i get this error when i use the format file.

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

 

thank you

Answer 20

Hi sarat,

yes the row terminator exists in the first and last row.

my CSV file looks like this

Section::Test
Column1,Column2
1,2

Section::Test2
Sec2Col1,Sec2Col2,sec3col3,sec4col4,sec5col5
1,2,3,4,2
2,3,4,5,3
4,5,6,5,6

Section::Test3
newCol1,newCol2,newcol3,newcol4
2,3,4,2
3,4,5,3

Section::Test4
Col1,col2,col3
2,2,2
3,4,5
5,4,6

thank you

Answer 21

Hi,

After testing, I found that we need format the file in excel like below:

And since ".csv"file is Comma-Saparated values, so when we saved our file, please right click the file and select edit, opened with txt, check the file like below format(The comma amount is the same as the [maximum column amounts-1], if not, please add the comma manually):

Then we can use the commands below to insert the data:

BULK INSERT DatabaseName.SchemaName.TableName
FROM 'Driver:\path\the filename.csv'
WITH (
           FIRSTROW = 7
           ,LASTROW = 9
           ,FIELDTERMINATOR=','
	 ,ROWTERMINATOR = '\n'
);

Thanks

Candy Zhou

Answer 22

Hi,

even if we manually add the extra commas at the end these are being inserted into the database along with the data.

Thank you

Answer 23

Hi,

Have you checked the comma amounts opend in txt format not in csv file(The csv file need to be format as above,which I have provide the screenshots above)? If the comma amounts less one than the maximum column amounts, there is no need to add the extra comma.If not, the comma format is also very important, such as:

Section::Test,,,,
Column1,Column2,,,
1,2,,,(Non-blank line according to the original comma amounts, append extra comma in the end)
,,,,(The blank line needs to add four comma)
Section::Test2,,,,
Sec2Col1,Sec2Col2,sec3col3,sec4col4,sec5col5(The maximum column amounts is 5)
1,2,3,4,2(No need to append)
2,3,4,5,3
4,5,6,5,6

Please check the format again.If you have any concern, please paste your error message here and original picture and the latest result as we can analysis further.

Thanks

Candy Zhou

Answer 24

Hi,

When we bulk insert for 3rd section and fourth section the inserted data also contains the extra commas at the end.

Thank you

Answer 25

Hi,

I have tested in my machine,after inserting for 3rd section and fourth section the inserted data didn't include the comma,however replace with 'Null', please check the picture below:

Could you share your commands here as we can analysis further?

Thanks

Candy Zhou

Answer 26

Hi,

Each of the above section is being inserted into different tables and the commands for all of these are as follows.

bulk insert table_1 from 'filepath' with 
(firstrow=3,lastrow=3,fieldterminator=',',rowterminator='\n' )

bulk insert table_2 from 'filepath' with 
(firstrow=7,lastrow=9,fieldterminator=',',rowterminator='\n' )

bulk insert table_3 from 'filepath' with 
(firstrow=13,lastrow=14,fieldterminator=',',rowterminator='\n' )

bulk insert table_4 from 'filepath' with 
(firstrow=18,lastrow=20,fieldterminator=',',rowterminator='\n' )

Thank you

Answer 27

Hi,

That is very strange, I tried all the steps,after inserting for 3rd section and fourth section the inserted data didn't include the comma, however replace with 'Null', it is normal.Please check the picture below. Please note that comma is just the separated value, and it should not add into the destination columns.

Please check all the steps I have provided above carefully and try again, it should work.

Thanks

Candy Zhou

Answer 28

Hi,

I don't insert all the data into a single table.Each of the section is being inserted into different tables with different number of columns

the issue never arises if the table columns is equal to the row with the highest no of columns in CSV.

Please check.

Thank you

Answer 29

BULK INSERT and bcp require all rows to be the same format.  There is no way to directly import the file you describe using these methods.  Every row is parsed for field delimeters and row delimiters regaurdless of what you specify for the firstrow and lastrow.

You need to use SSIS or rewrite the file with only the rows you want to import.

Answer 30

Hi,

Can i know the exact method, the row numbers are calculated from the CSV file.

thank you

Answer 31

Hi,

As Tom said that BULK INSERT and bcp require all rows to be the same format.  There is no way to directly import the file you describe using these methods. So if you want to insert the data into different tables, we'd better to rewrite the same format rows into separated files. For example,

For two columns format data, we need to save them separately as a single file then we can Bulk insert to deliver them to the corresponding destination table.

Section::Test
Column1,Column2
1,2
For five columns format data, we also need to save them separately as a single file then we can insert them to the corresponding destination table.
Section::Test2
Sec2Col1,Sec2Col2,sec3col3,sec4col4,sec5col5
1,2,3,4,2
2,3,4,5,3
4,5,6,5,6
……

and so on.

Even we use SSIS, we also need to rewrite the file with only the rows you want to import.

Thanks

Candy Zhou