Buzby58
asked on
MS SQL Bulk Insert problem
Hi All
Time to page the FOAK once again.
I'm having trouble with a bulk insert statement - hope someone can help me out here!
The sql is as follows:-
BULK INSERT dbo.gbinfo FROM 'I:/pages/gb/pass/monthly. txt' WITH(FIELDTERMINATOR = '|',ROWTERMINATOR = '\n')
I have some 750k rows all looking like:-
1|2002-12-01|12|2.71|0.00| 271.00|C01 0002|OXF|C entral|1
3|2002-12-01|12|30.51|0.00 |3051.00|C 010005|*N| *N|1
4|2002-12-01|12|1.67|0.00| 167.00|C01 0007|UN |Scotland|1
5|2002-12-01|12|7.09|0.00| 709.00|C01 0008|OXF|C entral|1
and so on
Until I can get an import I've set all the columns in the table to nvarchar and there is no index column.
It's chucking up this error:-
Auto-attach to process '[2340] [SQL] GRANTB' on machine 'GRANTB' succeeded.
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The program '[2340] [SQL] GRANTB: GRANTB\SQLEXPRESS' has exited with code 0 (0x0).
Many thanks in advance!
Best regards
Grant
Time to page the FOAK once again.
I'm having trouble with a bulk insert statement - hope someone can help me out here!
The sql is as follows:-
BULK INSERT dbo.gbinfo FROM 'I:/pages/gb/pass/monthly.
I have some 750k rows all looking like:-
1|2002-12-01|12|2.71|0.00|
3|2002-12-01|12|30.51|0.00
4|2002-12-01|12|1.67|0.00|
5|2002-12-01|12|7.09|0.00|
and so on
Until I can get an import I've set all the columns in the table to nvarchar and there is no index column.
It's chucking up this error:-
Auto-attach to process '[2340] [SQL] GRANTB' on machine 'GRANTB' succeeded.
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The program '[2340] [SQL] GRANTB: GRANTB\SQLEXPRESS' has exited with code 0 (0x0).
Many thanks in advance!
Best regards
Grant
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since the error is that the last value in the first row is too long, my guess is that the row terminator is at fault and the import has interpreted the entire remainded of the file as the contents of row1, column 10.
Try
ROWTERMINATOR = '/r'
More Info on row terminators here:
http://msdn.microsoft.com/en-us/library/ms191485.aspx
ROWTERMINATOR = '/r'
More Info on row terminators here:
http://msdn.microsoft.com/en-us/library/ms191485.aspx
ASKER
Tried a good combination of row terminators - still no go - just can't see why it won't work :-/
ASKER
Sorry I'm newsish to MS SQL
The txt file is 28mb and am using SQL express 2008 - load works like a dream in MySql but that options not open to me!
I've just discovered this:-
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 10. 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)".
Not sure about the field terminator and there's no nulls in there as far as can see - nulls are allowed in the receiving table.
Thanks
Grant