0

How can I import an Excel file into SQL Server database?

I am trying to do this:

INSERT INTO temptable  SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\team4\files\Update_66.xls','SELECT * FROM [Sheet1$]')

I then run into this error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

Is there a way to do this in powershell perhaps?

Please help me.

After reading Rigerta Demiri's code below, Here is my code:

 $dir = "\\server\files\"
    $latest = Get-ChildItem -Path $dir | Sort-Object LastWriteTime -Descending | Where-Object {$_.name -like "xyzfile"}  | Select-Object -First 1
    $SQLServerConnection = "Data Source=rock;Integrated Security=true;Initial Catalog=team;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SQLServerConnection
    $bulkCopy.DestinationTableName = "temptable"
    $bulkCopy.WriteToServer($latest)

this code is giving me this error message:

Multiple ambiguous overloads found for "WriteToServer" and the argument count: "1".
Immortal
  • 1,133
  • 1
  • 15
  • 35
  • Are You sure u can't use wizard of this? Import option? – Whencesoever May 24 '17 at 09:19
  • Are you able to export the excel file as CSV? Bulk Insert can then be used. – Kami May 24 '17 at 09:24
  • Using the wizard can do but I would want to automate this process at some point. I am using powershell to write my scripts. @Kami, am trying to import the file into sql server database not exporting, do you guys have an idea of how I can accomplish this?? – Immortal May 24 '17 at 09:32
  • You could create an SSIS package and call it from a job that runs every 15 mins or so. If a sheet exists in a specified location it will import it, if it doesn't it wont....type of idea. – GandRalph May 24 '17 at 09:52
  • Check this link: https://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked – alex.pulver May 25 '17 at 06:37

0 Answers0