Bulk Insert Errors

Question

Bulk Insert Errors

crazy_new

SSCarpal Tunnel

Points: 4804
More actions
March 20, 2015 at 2:31 am

#298765

Hi Guys,
I am trying to import a file using a format file but I am getting errors.
My Code is:
^{bulk insert PilotReportDB.[MPSales].[SalesReports]}
from 'C:\Work\Development\Sales_Report.xlsx'
with (formatfile = 'C:\Work\Development\SalesReportsFF.xml')
I am getting the following errors:
Msg 4866, Level 16, State 7, 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)".
And when I try using openrowset:
^{INSERT INTO PilotReportDB.[MPSales].[SalesReports]}
SELECT
*
FROM OPENROWSET(BULK 'C:\Work\Development\Sales_Report.xlsx',
FORMATFILE=':\Work\Development\SalesReportsFF.xml'
) as t1 ;
I get the following error:
Msg 4861, Level 16, State 3, Line 1
Cannot bulk load because the file ":\Work\Development\SalesReportsFF.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
I don't understand how it could be too long as this data is already in the database, I imported it with the Import wizard.
I am just doing this on a test machine to try to automate the process.
Any advise would be awesome.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

just checking the obvious here:

what drive is this on? the string starts with a colon which is obviously invalid but might be an artifact of code obfuscation? or an actual error that would explain the error?

FORMATFILE=':\Work\Development\SalesReportsFF.xml'

i would think it should be like this:

FORMATFILE='C:\Work\Development\SalesReportsFF.xml'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

just a quick question, have you checked it without file format?

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 3

Hi,

I did see that I didn't highlight the drive when I copied it in here but it is in here.

I have fixed the format file as there was a spelling error, but now it gives me the same error as the top one.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

You can't use BULK INSERT to import .xlsx files. It can be done with OPENROWSET but you'll need the ACE Drivers to do that. http://www.microsoft.com/en-us/download/details.aspx?id=13255

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 5

I already have the Access Database Engine drivers on. Would the version of the driver make a difference?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

crazy_new (3/23/2015)
I already have the Access Database Engine drivers on. Would the version of the driver make a difference?

Yes. The ACE drivers work with the xlsx Excel format. Also, I don't believe the Jet drivers have a 64-bit version.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 7

Hi, I have installed the driver but I am still getting those three error. I am using a format file so I don't understand why the first error says the column is too long.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Read Jeff's post again

Jeff Moden (3/23/2015)
You can't use BULK INSERT to import .xlsx files. It can be done with OPENROWSET but you'll need the ACE Drivers to do that. http://www.microsoft.com/en-us/download/details.aspx?id=13255

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 9

I get the same errors when using openrowset.

Just below those first errors I pasted my openrowset statement, but it is returning the same errors.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You've left out the drive letter in the path

Cannot bulk load because the file ":\Work\Development\SalesReportsFF.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

That needs to be either C:\, D:\, etc or \\Work\development

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 11

Sorry the letter part I just missed when I highlighted it to copy it. It is in there and then I get the same errors.

insert into mpsales.salesreports

select *

from openrowset(bulk 'C:\EuroSales.xlsx',

formatfile = 'C:\SalesReportsFF.xml')as t1

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

You missed the exact same letter in both the statement and the error message, an error that stated that the format of the file name was wrong?

If that's an excel spreadsheet, you don't need and shouldn't be using a format file, you should be specifying the driver that you installed earlier and importing it as an excel spreadsheet. Format file is for a text file. Excel spreadsheets aren't text.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 13

I have now moved it to the D drive and the statement I use is as follows:

insert into mpsales.salesreports

select *

from openrowset(bulk 'D:\EuroSales.xlsx',

formatfile = 'D:\SalesReportsFF.xml')as t1

The errors are:

Msg 4866, Level 16, State 7, 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)".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Don't put stuff in the root of a drive, you're likely to get permission problems. Windows protects the root directory of all drives, C the most, but all others as well.

See my edits of the previous post as to what's likely wrong

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass