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 Tuesday, August 6, 2013 9:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:54 AM
Points: 95, Visits: 793
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...
Post #1481418
Posted Tuesday, August 6, 2013 10:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1481426
Posted Tuesday, August 6, 2013 10:50 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 4,611, Visits: 4,067
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
Post #1481449
Posted Tuesday, August 6, 2013 11:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:54 AM
Points: 95, Visits: 793
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



  Post Attachments 
screenshot.jpg (13 views, 389.76 KB)
Post #1481462
Posted Tuesday, August 6, 2013 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1481503
Posted Wednesday, August 7, 2013 5:56 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 4,611, Visits: 4,067
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
Post #1481802
Posted Wednesday, August 7, 2013 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1481811
Posted Wednesday, August 7, 2013 7:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:54 AM
Points: 95, Visits: 793
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
Post #1481855
Posted Wednesday, August 7, 2013 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:54 AM
Points: 95, Visits: 793
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

Post #1481861
Posted Wednesday, August 7, 2013 8:44 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 4,611, Visits: 4,067
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1481913
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse