SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert text file into SQL table


Bulk Insert text file into SQL table

Author
Message
Robin35
Robin35
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1290
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
Robin35
Robin35
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1290
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....
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50213 Visits: 10844
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
Robin35
Robin35
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1290
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218562 Visits: 41997
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search