locked
Error while importing data from CSV to SQL table using BULK Insert RRS feed

  • Question

  • Hello All,

    I have an CSV file which have the foloowing data as shown below:

    DETAIL|17|2012-03-15|1|519     --First Row
    DETAIL|18|2012-03-16|2|520 --IMPORTED TO DB
    DETAIL|19|2012-03-17|3|521 --IMPORTED TO DB
    DETAIL|20|2012-03-18|4|522     --Last Row

    When I import the data from CSV file to SQL table the data imported successfully but it imports only 2 rows instead of 4 i.e first row and the last rows are not imported.

    I am using Bulk Insert command in SQL as shown below:

    'BULK INSERT Employee FROM ''' + 'C:\Users\Administrator\Desktop\extract2.csv' + ''' WITH 
    ( FIRSTROW = 1,
    MAXERRORS =0,
    FIELDTERMINATOR = ''|'' ,
    ROWTERMINATOR   =''\n''
    )'

    I has given me lot of grief to find out the problem but not able to identify it.

    Please help me for this issue.

    Thanks in advance.


    Pankaj Kumar Yadav-


    • Edited by Pankaj067 Monday, April 30, 2012 7:27 AM
    Monday, April 30, 2012 7:27 AM

Answers

  • Hi Pankaj Kumar Yadav,
    Regarding to the data you provided, using  Transact-SQL bulk insert I did a test success. The code as below:
    BULK
     INSERT tempdb.dbo.Table_1
    FROM 'C:\\File1.csv'
    WITH
    ( FIELDTERMINATOR ='|',
     ROWTERMINATOR ='\n',
    firstrow = 1,
    lastrow = 4
     )

    If you still have problem, you can try to use a tool which can handle this, like SSIS which is much easier.

    Regards, Amber zhang

    Tuesday, May 1, 2012 5:04 AM

All replies

  • can you try to unify spacing?

    not spacing between last field and comment is different between rows 1 and 4 and rows 2 and 3


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Monday, April 30, 2012 7:36 AM
  • Actually data is 

    DETAIL|17|2012-03-15|1|519   
    DETAIL|18|2012-03-16|2|520 
    DETAIL|19|2012-03-17|3|521
    DETAIL|20|2012-03-18|4|522

    The comments are just for reference.........


    Pankaj Kumar Yadav-

    Monday, April 30, 2012 9:00 AM
  • Hi Pankaj Kumar Yadav,
    Regarding to the data you provided, using  Transact-SQL bulk insert I did a test success. The code as below:
    BULK
     INSERT tempdb.dbo.Table_1
    FROM 'C:\\File1.csv'
    WITH
    ( FIELDTERMINATOR ='|',
     ROWTERMINATOR ='\n',
    firstrow = 1,
    lastrow = 4
     )

    If you still have problem, you can try to use a tool which can handle this, like SSIS which is much easier.

    Regards, Amber zhang

    Tuesday, May 1, 2012 5:04 AM