jazzray
asked on
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.
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-FitDe
'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.
The spreadsheet is located on my local machine at : C:\Kwik-FitDeliveryPoints.
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-FitDe
'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.
ASKER
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-FitDe
'SELECT * FROM [Sheet1$]')
But I get the same error.
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-FitDe
'SELECT * FROM [Sheet1$]')
But I get the same error.
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
<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
ASKER
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.
ASKER
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.
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.
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?
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?
ASKER
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.
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.
ASKER
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
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
>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?
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?
ASKER
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.
'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.
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.
'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.
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'
EXEC master..xp_cmdshell 'dir C:\'
and to make sure if file exists there, run this
exec master..xp_fileexist 'C:\Addresses.xls'
ASKER
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.
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.
You can run these from query analyzer.
ASKER
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?
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?
>>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.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
glad it helped. :o)
If yes, change the select statement to...
'SELECT * FROM [KwikFitDlrs$]'