locked
Bulk insert 0 rows effected RRS feed

  • Question

  • Hi

    I am trying to bulk insert pipe delimited text file below is my text file looks like

    a|b|c|

    when I look at the file it has lot of spaces after the data so when I try to bulk insert it is searching for end of file I believe

    I am inserting through the store procedure.  My bulk insert statement is

    SET@SQL ="BULK INSERT #temp FROM '"+@sourceFile +

                "' WITH (FIELDTERMINATOR='|', ROWTERMINATOR ='"+Char(10)+"', FIRSTROW=2)"

    The result is 0 rows effected.  I don't know why.

    Can anybody help me. 

    Thanks

    Mary Sunish


    • Edited by MaryAbraham Wednesday, December 11, 2013 1:52 AM
    Wednesday, December 11, 2013 1:50 AM

Answers

All replies

  • Try this,

    DELETE #temp
    BULK INSERT #temp
       FROM 'C:\TESTING\testing.txt'
       WITH 
          (
             FIELDTERMINATOR ='|',
             ROWTERMINATOR ='|\n'
          );
    SELECT * FROM #temp


    Regards, RSingh

    Wednesday, December 11, 2013 6:56 AM
  • Try with ROWTERMINATOR AS | + CHAR(10) and check

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Wednesday, December 11, 2013 7:28 AM
    Wednesday, December 11, 2013 7:28 AM
  • Hi,

    You can try this.

    set @Query = 'BULK INSERT #temp FROM '''+ @sourceFile +''' WITH(FIELDTERMINATOR=''|'', ROWTERMINATOR =''\n'', FIRSTROW=2)'

    because in your case its not finding the row terminator,  so put '\n' as row terminator and execute the query..

    Regards

    AtishRG

    Please Mark This As Answer if it helps to solve the issue ..


    AtishRG

    Wednesday, December 11, 2013 1:22 PM
  • You cannot use a ROWTERMINATOR = CHAR(10), it must be a valid character representation.

    Please see:

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

    Wednesday, December 11, 2013 2:03 PM
  • Hi

    I tried that but still says 0 rows affected

    and when I opened the txt file in text pad it has extra spaces after |a||b||c|

    can we rtrim using bulk insert if so how to use it.

    the error now is

    BULK INSERT #temp'\\mrsi-data2\ED_Logs\Temp\mcf_edlog_20131210.txt'WITH (FIELDTERMINATOR='|', ROWTERMINATOR ='|\n')

    Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 1, column 14 (c).

    (1 row(s) affected)

    (1 row(s) affected)

    Wednesday, December 11, 2013 3:21 PM
  • I tried no luck

    Wednesday, December 11, 2013 4:03 PM
  • You need a format file with four fields where you throw the last field away:

    9.0
    4
    1 SQLCHAR 0 0 "|"         1 col1     Latin_General_CI_AS
    2 SQLCHAR 0 0 "|"         2 col2     Latin_General_CI_AS
    3 SQLCHAR 0 0 "|"         3 col3     Latin_General_CI_AS
    4 SQLCHAR 0 0 "\r\n"    0 ""     ""

    If the file is a Unix file, \r\n should be \n.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 11, 2013 4:03 PM
  • You can try adding space before and after pipe row terminator as,

    ROWTERMINATOR =' | \n'

    Refer the examples given at the bottom of this link http://technet.microsoft.com/en-us/library/ms188365(v=sql.110).aspx


    Regards, RSingh


    • Edited by Ch. Rajen Singh Wednesday, December 11, 2013 4:19 PM
    • Marked as answer by MaryAbraham Thursday, December 12, 2013 12:34 AM
    Wednesday, December 11, 2013 4:16 PM
  • Hi Athish,

    I tried  but still it is showing 0 rows effected.

    But if I modify the file the fields getting inserted like if I remove extra spaces in the right and below.

    But having issues with convert date it says conversion out of range

    one of the fields in the text file has only time and no date it throws an error as conversion out of range.

    can the field be

    Registration time

    0000-00-00 15:13:10

    Thanks,

    Mary Abraham

    Thursday, December 12, 2013 12:32 AM
  • Hi

    Thanks I modified the file and it worked now but having date conversion issue.

    Don't know if they accept modifying the file.

    Will have to wait and see.

    Thanks

    Mary Abraham

    Thursday, December 12, 2013 12:34 AM
  • Thanks I modified the file and it worked now but having date conversion issue.

    Don't know if they accept modifying the file.

    Did you attempt the format file I suggested? That should save you from modifying the file.

    As for the date, please post the CREATE TABLE statement for your table and a sample of the data with the problematic dates.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 12, 2013 8:59 AM