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

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!!!!

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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.

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

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

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

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

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

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

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

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 ?

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

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

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

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.

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

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.