2

I have got huge list of contact information in an Excel sheet that I would like to turn into table in the database so that I can maintain them in the database.

I tried following the import/export flat file import from the SQL Server 2008 R2, but it did not work. Can anyone tell me how do I successfully import the spreadsheet into a table in the database?

Thanks

pnuts
  • 58,317
  • 11
  • 87
  • 139
Sudeep Devkota
  • 189
  • 1
  • 1
  • 11
  • 2
    In the import, you need to choose Excel File and not Flat File. Either that or convert your source file to a CSV and then use flat file in your import. – R_Scott Feb 12 '14 at 15:37
  • Also, note that if you're using the `Import and Export Data` option from the SQL Server start menu group, you will have to use the 32-bit version, as the excel provider does not work with 64-bit. – bhamby Feb 12 '14 at 15:40
  • possible duplicate of [how to import an excel file into sqlserver 2008](http://stackoverflow.com/questions/6123113/how-to-import-an-excel-file-into-sqlserver-2008) – John Odom Mar 23 '15 at 20:11

1 Answers1

3

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Manually

Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination

bhamby
  • 15,112
  • 1
  • 45
  • 66
Jag
  • 291
  • 1
  • 4