Bulk Insert Help

[gaelin]

Like others who have posted this problem, I believe the problem is I can't determine the row terminator of the text file.

The error I get is:
Msg 4866, Level 16, State 8, 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.

In the facility I work I'm not allowed to load up a bit text editor, so anyone would help I would really appreciated it

I'm listed some of the text file

code[/LIDSR-E.DAT 2011/03/03 04:40:30 LIMESTONE
DD 0000010684110448190E 4F 8815 CQADIQ559ZZ S 5FA300211C0007 D750A 000000 00000000000000000000000000000000668751A00000000000000000000000000 1022 25 9890 R
DD 0000020406540031510L 3011154K0491212F 559ZZ BMOE00 S 5FA308910F0212 000000 2G 00000000000000000000000000000000127500{00000000000000000000000000 1029 25 0 R
DD 0000033099570396920L 300164450385976F11 52900 BW 3A44497CS 5FA304710C0067 A39616000000 1R 00000000000000000000000000000000765049E000000000000{000000059244B 0307 25 0 R
DD 0000048362300177480L 2X116446B887700F05 55802 3D562B S 5FA304711P0087 000000 10 00000000000000000000000000000000071500{00000000000000000000000000 0310 25 0 R
DD 0000058362300177480L 2X116446B887700F05 55802 3D562B S 5FA304711P0087 000000 10 00000000000000000000000000000000007500{00000000000000000000000000 0310 25 0 R
DD 0000073098610396920L 300164450385976F11 52200 BW 3A44497CS 5FA304710C0067 A39616000000 1R 00000000000000000000000000000002799500D00000000000000000000000000 0261 25 0 R
DD 0000088362300177480L 2X116446B887700F05 55802 3D562B S 5FA304711P0087 000000 10 00000000000000000000000000000000011000{00000000000000000000000000 0310 25 0 R
DD 000010C208260357910L 309178770127596F01 627 W0XD007CZN4 IE 000000 1Z 00000000000000000000000000000000002512E00000000000000000000000000 0275 09 0 R
DD 0000131399910052590X 30TT78740128006F13 592 L77032C B 4F1962893C0016 FF 000000000000001C 00000000000000000000000000000000130750G000000000000{000000189084C 00960000000 09 0 R
DD 0000171852930288140C 290 47B1 61102F 61231158800 J0L308 6TR0NL G 4F1ATA00060G004 NLDRS001000000 00000000000000000000000000000000011184G0000000000000000001238815C 1045 524 0 R
DD 000019C226380357910L 309178770127596F01 627 W0X100ZAZN4 IE 000000 1Z 00000000000000000000000000000000002872B00000000000000000000000000 0275 09 0 R
DD 000024E329190196340L 301178770128534F01 628 W0X100ZAZN4 IE 000000 1Z 00000000000000000000000000000000493765H00000000000000000000000000 1056 09 0 R
DD 000025E329660196340L 301178770128534F01 627 W0X9007CZN4 IE 000000 1Z 00000000000000000000000000000000000234H00000000000000000000000000 1008 09 0 R
DD 000026A285760394750L 300164310385978F10 53390 BW 3A4421 S 5FA308910P0087 A41129000000 1R 00000000000000000000000000000000002649F000000000000{000000341053B 0323 25 0 R
DD 0000303099590396920L 300164450385976F11 53200 BW 3A44497CS 5FA304710C0067 A39616000000 1R 00000000000000000000000000000000250572{000000000000{000000375858{ 1042 25 0 R
DD 0000324153090073940X 30WW78740433126F01 569 L721389 B 5F0460696D0074QP18UQ 000000000000002B 00000000000000000000000000000000071331E00000000000000000000000000 13410000000 09 0 R
DD 0000410822330038480L 301115570491220F 70210 BMHL544JS 5FA308911F0050 000000 2A 00000000000000000000000000000000106600{00000000000000000000000000 1057 25 0 R
DD 000047A930240396920L 300164450384731F01 61900 3L3959 S 5FA304710F0269 000000 2A 00000000000000000000000000000000000000{000000060000{0000000000000 1059 25 0 R
DD 0000686151230393420L 2X816446B887724F05 592 3D5823 S 5FA304708P0813 000000 30 00000000000000000000000000000000000064A000000000000{000000837135I 0203 25 0 R
]

 

[djj55]

What SQL version are you using?
What are you using to do the bulk insert? (SSIS, DTS, BCP,...)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gaelin]

I'm using SQL Server 2005. I'm unfamiliar with SSIS, DTS, etc, but wouldn't I still need to be able to determine the row terminator? For what it's worth, I opened the text file in VFP and saved it. I could then use bulk insert with the "new" text file. This text file is essentially one field (which represent a bunch of data concatenated together) with some sort of row terminator I can NOT determine.

 

[djj55]

If you are using the wizard that is basically SSIS.

One way the wizard is accessed is by right clicking the database in SSMS. Please see BOL for importing.

One thing you may need to change is under the Advanced item under "Data source:" the OutputColumnWidth should be increased to fit your data.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gaelin]

Thanks for replying, I will check this out on Monday and let you know.

 

[gaelin]

I was able to load the text file. I found the I needed to use the hex decimal code of '0x0a' to identify the line feed character..