Importing data from Excel

Hi
I'm having some data in some Excel spreadsheets, that I'd like to get into a
few tables in a SQL database.
What's the best/easiest way to do this with T-SQL?
I can do it with DTS, but I'd like to be able to do it with T-SQL so I'm
able to better control what's going to happen with the data.
I've tried to add my Excel sheet as a linked server, but then I simply can't
figure out how to select the columns in my sheet. (...apparently I don't
quite understand the explanation in BOL). It says that I have have to give a
range of cells a name, but maybe I have done this wrong. In Excel I've went
to Insert > Name and then added a name that points to the first column in my
sheet. When I then select the column, it then shows the name in the upper
left corner, so it actually seems ok.
When I then i QA runs a select statement "select * from
excelsource...filnavn" where excelsource is the "name" of my linked server,
I get the error message :
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.].
This doesn't tells me a lot...
Can any of you guide to what I can check to make this work, or maybe give me
some other ideas of how to get data from Excel?
Thanks a lot...
Regards
SteenSteen
One option is to use OpenDataSource. Run this script on QA
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23WI%23WjRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm having some data in some Excel spreadsheets, that I'd like to get into
a
> few tables in a SQL database.
> What's the best/easiest way to do this with T-SQL?
> I can do it with DTS, but I'd like to be able to do it with T-SQL so I'm
> able to better control what's going to happen with the data.
> I've tried to add my Excel sheet as a linked server, but then I simply
can't
> figure out how to select the columns in my sheet. (...apparently I don't
> quite understand the explanation in BOL). It says that I have have to give
a
> range of cells a name, but maybe I have done this wrong. In Excel I've
went
> to Insert > Name and then added a name that points to the first column in
my
> sheet. When I then select the column, it then shows the name in the upper
> left corner, so it actually seems ok.
> When I then i QA runs a select statement "select * from
> excelsource...filnavn" where excelsource is the "name" of my linked
server,
> I get the error message :
> 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.].
> This doesn't tells me a lot...
> Can any of you guide to what I can check to make this work, or maybe give
me
> some other ideas of how to get data from Excel?
> Thanks a lot...
> Regards
> Steen
>
>|||Hi Uri
I think that I might be missing some basic knowledge about using these
features. I can't get it to work with OpenDataSource either. It gives me the
same error as before when using the LinkedServer option.
What I'm missing, is which userId and Password I have to supply? If I supply
anything else than "admin" as in your example, it gives me an
"authentication failed" error. That kind of indicates that it do get
authenticated with the admin userId, but that sounds a bit strange to me.
I'm also unsure about the last bit of the string -..Book1$? Is that
something I have to change to something else, or is it just the default
"sheet" (..I can't remember what it's called in english - I'm sitting with a
danish Excel - unfortunately).
I'm sorry if I sounds stupid with these questions, but I don't find BOL very
detailed on this subejct. If any of you have any links to other placed where
I can find some more detailed info about it, I'd be happy about that.
Regards
Steen
Uri Dimant wrote:
> Steen
> One option is to use OpenDataSource. Run this script on QA
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23WI%23WjRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I'm having some data in some Excel spreadsheets, that I'd like to
>> get into a few tables in a SQL database.
>> What's the best/easiest way to do this with T-SQL?
>> I can do it with DTS, but I'd like to be able to do it with T-SQL so
>> I'm able to better control what's going to happen with the data.
>> I've tried to add my Excel sheet as a linked server, but then I
>> simply can't figure out how to select the columns in my sheet.
>> (...apparently I don't quite understand the explanation in BOL). It
>> says that I have have to give a range of cells a name, but maybe I
>> have done this wrong. In Excel I've went to Insert > Name and then
>> added a name that points to the first column in my sheet. When I
>> then select the column, it then shows the name in the upper left
>> corner, so it actually seems ok.
>> When I then i QA runs a select statement "select * from
>> excelsource...filnavn" where excelsource is the "name" of my linked
>> server, I get the error message :
>> 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.].
>> This doesn't tells me a lot...
>> Can any of you guide to what I can check to make this work, or maybe
>> give me some other ideas of how to get data from Excel?
>> Thanks a lot...
>> Regards
>> Steen|||Import using DTS is the best and quick enough. Make your import into a work
table and then remake using SQL. So you'll have it under control.
Vlastik
"Steen Persson" <SPE@.REMOVEdatea.dk> pí¹e v diskusním pøíspìvku
news:#WI#WjRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm having some data in some Excel spreadsheets, that I'd like to get into
a
> few tables in a SQL database.
> What's the best/easiest way to do this with T-SQL?
> I can do it with DTS, but I'd like to be able to do it with T-SQL so I'm
> able to better control what's going to happen with the data.
> I've tried to add my Excel sheet as a linked server, but then I simply
can't
> figure out how to select the columns in my sheet. (...apparently I don't
> quite understand the explanation in BOL). It says that I have have to give
a
> range of cells a name, but maybe I have done this wrong. In Excel I've
went
> to Insert > Name and then added a name that points to the first column in
my
> sheet. When I then select the column, it then shows the name in the upper
> left corner, so it actually seems ok.
> When I then i QA runs a select statement "select * from
> excelsource...filnavn" where excelsource is the "name" of my linked
server,
> I get the error message :
> 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.].
> This doesn't tells me a lot...
> Can any of you guide to what I can check to make this work, or maybe give
me
> some other ideas of how to get data from Excel?
> Thanks a lot...
> Regards
> Steen
>
>|||Your linked server may not function. drop it and try this:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\test.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
select * into #temp from ExcelSource...Sheet1$
select * from #temp
At least this works for me. HTH.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23QdZMoShEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi Uri
> I think that I might be missing some basic knowledge about using these
> features. I can't get it to work with OpenDataSource either. It gives me
the
> same error as before when using the LinkedServer option.
> What I'm missing, is which userId and Password I have to supply? If I
supply
> anything else than "admin" as in your example, it gives me an
> "authentication failed" error. That kind of indicates that it do get
> authenticated with the admin userId, but that sounds a bit strange to me.
> I'm also unsure about the last bit of the string -..Book1$? Is that
> something I have to change to something else, or is it just the default
> "sheet" (..I can't remember what it's called in english - I'm sitting with
a
> danish Excel - unfortunately).
> I'm sorry if I sounds stupid with these questions, but I don't find BOL
very
> detailed on this subejct. If any of you have any links to other placed
where
> I can find some more detailed info about it, I'd be happy about that.
> Regards
> Steen
>
> Uri Dimant wrote:
> > Steen
> > One option is to use OpenDataSource. Run this script on QA
> > SELECT *
> > FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source="c:\MyExcel.xls";
> > User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
> >
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:%23WI%23WjRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> Hi
> >>
> >> I'm having some data in some Excel spreadsheets, that I'd like to
> >> get into a few tables in a SQL database.
> >> What's the best/easiest way to do this with T-SQL?
> >> I can do it with DTS, but I'd like to be able to do it with T-SQL so
> >> I'm able to better control what's going to happen with the data.
> >>
> >> I've tried to add my Excel sheet as a linked server, but then I
> >> simply can't figure out how to select the columns in my sheet.
> >> (...apparently I don't quite understand the explanation in BOL). It
> >> says that I have have to give a range of cells a name, but maybe I
> >> have done this wrong. In Excel I've went to Insert > Name and then
> >> added a name that points to the first column in my sheet. When I
> >> then select the column, it then shows the name in the upper left
> >> corner, so it actually seems ok.
> >> When I then i QA runs a select statement "select * from
> >> excelsource...filnavn" where excelsource is the "name" of my linked
> >> server, I get the error message :
> >>
> >> 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.].
> >>
> >> This doesn't tells me a lot...
> >>
> >> Can any of you guide to what I can check to make this work, or maybe
> >> give me some other ideas of how to get data from Excel?
> >>
> >> Thanks a lot...
> >>
> >> Regards
> >> Steen
>|||Agree with Greybeard.
(a) Create a table whose structure matches that of the excel spreadsheet.
(b) Import the data into this table using DTS, OpenDataSource, etc..
(c) Run your data manipulation scripts on this table to get it into the
shape that you want.
(d) Update the actual tables from this table.
It's the normal ETL process in a nutshell.
Cheers!|||Hi
Thanks for your inputs... Now I've actually got it working with both
AddLinkedServer and OpenDataSource.
Whne using Richards example, it gave me an error accessing the Excel sheet.
That lead me to my error source which was the way I defined the path to my
Excel sheet. My mistake was that I thought the drive letters was what it was
on my workstation where I run QA, but it's what it is in the SQL
server...:-). I normally try avoiding using drive letters and using UNC
path's instead, but in this case I had the file on a drive where wasn't sure
about the UNC path...so much for trying the easy solution...:-).
As always...thanks a lot for your suggestions....
Regards
Steen
Richard Ding wrote:
> Your linked server may not function. drop it and try this:
> EXEC sp_addlinkedserver 'ExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'c:\test.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
> select * into #temp from ExcelSource...Sheet1$
> select * from #temp
> At least this works for me. HTH.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23QdZMoShEHA.592@.TK2MSFTNGP11.phx.gbl...
>> Hi Uri
>> I think that I might be missing some basic knowledge about using
>> these features. I can't get it to work with OpenDataSource either.
>> It gives me the same error as before when using the LinkedServer
>> option.
>> What I'm missing, is which userId and Password I have to supply? If
>> I supply anything else than "admin" as in your example, it gives me
>> an "authentication failed" error. That kind of indicates that it do
>> get authenticated with the admin userId, but that sounds a bit
>> strange to me. I'm also unsure about the last bit of the string
>> -..Book1$? Is that something I have to change to something else, or
>> is it just the default "sheet" (..I can't remember what it's called
>> in english - I'm sitting with a danish Excel - unfortunately).
>> I'm sorry if I sounds stupid with these questions, but I don't find
>> BOL very detailed on this subejct. If any of you have any links to
>> other placed where I can find some more detailed info about it, I'd
>> be happy about that.
>> Regards
>> Steen
>>
>> Uri Dimant wrote:
>> Steen
>> One option is to use OpenDataSource. Run this script on QA
>> SELECT *
>> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source="c:\MyExcel.xls";
>> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:%23WI%23WjRhEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I'm having some data in some Excel spreadsheets, that I'd like to
>> get into a few tables in a SQL database.
>> What's the best/easiest way to do this with T-SQL?
>> I can do it with DTS, but I'd like to be able to do it with T-SQL
>> so I'm able to better control what's going to happen with the data.
>> I've tried to add my Excel sheet as a linked server, but then I
>> simply can't figure out how to select the columns in my sheet.
>> (...apparently I don't quite understand the explanation in BOL). It
>> says that I have have to give a range of cells a name, but maybe I
>> have done this wrong. In Excel I've went to Insert > Name and then
>> added a name that points to the first column in my sheet. When I
>> then select the column, it then shows the name in the upper left
>> corner, so it actually seems ok.
>> When I then i QA runs a select statement "select * from
>> excelsource...filnavn" where excelsource is the "name" of my linked
>> server, I get the error message :
>> 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.].
>> This doesn't tells me a lot...
>> Can any of you guide to what I can check to make this work, or
>> maybe give me some other ideas of how to get data from Excel?
>> Thanks a lot...
>> Regards
>> Steen