import excel spreadsheet via a script 2

[christer99]

How can I import an excel spreadsheet automatically via a script (in other words NOT manually using DTS)?

 

[gmmastros]

You can use OpenDataSource to get your excel data, like so...

Select *
From   OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="[!]c:\tektips.xls[/!]";User ID=Admin;Password=;Extended properties=Excel 8.0;')...[!]Sheet1$[/!]

You'll need to change the file name and the sheet name to suit your needs.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[christer99]

Getting this error:

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.].

Select *
From OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\ARP\SICCODES.xls";User ID=Admin;Password=;Extended properties=Excel 8.0;')...[naicstb1_NEWrevision3$]

 

[SQLDenis]

I like to use OPENROWSET instead

like this

SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')

Your problem is that the path is wrong/not accessable or the excel file is already opened by someone else (or your self) and you are trying to open it
for some problems and why they occur read this

http://sqlservercode.blogspot.com/2006/03/openrowset-and-excel-problems.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I was afraid that would happen.

Your data source is C:\folder\file.xls

This MUST be referenced to the server.

For example, if sql server is installed on another computer (different than the computer you are trying to run it from), then you'll have problems.

From sql server's perspective, is there a folder named ARP, with a file named SICCODES.xls? Or does this file exist on your computer?

Another common mistake occurs when you are viewing the file in Excel while you attempt to import it. You have to close excel first.

Clear as mud? Hope not. If you don't understand, let me know and I will try to explain better.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[christer99]

It worked when I changed the path to: \\gadomino25\ARP\SICCODES.xls (instead of C:\)

thank you very much

 

[gmmastros]

Denis,

Why do you prefer OpenRowSet instead of OpenDataSource?

Is it just 'habit' or is there some other reason?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Just habit I guess and I don't like the 3 dots at the end, syntax is not consistent
1 dot for SQL server , 3 for Excel etc etc


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[christer99]

Do I need to have Excel installed on the server for this work? It is working on W2003, but tried it on two different W2000 servers and getting this error:


OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Failure creating file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
1> 2> 3>

when running this line:
use arp

INSERT INTO dbo.SIC2
(SICCODE, SICDESC)
(Select (case when LEN(convert(varchar(4),siccode))= 3 then '0'+(convert(varchar(5),siccode)) else (convert(varchar(4),siccode)) end) as SIC, LEFT(sicdesc,150) AS SDESC
From OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\gaexchange\ARP\SICCODES.xls";User ID=Admin;Password=;Extended properties=Excel 8.0;')...[naicstb1_NEWrevision3$])

GO

 

[gmmastros]

Excel does not need to be installed. You are using Excel OLEDB to open the excel file. This is installed with MDAC. I suggest you check your version of MDAC and possibly install a newer version on the other machines.

To check the version of mdac...

http://www.microsoft.com/downloads/...F6-4A21-4B43-BF53-14332EF092C9&displaylang=en

To download the latest version...

http://www.microsoft.com/downloads/...95-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[christer99]

Have the latest version of MDAC installed on W2000 server and I am still getting the error. Any other ideas? As mentioned it is working on a W2003 server, but not on two different W2000 servers. What does the "failure creating file" mean?

 

[gmmastros]

Do the other servers have access to the file...
\\gaexchange\ARP\SICCODES.xls

It could be a permissions issue. Maybe.

Try...

exec master..xp_cmdshell 'dir \\gaexchange\ARP\SICCODES.xls'

Does this return anything that may help you track down this problem?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom