• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12161
  • Last Modified:

Import Excel Data into new SQL Server 2005 DB

I need to create a SQL Server database from data in an MS Excel spreadsheet
0
moransa
Asked:
moransa
  • 2
1 Solution
 
YveauCommented:
yep, here's a nice way to do that:
1. In the excel sheet, select a range that you want as a table.
2. Select from the menu insert, name, define ...
3. Give in a name for the selected area.
4. repeat this step for each table you want in SQL Server.
5. close the excel sheet.
6. from sql server create the tables.
7. for each table run the script:
insert into <Table>
SELECT  *
FROM    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="X:\Path\To\File.xls";Extended Properties=Excel 8.0')...[AreaName]

... done.

Tables need to be created by hand this way.
If you are looking for a automatic way to create the tables as well, use the import/export wizard:
Open a connection to the database from SSMS, in the object explorer pane, right click the database and select tasks, import data. From there follow the wizard.

Hope this helps ...


0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
 
Prasad_sivaCommented:
yep, here's a nice way to do that:
1. In the excel sheet, select a range that you want as a table.
2. Select from the menu insert, name, define ...
3. Give in a name for the selected area.
4. repeat this step for each table you want in SQL Server.
5. close the excel sheet.
6. from sql server create the tables.
7. for each table run the script:
insert into <Table>
SELECT  *
FROM    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="X:\Path\To\File.xls";Extended Properties=Excel 8.0')...[AreaName]

... done.

Tables need to be created by hand this way.
If you are looking for a automatic way to create the tables as well, use the import/export wizard:
Open a connection to the database from SSMS, in the object explorer pane, right click the database and select tasks, import data. From there follow the wizard.

Hope this helps ...
sql is getting  this while excuting  the above said soulution by Yveau, SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
0
 
jgroetchCommented:
I defined an area in my spreadsheet called "DataFeed" as follows, =Sheet1!$B$2:$I$500

then in Qry Analyzer I run the following select,

select * into sheetdata from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=d:\MarketData.xls',DataFeed)

however, I noticed that the resulting column in my Sql table are not in the same order (i.e. column B and C should be my first two rows, but are coming in as the last two).

Any ideas on how to specify the order of which I pull my columns from the spreadsheet ?
Thanks,
Bob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now