How do I import Excel file into SQL Express???

[cyoung3000]

Hello,

I am total beginner to SQL and want to import an Excel file into SQL but I'm not sure where to start. The Excel file is a small company directory, names, addresses etc., not too big.

Any help would be greatly appreciated!!!!

[kabilahan]

hi..
my adivice is dont use excel, create a MS-Access data base and import that into sql..

[cyoung3000]

thanks for the post.

Assuming I am able to create an MS Access database, what are the steps to importing this file into SQL?

thanks

[kabilahan]

1.right click on any database
2.select TASKS and then select IMPORT DATA after clicking
this one dialog box will appear,click NEXT
3.Choose MICROSOFT ACCESS as DATA SOURCE,then BROWSE ur access file then click NEXT
4.Chosse the DESTINATION[SQL native client]
5.Select the SERVERNAME
6.There are two AUTHENTICATION, select the SQL SERVER AUTHENTICATION and give the USERNAME & PASSWORE
7.Select the DATABASE and then click next
8.select "COPY DATA FROM ONE OR MORE TABLES OR VIEWS" then click next
9.the tables what you created in access will be showed here
10.select the tables which you want to insert and then click next
11.select the "EXECUTE IMMEDIATELY" check box and then click next
12.click finish
13.it takes some time to process and finallY, if there are no mistakes,it executes
successsfuly else it shows the error message

[Tros]

You can't Import / Export using the Express editions.

[rebbing]

You can't Import / Export using the Express editions.

Seems it may be round about, Excel -> Access -> SQL Express.

[Hayden]

When you download the SQL Express software, I believe it comes with a 'wizard' called DTS Wizard. It allows you to transfer among all the previously mentioned types.

[onyris]

Hi .. i just tried to do those steps..but when i go right click on a database and then TASKS ..there is no import data or export or anything.
Can someone tell me another way to import the data from an excel ..or MS Access into SQL .
Thanks

[BHTanna]

Dont you have SQL Enterprise Manager??? Above steps are for the same.

If u want to do the same using SQL Query Analyser, u need to save your excel file on Server's local drive and try following query...

Select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\<filename>.xls; Extended Properties=Excel 8.0')...[<sheet name>$]

Hope this will help.

[onyris]

Hi thanks for replay ...but i still have some problems with that..i'm getting this error message when try to run the query :

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "chemicals$".
The table either does not exist or the current user does not have permissions on that table.

The query looks like this :

Select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Chemical_table.xls; Extended Properties=Excel 8.0')...[chemicals$]

Not sure what i'm doing wrong or what i'm missing ....could u help me again
Thanks

[onyris]

Ah ..i forgot ...i'm using Microsoft SQL Server Management Studio Express

[BHTanna]

Hi,

I hope u have spelled you sheet name correctly...

Request you to rename the sheet which has data to "A" or something very easy....

Other than this, i dont find any other problem..

It should work perfectly....

[onyris]

I just changed the name ...and now i'm getting this error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

Any ideea about this ?

[BHTanna]

Have u changed file path or moved file somewhere else???

It seems, your file does not exists now, may be you have chenged path or renamed it???

Or is it opened??? Then too u will get this error...

If it is in sharing, & used by someone else then too u will get this error...

[onyris]

the file is there ..i'm using the same names..but still getting the first error

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "a$".
The table either does not exist or the current user does not have permissions on that table

I have no ideea what is wrong.

[BHTanna]

Now you are getting first error..

I suggest you to keep your excel file in c:\ of your server and simply try..

It seems you are violating some basic file sharing rules...

As this error indicates, either u dont have rights on this sheet or sheet doesnt exists.

Hope this will help.