Bulk Insert of TXT File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Bulk Insert of TXT File

Author

Topic

cobby1812
Starting Member

16 Posts

Posted - 2011-11-25 : 09:56:38

Hi there,
I am a bit new to Bulk Insert. I have a file that will come in daily as shown below

1000000111111107400000000000000000000000021562BANK GIRO CREDIT 11328
1000000111111107400000000000000000000010223568BANK GIRO CREDIT 555555 11328

I need to import this into a Database. I have created a Format file...as shown below

4.2
52
1 SYBCHAR 0 6 "" 1 BankSrtCode
2 SYBCHAR 0 8 "" 2 BankAcctNumber
3 SYBCHAR 0 3 "" 3 Type
4 SYBCHAR 0 29 "" 4 Amount
5 SYBCHAR 0 18 "" 5 Reference
6 SYBCHAR 0 37 "" 6 Details
7 SYBCHAR 0 5 "\r\n" 7 Date

Yet when I do it I get this error

Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "d:\DebtorPaymentsFMT\DebtorPayments.fmt".

Any help would be greatly appreciated.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 10:13:06

Try canging the 52 to 7?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cobby1812
Starting Member

16 Posts

Posted - 2011-11-25 : 10:17:03

Hi Nigel,
OK that moved it on........now getting an error re the 4 SYBCHAR 0 29 "" 4 Amount
as follows
Bulk load data conversion error (overflow) for row 12, column 4 (Amount).
Msg 4867, Level 16, State 1, Line 1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 10:32:40

I think you have a problem with how you are tryiong to do the import or the file format.
It looks like you are trying to import fixed length fields - which is ok as long as the non-fixed length data is at the end of the row. Looking at the data thought I think that 11328 is the date which means that the preceding data is variable length - you will need to use delimiters for this which doesn't look possible for that data. What is the 555555 in the second row.

Think you would have to import into a single column table (without a format file) and parse it in sql server. A lot simpler than what you are trying to do.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cobby1812
Starting Member

16 Posts

Posted - 2011-11-25 : 10:45:49

Hi there,
I thought as much....bummer. OK thank you for your help. Will insert into one column and strip it out from there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources