We help IT Professionals succeed at work.

Bulk Insert Problem

657 Views
Last Modified: 2008-01-09
My data I am trying to insert is one column and looks like this,

3684294
502209
35777631
38085589
136029669
149422422
20290639
162647046
3268706
37525885
72728160
84211580
37734343
4823968
1057244
148697486
37823586
357429

My Bulk Insert looks like this,

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (ROWTERMINATOR = '/n'),
(FIELDTERMINATOR = ',')

and the error I get is,

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Comment
Watch Question

Author

Commented:
The table I am trying to insert to looks like this,

CREATE TABLE [dbo].[acct_missing_in_hub](
      [Column 0] [bigint] NULL
) ON [PRIMARY]

Commented:

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '/n',
(FIELDTERMINATOR = ','
)

Commented:
BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '/n',
FIELDTERMINATOR = ','
)

***
missed one

Author

Commented:
I ran this,

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '/n',
FIELDTERMINATOR = ','
)

I got this,

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 1. 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)".

Commented:

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)

Author

Commented:
ran,

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)

got

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 1. 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)".

Commented:

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n'
)

***
Fast fingers, sorry.

ROWTERMINATOR = '\n' you had '/n'
In the example you have there is no field terminator, but our first example used the ',' I changed deleted the requirement for a field terminator.

You could also do
BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'

because the default row terminator is \r\n (new row new line)

Author

Commented:
ran,

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n'
)

got

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 1. 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)".

Commented:
I just did it on my computer and it worked without issue.

Author

Commented:
ran,

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n'
)


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 1. 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)".

Commented:
I copied your column to a notepad page and saved it as a CSV file.

I used your CREATE table statement.

I have been able to bulk insert with either of the last two examples.

Commented:
Are you inserting the same data as the example?

Commented:
You state at the beginning:

My data I am trying to insert is one column and looks like this,

3684294
502209
35777631
38085589
136029669
149422422
20290639
162647046
3268706
37525885
72728160
84211580
37734343
4823968
1057244
148697486
37823586
357429

***
Is this column the data that you are trying to insert?

Commented:
I have added the largest size for a bigint data type to your column

9223372036854775807
3684294
502209
35777631
38085589
136029669
149422422
20290639
162647046
3268706
37525885
72728160
84211580
37734343
4823968
1057244
148697486
37823586
357429

Still successful.

If you are using a different set of numbers - if they are larger than the top line then the insert will fail

Author

Commented:
yes, same data.

Author

Commented:
Changed source table field to varchar(max).  Changed file extension from .rpt to .csv.  Now its only inserting 1st character of first record.

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.csv'
WITH (
ROWTERMINATOR = '\n'
)

Author

Commented:
what would the row terminator be if its not \n?

Commented:
That is odd.

Here is the script that I am running

CREATE TABLE [dbo].[input](
      [Column 0] [bigint] NULL
) ON [PRIMARY]

BULK INSERT dbo.input
FROM 'C:\book\input.csv'
WITH (
ROWTERMINATOR = '\n'
)

TRUNCATE TABLE dbo.input

BULK INSERT dbo.input
FROM 'C:\book\input.csv'

SELECT [column 0] FROM dbo.input
***
I have both bulk insert statements to test if either one fails. I added a truncate to clear out my test table. I also added a NULL value at the top to see if that made it fail.

The only difference I see is that you are using a file named P:\acct_missing_in_hub.rpt, while I am using a CSV file. Maybe the application that creates the report adds hidden characters? Can you rerun the report but make it add a comma (or some other delimiter, |, ^, etc) after every value? Then you could run the

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)

(Change the FieldTerminator to whatever delimiter is chosen.)

Author

Commented:
I looked at the hidden character after the values in the file with a text editor, textpad.

The character most resembles a "d" and i get the same character when I hit return in the file.

Commented:
What application generates the report?

Author

Commented:
it was exported with sql server 2005 client and originally had .rpt extension

Author

Commented:
Also I can't get the import wizard to display the data correctly in preview.

Author

Commented:
Ok, I converted to DOS format in textpad and everything is cool.  I will give you points for your effort.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.