Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Not reading the first line of CSV file during bulk insert operation in SQl Server 2008

Answered Not reading the first line of CSV file during bulk insert operation in SQl Server 2008

  • Wednesday, May 02, 2012 5:31 AM
     
     
    Sign In to Vote
    0

    Hello all,

    I have a csv file (pipe {|} delimited) which has to be written in the database table SQl Server. The format of the file when seen from Notepad++ looks like:

    EXTRACT|2012-03-15|11|1097.2800{CR}{LF}
    DETAIL|17|2012-03-15|1|519|Savident|Tracy|L|CA{CR}{LF}
    DETAIL|17|2012-03-15|2|519|Savident|Tracy|L|CA{CR}{LF}
    DETAIL|17|2012-03-15|3|519|Savident|Tracy|L|CA{CR}{LF}
    DETAIL|17|2012-03-15|4|519|Savident|Tracy|L|CA{CR}{LF}
    DETAIL|17|2012-03-15|5|519|Savident|Tracy|L|CA{CR}{LF}

    And my BULK INSERT query is as follows:

    EXEC ( 'BULK INSERT TMP_SAE FROM ''' + 'C:\Users\Administrator\Desktop\Stored procedure\extract2.csv' + ''' WITH 
    ( FIRSTROW = 2,
    MAXERRORS =1000,
    FIELDTERMINATOR = ''|'' ,
    ROWTERMINATOR   =''\n''
    )'
    )

    Task is to write all the data rows except the EXTRACT row (i.e except "EXTRACT|2012-03-15|11|1097.2800 ") to the database table of SQl Server.

    But when i execute my BULK Insert query it also quit the first DETAIL row and write only 4 rows to the database table instead of 5. Even their is end of line delimiter (i.e {CR}{LF}) in each row.

    Please suggest an appropriate method or solution with respective to this problem.

    Thanks in advance.

    Pankaj Kumar Yadav-

     

All Replies

  • Wednesday, May 02, 2012 5:32 AM
     
      Has Code
    Sign In to Vote
    0

    may be the highlighted in bold is the problem ?

    EXEC ( 'BULK INSERT TMP_SAE FROM ''' + 'C:\Users\Administrator\Desktop\Stored procedure\extract2.csv' + ''' WITH 
( FIRSTROW = 2,
MAXERRORS =1000,
FIELDTERMINATOR = ''|'' ,
ROWTERMINATOR   =''\n''
)'
)

    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.

     
  • Wednesday, May 02, 2012 5:33 AM
     
     
    Sign In to Vote
    0
    So, what could be the appropriate solution to this issue?

    Pankaj Kumar Yadav-


    • Edited by Pankaj067 Wednesday, May 02, 2012 5:34 AM
    •  
     
  • Wednesday, May 02, 2012 5:36 AM
     
     
    Sign In to Vote
    0
    could you change it to 1

    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.

     
  • Wednesday, May 02, 2012 5:41 AM
     
     
    Sign In to Vote
    0

    Actually  I have tried it and change the FIRSTROW = 1,

    but then I get the following error as shown below:

    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (BATCHDATE).

    I think this error was occur due to datatype mismatch in the first row(i.e "EXTRACT|2012-03-15|11|1097.2800 " ) but as i have mentioned I have to skip the first row and insert all the rest rows to the  database table.

    Pankaj Kumar Yadav-


    • Edited by Pankaj067 Wednesday, May 02, 2012 5:41 AM
    •  
     
  • Wednesday, May 02, 2012 5:50 AM
     
     
    Sign In to Vote
    1

    Hi Pankaj

    this is because you are having some problem with your first row. If you can see your records, few fields are missing which are  disturbing the column sequence and resulting in datatype mismatch.


    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com

     
  • Wednesday, May 02, 2012 5:54 AM
     
     
    Sign In to Vote
    0

    Hi Pankaj

    this is because you are having some problem with your first row. If you can see your records, few fields are missing which are  disturbing the column sequence and resulting in datatype mismatch.

    As per mentioned above i have to skip the first row and insert rest of the record to the database table , the schema of the database table is appropriate as for all rows starting with "DETAILS" and for the "EXTRACT" i have to skip this row and not to be inserted into the database.

    Any workaround?

    Pankaj Kumar Yadav-

     
  • Wednesday, May 02, 2012 6:05 AM
     
     
    Sign In to Vote
    0
    If you are generating the CSV file through automation why don't you let automation trim the first row ? 

    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.

     
  • Wednesday, May 02, 2012 6:08 AM
     
     
    Sign In to Vote
    0

    Actually the first row contains the details for this extract file in which it include the information of DateOfCreation, NumberofRecord etc..

    So we cannot delete this row.

    Is their any other workaround regarding this issue?

    Pankaj Kumar Yadav-

     
  • Wednesday, May 02, 2012 6:13 AM
     
     Answered
    Sign In to Vote
    0
    You could write simple batch file that generate copy from the file without the first row so it can be bulk inserted in SQL server, let me now if you want sample for the batch file parser.

    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.

    • Marked As Answer by Pankaj067 Wednesday, May 02, 2012 11:31 AM
    •  
     
  • Wednesday, May 02, 2012 6:21 AM
     
     
    Sign In to Vote
    0
    Yes, if you could provide the sample then it would be great.

    Pankaj Kumar Yadav-

     
  • Wednesday, May 02, 2012 6:27 AM
     
     
    Sign In to Vote
    0

    http://www.mrexcel.com/forum/showthread.php?t=68806

    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.

     
  • Wednesday, May 02, 2012 6:31 AM
     
     
    Sign In to Vote
    1

    We dont have filter facility in BULK Insert Command based on any creteria. You Probably need to do following

    Create A Table With Similar Structure as TMP_SAE say TMP_SAE_Common and keep all field  DataType as SQL_Variant

    Now use this table for Bulk Insert and once you have got data into this table you can easily Filter and extract Data.

    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com

     
  • Wednesday, May 02, 2012 11:32 AM
     
     
    Sign In to Vote
    0

    Solve the problem through SSIS by  skipping the first row and then writing to the database table.

    Thanks Ahmed Ibrahim  for your effort......

    Pankaj Kumar Yadav-




    • Edited by Pankaj067 Wednesday, May 02, 2012 11:33 AM
    • Edited by Pankaj067 Wednesday, May 02, 2012 11:33 AM
    • Edited by Pankaj067 Wednesday, May 02, 2012 11:34 AM
    •