Importing data to SQL...Bulk Insert

Question

Importing data to SQL...Bulk Insert

Ahmad Osama

SSCertifiable

Points: 7676
More actions
June 30, 2008 at 3:29 am

#71663

Hi All,
I have a txt file with 106 columns and 34 mil rows....I want to import it into SQL server 2000.....
I used the following command .. but it dint worked ....
bulk insert dnbtxt1
from 'E:\smb\dnb.txt'
with (
FieldTerminator='/t ',
ROWTERMINATOR='/n' )
ERROR:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Plz help.....
Thanks Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

I assume your columns are tab delimited, and each line is CR delimited. If so you had /t instead of \t), and had an additional space after the /t:, same for the /n

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

Hi All,

Can i export MS Excel 2007 file into sql 2000

Regards,

Ahmad

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 3

Ummmm... before you go asking another question... how about letting us know if the fix for the previous question worked...

--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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 4

Jeff Moden (7/1/2008)
Ummmm... before you go asking another question... how about letting us know if the fix for the previous question worked...

hi...

No, it dint.

I don't have a correct format of the data in text file....and to use bcp or bulk insert one should have an existing table to import the data....All I have is 5 gb text file ....I have split the file into small size text file and then I opened it into MS Excel 2007.

But am unable to export that into SQL.....

Any suggestions.... 🙂

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

Ummm.... Use DTS to import just one of the Excel sheets... the one with the column headers. That will define a table fairly nicely. Then, truncate the table and use DTS to import the text file. Yeah, I know you can import a text file with DTS, but if a table doesn't already exist, it'll make a table with all VARCHAR(8000) columns... not a good idea.

If it's something that you need to do on a regular basis, you can use the FORMAT option of BCP to generate a BCP Format file and do this right. With the BCP Format file, you should be able to import the 34 million rows easily in less than 30 minutes.

--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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 6

Jeff Moden (7/1/2008)
Ummm.... Use DTS to import just one of the Excel sheets... the one with the column headers. That will define a table fairly nicely. Then, truncate the table and use DTS to import the text file. Yeah, I know you can import a text file with DTS, but if a table doesn't already exist, it'll make a table with all VARCHAR(8000) columns... not a good idea.
If it's something that you need to do on a regular basis, you can use the FORMAT option of BCP to generate a BCP Format file and do this right. With the BCP Format file, you should be able to import the 34 million rows easily in less than 30 minutes.

Thanks Jeff..already tried the mentioned thing...I am getting datatype error...well... I have tried every data type ..buts its not working.

Any suggestions...????

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 7

You made a BCP FORMAT file like I suggested, eh?

--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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 8

Oops...I did it directly...

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 9

Hey Jeff... Still unable to upload the upload the file....

Code for format file.....

bcp dnbsql..dnb12 format -T -n -f dnb.fmt

Code for Bulk insert

BULK INSERT dnb12

FROM 'E:\smb\dnb.txt'

WITH (FORMATFILE = 'E:\SMB\dnb.fmt')

ERROR

[Code]

Server: Msg 4839, Level 16, State 1, Line 1

Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'E:\SMB\dnb.fmt'.

[/code]

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 10

Obviously, you'll need to fix the error...

Part of the whole problem on things like this is the condition of the data. For example, I downloaded the provider list for Medicaid and followed their record layout to setup the table... their record layout was (still is) wrong. It was a real bugger to get that one going... hundreds of columns and millions of rows.

It's just gonna take some work on your part... maybe, a lot of work. You're headed in the right direction with the BCP format file... keep plugging.

--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)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 11

Jeff Moden (7/2/2008)
Obviously, you'll need to fix the error...

but what should I do ... Should i open up the format file and check the collation...I don't hv any idea to proceed on this ....

Regards,
[font="Verdana"]Sqlfrenzy[/font]