none
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

    Friday, September 20, 2013 10:00 AM

Answers

  • 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

    Thursday, September 26, 2013 1:24 AM
  • 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.

    Tuesday, September 24, 2013 3:56 PM
    Moderator

All replies

  • 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]


    Friday, September 20, 2013 10:19 AM
    Moderator
  • 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ć

    Friday, September 20, 2013 1:03 PM
  • 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

    Monday, September 23, 2013 3:34 AM
  • 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

    Monday, September 23, 2013 4:50 AM
  • http://imgur.com/delete/7ZXPrkITfm7T89s
    Monday, September 23, 2013 7:37 AM
  • Hi,

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

    thank you

    Monday, September 23, 2013 7:38 AM
  • 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

    Monday, September 23, 2013 7:43 AM
  • http://i.imgur.com/vq2Eqz3.png?1
    Monday, September 23, 2013 7:46 AM
  • please check the image for the CSV format

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

    Monday, September 23, 2013 7:52 AM
  • 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

    Monday, September 23, 2013 7:56 AM
  • 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

    Monday, September 23, 2013 8:00 AM
  • Hi,

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

    (0 row(s) affected)

    Thank you

    Monday, September 23, 2013 8:06 AM
  • 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

    Monday, September 23, 2013 8:08 AM
  • 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

    Monday, September 23, 2013 9:09 AM
  • 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.

    Monday, September 23, 2013 9:30 AM
  • Hi,

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

    Thank you

    Monday, September 23, 2013 9:50 AM
  • 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.

    Monday, September 23, 2013 11:06 AM
  • Go through below link it will help you..

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

    Monday, September 23, 2013 11:21 AM
  • 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

    Tuesday, September 24, 2013 12:00 AM
  • 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

    Tuesday, September 24, 2013 12:13 AM
  • 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

    Tuesday, September 24, 2013 2:07 AM
  • 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

    Tuesday, September 24, 2013 2:21 AM
  • 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

    Tuesday, September 24, 2013 2:33 AM
  • Hi,

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

    Thank you

    Tuesday, September 24, 2013 3:00 AM
  • 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

    Tuesday, September 24, 2013 6:13 AM
  • 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

    Tuesday, September 24, 2013 7:04 AM
  • 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

    Tuesday, September 24, 2013 8:54 AM
  • 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

    Tuesday, September 24, 2013 9:24 AM
  • 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.

    Tuesday, September 24, 2013 3:56 PM
    Moderator
  • Hi,

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

    thank you

    Wednesday, September 25, 2013 7:11 AM
  • 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

    Thursday, September 26, 2013 1:24 AM