IMPORT DATA INTO A TABLE FROM A FLAT FILE

Question

IMPORT DATA INTO A TABLE FROM A FLAT FILE

Ronnie Jones

Hall of Fame

Points: 3377
More actions
March 27, 2012 at 2:38 pm

#253195

Hello All,
I’m having a problem importing the data from a .dat flat file that I receive via FTP.
I’m using the following statement to create a table and then import the contents of the .dat file:
USE DATA_COLLECTION --database
GO
CREATE TABLE TEMP_miscid_sponsor_xref
(
[miscid] VARCHAR(128)
,[sponsor_attuid] VARCHAR(128)
,[create_date] DATE
,[status_chge_date] DATE
)
GO
BULK
INSERT TEMP_miscid_sponsor_xref
FROM 'E:\FTP2\miscid_sponsor_xref.dat'
WITH
(
FIELDTERMINATOR = '|'
,ROWTERMINATOR = '\r'
)
GO
I receive the following errors:
Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
This is an example what the data in the .dat file looks like:
ztf917|mj9030|20040519|20120207
ztf918|mj9030|20040519|20120207
ztf919|mj9030|20040519|20120207
ztf920|mj9030|20040519|20120207
ztf921|mj9030|20040519|20120207
ztf922|mj9030|20040519|20120207
ztf923|mj9030|20040519|20120207
ztf924|mj9030|20040519|20120207
ztf925|mj9030|20040519|20120207
ztf926|mj9030|20040519|20120207
ztf927|mj9030|20040519|20120207
There are no column headings in the file.
Any suggestions on how to resolve this error or other recommendation on a better method to import the data from this .dat file into a table?
Let me know if you have any questions.
Note: If I use SSIS, I can perform an manual import into a table, but if save that same import via SSIS as a package and run the package, it errors. Very strange.
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

you might try "/n" for your row terminator. that seems to be what the first error is complaining about. after that the additional errors may be generated because of the first error.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

Is it possible the .dat file is Unix based?

In that case a line feed is .

But when using BULK INSERT, it silently add \r to it, causing the error.

There are two options I know of:

a) use the Hex value of the Unix line feed: ROWTERMINATOR = '0x0a'

b) create a format file with instead of \r

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jared SSC Guru Points: 61793 More actions · Answer 3

My guess is that SSIS is converting the column.

If you run this code:CREATE TABLE #temp (create_date DATE)

INSERT INTO #temp

SELECT 20120501

You get an error... right? Why do you think that it will be different when doing a bulk insert without a format file?

Jared
CE - Microsoft

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 4

VIOLA!! Using the Hex for the ROWTERMINATOR did the trick. It's now importing correctly.

Thank you for all of your responses on this issue. I've been at this all day!

Ronnie

LutzM SSC Guru Points: 107049 More actions · Answer 5

Ronnie Jones (3/27/2012)
VIOLA!! Using the Hex for the ROWTERMINATOR did the trick. It's now importing correctly.
Thank you for all of your responses on this issue. I've been at this all day!
Ronnie

Glad I could help 😀

I've actually never run into this issue. I just remembered reading about it in a thread here at SSC. The rest of it was just plain google 😎

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]