Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Bulk Insert text file into SQL table Expand / Collapse
Author
Message
Posted Wednesday, August 7, 2013 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 95, Visits: 789
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.


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
Post #1481987
Posted Thursday, August 8, 2013 11:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 95, Visits: 789
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....
Post #1482484
Posted Thursday, August 8, 2013 11:46 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 4,451, Visits: 3,901
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
Post #1482498
Posted Friday, August 9, 2013 9:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 95, Visits: 789
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




Post #1482811
Posted Friday, August 9, 2013 9:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483004
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse