Solved

Extracting Excel Spreadsheet Data to load an SQL Table

Posted on 2007-04-05
20
419 Views
Last Modified: 2012-06-27
I am trying to use MS SQL Analyser to extract data from an Excel 2000 v9.0 spreadsheet and insert the data into an SQL table that already exists.

The spreadsheet is located on my local machine at : C:\Kwik-FitDeliveryPoints.xls
There is one worksheet called Sheet1. Row 1 contains the column names. I have also created a name range called KwikFitDlrs that covers all the data plus row 1.

I have tried the following code (after seeing the answer for another question that referred the person to:
http://support.microsoft.com/Default.aspx?kbid=321686
which seemed straight forward!!!!)

INSERT       ArMultAddress
      (Customer,
      AddrCode,
      ShipToName,
      ShipToAddr1,
      ShipToAddr2,
      ShipToAddr3,
      ShipToAddr4,
      ShipToAddr5,
      ShipPostalCode)
SELECT      '0010012',
      Centre,
      'KWIK-FIT (GB) LTD',
      CentreName,
      Address1,
      Address2,
      Address3,
      'Tel No: ' + Phone,
      PostCode
FROM OPENROWSET      ('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\Kwik-FitDeliveryPoints.xls',
            'SELECT * FROM [KwikFitDlrs]')

But I get the following error messages:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

Has anyone got any idea what I'm doing wrong?
Thank-you for any help you can give.

0
Comment
Question by:jazzray
  • 10
  • 10
20 Comments
 
LVL 14

Expert Comment

by:puranik_p
ID: 18856711
Is KwikFitDlrs name of a sheet in you workbook?
If yes, change the select statement to...
'SELECT * FROM [KwikFitDlrs$]'
0
 

Author Comment

by:jazzray
ID: 18856744
Hi,
No it isn't. It's the name of a name range within Sheet1
I have also tried the OPENROWSET to be as below:
FROM OPENROWSET      ('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\Kwik-FitDeliveryPoints.xls',
            'SELECT * FROM [Sheet1$]')

But I get the same error.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18856753
I googled and found this...
http://support.microsoft.com/kb/814398

Is this the issue?
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18856771
One more thing...
<pasted>
It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine.  The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive.  If the Excel file specified in the provider_string  parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error.].
</pasted>
from http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
0
 

Author Comment

by:jazzray
ID: 18856890
Ah...... your comment about where the C: drive aught to be may well be the solution as I had it located on the C: drive of my local machine. I will give this a try and let you know how I get on.
0
 

Author Comment

by:jazzray
ID: 18857010
Hi puranik,
I have logged onto the SQL server itself (using VNC) and copied the excel spreadsheet onto the C: drive on that server. I am logged into the server as the administrator and I've started query analysis on the server itself and logged in with the administrator account ........ so presumably there shouldn't be any permissions problems?? However, I did start to read through the link at http://support.microsoft.com/kb/814398

but am I right that by logging in as administrator (which is also the SQL startup account), it shouldn't be a problem?

However, I still get the same error.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18857068
If you are logging in as administrator, then surely kb814398 does not apply.
So the issue must be something else.

Why don't you go step by step as specified the link below? http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

Follow all the steps exactly in the same manner and use the data they have given as Sheet1.
If we still have a problem it means we have some issue with the setup/security. If it works, it means the issue is with your excel sheet.
Can you try it?
0
 

Author Comment

by:jazzray
ID: 18857115
Hi puranik,
I will try this. (I  perhaps should have pointed out that I'm very much a novice re: SQL administration and permissions).
Thank-you for all your suggestions; I'll let you know how I get on.
0
 

Author Comment

by:jazzray
ID: 18881539
Hi puranik,
I have tried EVERYTHING but cannot get this to work. I'm pretty sure that your link about permissions may be the route cause of the problem ...... but I have tried all sorts of things re permissions and security settings with no luck.
However, determined to find some sort of solution ....... I then hunted round the help for SQL Server Enterprise and realised I could use the Data Transformation Services. OK..... to get the resulting data in the format I want, I have had to do it via an initial data load and then some SQL update/tweeks afterwards .... but at least it's done! If you have any other suggestions about the original problem, I'd be interested in trying them as that approach is more simple and I could see me wanting to use it in the future?
So ..... I don't know how this aught to be signed off as I haven't really used your suggestion......... but thank-you for giving me several pointers.
Thanks again
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18881565
>how this aught to be signed off
Post a Q in Community Support for DELETE/Refund. and They will do it.
Now back to the problem...
>I have tried EVERYTHING but cannot get this to work
Do you mean you could not read from the other (newly created) workbook as well?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:jazzray
ID: 18881866
Hi puranik,
No, I couldn't and I made it exactly the same as in the example. I also just did
SELECT *
FROM OPENROWSET      ('Microsoft.Jet.OLEDB.4.0',
            'Excel 9.0;Database=C:\Addresses.xls;IMEX=1',
            'SELECT * FROM [Sheet1$]')

Just incase there was something 'odd' with the column names (although I know that's really clutching at straws) but it made no difference.

The microsoft link about the TEMP and TMP system variables was a possible, but I was surprised it said you had to log off and log back on again for the authority changes to take effect. (the administrator for our domain is the SQL Startup account and I realised it DIDN'T have full control of those folders for some reson????). Because it is a production system server, I can't do this willy-nilly. So asked a colleaague to log it off over the weekend and was only able to try it out again this morning. To find it still didn't work.

I'm sure it's got something to do with permissions ...... but thus is not an area I have much expertise in.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18922589
To make sure this is seciroty issue, run this
EXEC master..xp_cmdshell 'dir C:\'

and to make sure if file exists there, run this
exec master..xp_fileexist 'C:\Addresses.xls'
0
 

Author Comment

by:jazzray
ID: 18964011
Hi Puranik,
Sorry I haven't replied; been away. Do I run these commands in a RUN line or in some other way? I tried in the RUN window but it doesn't understand EXEC?
Thanks.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18964030
You can run these from query analyzer.
0
 

Author Comment

by:jazzray
ID: 18964289
Thank-you puranik.

They both run fine. The second shows that the file exists. Is there any particular information from the first that is important? Or is it just a case of ...... if I can run it, I have the necessary authority?
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18964750
>>if I can run it, I have the necessary authority?
No. You should get the results as expected.

EXEC master..xp_cmdshell 'dir C:\' should give you all the files and folders in C.
and exec master..xp_fileexist 'C:\Addresses.xls' should tell you that file exists.

I think you have all the permissions. and the problem is with the excel sheet.
0
 

Author Comment

by:jazzray
ID: 18966413
Any suggestions as to what the problem might be? I can open it with Excel on the server where the SQL database is, without any problems so I'm at a loss as to what the problem might be?
0
 
LVL 14

Accepted Solution

by:
puranik_p earned 500 total points
ID: 18971530
May be some syntactical issue with the parameters in OPENROWSET. I can't find it though.
How about taking this up and doing it step by step again...
http://www.databasejournal.com/features/mssql/article.php/3331881

All the best.
Pura
0
 

Author Comment

by:jazzray
ID: 18972143
Hi Pura,
Thank-you very much for that link. I worked through the examples and this time it works! I don't have the time at the moment to try and see what I was doing wrong before, but at least I now know how to do it!

Actually, I think you deserve the points just for the link itself; it's a very useful site.
Thanks once again.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 18972162
glad it helped. :o)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now