Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

Bulk Insert Problem

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 ','.
0
donnatronious
Asked:
donnatronious
  • 12
  • 12
1 Solution
 
donnatroniousAuthor 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]
0
 
ptjcbCommented:

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

***
missed one
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
donnatroniousAuthor 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)".
0
 
ptjcbCommented:

BULK INSERT dbo.acct_missing_in_hub
FROM 'P:\acct_missing_in_hub.rpt'
WITH (
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)
0
 
donnatroniousAuthor 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)".
0
 
ptjcbCommented:

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)
0
 
donnatroniousAuthor 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)".
0
 
ptjcbCommented:
I just did it on my computer and it worked without issue.
0
 
donnatroniousAuthor 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)".
0
 
ptjcbCommented:
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.
0
 
ptjcbCommented:
Are you inserting the same data as the example?
0
 
ptjcbCommented:
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?
0
 
ptjcbCommented:
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
0
 
donnatroniousAuthor Commented:
yes, same data.
0
 
donnatroniousAuthor 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'
)
0
 
donnatroniousAuthor Commented:
what would the row terminator be if its not \n?
0
 
ptjcbCommented:
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.)

0
 
donnatroniousAuthor 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.
0
 
ptjcbCommented:
What application generates the report?
0
 
donnatroniousAuthor Commented:
it was exported with sql server 2005 client and originally had .rpt extension
0
 
donnatroniousAuthor Commented:
Also I can't get the import wizard to display the data correctly in preview.
0
 
donnatroniousAuthor Commented:
Ok, I converted to DOS format in textpad and everything is cool.  I will give you points for your effort.
0
 
ptjcbCommented:
Thanks.

Yes, I hope you don't have to do this every day. :  )
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now