Post reply

Bulk Insert text file into SQL table

  • August 6, 2013 at 9:54 am

    #278333

    Hi,

    I have a requirement to load sql server table from text file using the below command....and the file is in the local server....its throwing an error...could someone help me on this please....it urgent...

    BULK INSERT dbatest.dbo.test FROM 'L:\test.log' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = \r')

    Error :

    Msg 4866, Level 16, State 1, Line 1

    The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    Thanks in advance...

  • August 6, 2013 at 10:12 am

    #1639010

    open the file up in an enhanced text editor like NotePad++;

    you'll be able to see what the row terminator actually is.

    i think \r maps to CHAR(10), and \n is both CHAR(13) + CHAR(10);

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 6, 2013 at 10:50 am

    #1639027

    For repeated data loads from text files, I find it better to use a format file to control what goes where. It takes a bit of work to set up, but it also gives you a much finer level of control over what to include, terminator characters and what source data maps to what destination fields.

    Then all you have to do is include a FORMATFILE parameter in your BULK INSERT and it takes care of the rest. If you're interested, here's the MSDN article to get you started. http://msdn.microsoft.com/en-us/library/ms178129.aspx

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 6, 2013 at 11:15 am

    #1639039

    Lowell (8/6/2013)

    open the file up in an enhanced text editor like NotePad++;

    you'll be able to see what the row terminator actually is.

    i think \r maps to CHAR(10), and \n is both CHAR(13) + CHAR(10);

    Thanks a lot lowell for the response....i looked into notepad++....its very wierd...

    please look at the attached screenshot....and please let me know how to proceed....

    thanks

  • August 6, 2013 at 12:51 pm

    #1639075

    i've seen that a few times, that nul character is CHAR(0), and a great example of why you need a little better text editor; plain old windows notepad won't show you that kind of issue.

    i've typically done a find and replace of those chars to fix the files themselves before i import them.

    should those NUL values be treated as commas? spaces? maybe go back to teh source and tell em it's nto a good format?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 7, 2013 at 5:56 am

    #1639326

    Lowell (8/6/2013)

    i've seen that a few times, that nul character is CHAR(0), and a great example of why you need a little better text editor; plain old windows notepad won't show you that kind of issue.

    Lowell - I've seen NotePad++ and know some people who use it, but I prefer UltraEdit. It isn't free, but it does a lot. A couple of examples are that you can program it (with it's own macro language or Javascript), it handles nearly anything from Unicode to EBCDIC and syntax highlighting is available for nearly any language. It's worth a look. And no, I do not work for them. 🙂

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 7, 2013 at 6:11 am

    #1639333

    Ed i agree. Ive used ultraedit and my current favorite is EditPlus. I knew notepad++ was free, so i suggested that over something the OP might have to trialware.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 7, 2013 at 7:33 am

    #1639372

    Lowell (8/7/2013)

    Ed i agree. Ive used ultraedit and my current favorite is EditPlus. I knew notepad++ was free, so i suggested that over something the OP might have to trialware.

    hi Ed and Lowell thanks for your quick response, i have contacted the application guy...he said from perl they generate the text file and its null separated file \0....now sure how to proceed with this.....

    do we have trial version of ultraedit or editplus ?

    thanks

  • August 7, 2013 at 7:36 am

    #1639378

    Ed Wagner (8/6/2013)

    For repeated data loads from text files, I find it better to use a format file to control what goes where. It takes a bit of work to set up, but it also gives you a much finer level of control over what to include, terminator characters and what source data maps to what destination fields.

    Then all you have to do is include a FORMATFILE parameter in your BULK INSERT and it takes care of the rest. If you're interested, here's the MSDN article to get you started. http://msdn.microsoft.com/en-us/library/ms178129.aspx

    Thanks Ed for the suggestion...i have tried it....using the below code....and got the following error..

    format file :

    bcp dbatest.dbo.test format nul -c -t, -f test.Fmt -T

    BULK INSERT dbatest.dbo.test

    FROM 'L:\test.log'

    WITH (FIELDTERMINATOR = '\0',ROWTERMINATOR = '', formatfile = 'L:\test.Fmt')

    error :

    Msg 4823, Level 16, State 1, Line 1

    Cannot bulk load. Invalid column number in the format file "L:\test.Fmt".

    Please let me know your thoughts...thanks

  • August 7, 2013 at 8:44 am

    #1639426

    The error says that you have an invalid column number in your format file. I know it can be a bit confusing at first, but once you really get how this works, it's pretty simple to write and maintain. Here's an example of a format file and how things map from source (RECORD) to destination (ROW).

    <?xml version="1.0"?>

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <RECORD>

    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="13" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    </RECORD>

    <ROW>

    <COLUMN SOURCE="1" NAME="Code" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="3" NAME="ControlNum" xsi:type="SQLVARYCHAR" />

    </ROW>

    </BCPFORMAT>

    In this example, FIELD 1 maps to COLUMN "Code" because the SOURCE = 1. In the COLUMN SOURCE="1" line for ROW, the SOURCE must point to the FIELD ID="1" identifier in RECORD to map correctly. I think this is where your problem lies. The SOURCE isn't just an incremented integer, it's a pointer to the FIELD ID in RECORD.

    To not map an incoming field in RECORD, simply don't include a COLUMN in ROW for it.

    Am I making any sense here? Is this clear at all? It makes perfect sense to me, but I'm the one explaining it. :w00t:

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 7, 2013 at 11:28 am

    #1639491

    Ed Wagner (8/7/2013)

    The error says that you have an invalid column number in your format file. I know it can be a bit confusing at first, but once you really get how this works, it's pretty simple to write and maintain. Here's an example of a format file and how things map from source (RECORD) to destination (ROW).

    <?xml version="1.0"?>

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <RECORD>

    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="13" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS" />

    </RECORD>

    <ROW>

    <COLUMN SOURCE="1" NAME="Code" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="3" NAME="ControlNum" xsi:type="SQLVARYCHAR" />

    </ROW>

    </BCPFORMAT>

    In this example, FIELD 1 maps to COLUMN "Code" because the SOURCE = 1. In the COLUMN SOURCE="1" line for ROW, the SOURCE must point to the FIELD ID="1" identifier in RECORD to map correctly. I think this is where your problem lies. The SOURCE isn't just an incremented integer, it's a pointer to the FIELD ID in RECORD.

    To not map an incoming field in RECORD, simply don't include a COLUMN in ROW for it.

    Am I making any sense here? Is this clear at all? It makes perfect sense to me, but I'm the one explaining it. :w00t:

    Thanks Ed, i have created format file in text format.....but i came to know that its should to create as a null separated format file and use the file in bcp load command.....does this makes sense and could you please let me know how to create null separated format file ?

    Thanks

  • August 8, 2013 at 11:17 am

    #1639908

    Hi, I installed ultraedit trial version....could anyone of you please help me how to bcp the data from text file ( null separated file ) to sql table....

    I really appreciate your time....i'm new to bcp topic...trying to learn but seems like this is bit complicated...

    Thanks in advance....

  • August 8, 2013 at 11:46 am

    #1639921

    I'm not sure about using bcp, but here's how I would do it in a procedure.

    BULK INSERT incoming_data

    FROM 'L:\CurrentData.txt'

    WITH (DATAFILETYPE = 'CHAR',

    FIRSTROW = 2,

    FORMATFILE = 'L:\format.xml',

    MAXERRORS = 0);

    You should be able to use this approach in a stored procedure to load your data from a database job or as needed.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 9, 2013 at 9:21 am

    #1640179

    Ed Wagner (8/8/2013)

    I'm not sure about using bcp, but here's how I would do it in a procedure.

    BULK INSERT incoming_data

    FROM 'L:\CurrentData.txt'

    WITH (DATAFILETYPE = 'CHAR',

    FIRSTROW = 2,

    FORMATFILE = 'L:\format.xml',

    MAXERRORS = 0);

    You should be able to use this approach in a stored procedure to load your data from a database job or as needed.

    Thanks for your help and patience Ed...still getting the same error...using the same code...

    Msg 4823, Level 16, State 1, Line 1

    Cannot bulk load. Invalid column number in the format file "L:\test.Fmt".

    If i dint mention my scenario clearly...Here's the background information...this log file is generated from Perl and its a null separated file....When i contacted the application to use the comma,or tab separated file they replied back that there are some values with commas and rows with tab spaces in the log file....so they opted for null separated file...

    i'm able to create a non-xml format file but when i create an xml format file its throwing the below error

    Command :

    bcp TempImport.dbo.cp_TempImport format nul -c -x -f test.xml -T

    Error : Invalid Ordinal for field 2 in xml format file

    And when i try to bulk copy the text file using non-xml format file....it throws me the following error

    Command :

    bcp TempImport.dbo.cp_TempImport in L:\test.log' -f L:\test.fmt -T

    Error : Incorrect host-column number found in BCP format file

    Also i have checked the ordinal number for table columns and it matches with format file....

    Could you or anyone please help me on this and let me know if you have any questions

    I really appreciate your help....

    Thanks in advance

  • August 9, 2013 at 9:36 pm

    #1640346

    Can you attach the file that you're trying to import without breaking any privacy or proprietary concerns?

    Also, take up a collection to pay someone beat the hell out of the person that used the NULL character as a delimiter. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply