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: 473
  • Last Modified:

Extracting Excel Spreadsheet Data to load an SQL Table

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
jazzray
Asked:
jazzray
  • 10
  • 10
1 Solution
 
puranik_pCommented:
Is KwikFitDlrs name of a sheet in you workbook?
If yes, change the select statement to...
'SELECT * FROM [KwikFitDlrs$]'
0
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
I googled and found this...
http://support.microsoft.com/kb/814398

Is this the issue?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
puranik_pCommented:
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
 
jazzrayAuthor Commented:
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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
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
 
jazzrayAuthor Commented:
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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
>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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
You can run these from query analyzer.
0
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
>>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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
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
 
jazzrayAuthor Commented:
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
 
puranik_pCommented:
glad it helped. :o)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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