Import data from Excel to SQL Server 2008

Question

I'm doing a redesign on a site that uses VB.Net. The system will get a fair bit of data from Excel 97-2003 files, which are uploaded by a third party, then uploaded to SQL 2008. Here's the problem, the files that are uploaded have the extension .P. I've used the below code to try and grab the data and upload to the database.

Dim xlApp As New Excel.Application
    xlApp.Workbooks.Open(Filename:=Server.MapPath("~/ExtractFiles/10-31-13.P"))
    xlApp.ActiveWorkbook.SaveAs(Filename:=Server.MapPath("~/ExtractFiles/10-31-13.xls"), FileFormat:=51)
    If Not xlApp Is Nothing Then
        xlApp.ActiveWorkbook.Close()
        xlApp.Quit()
        xlApp = Nothing
    End If

    PrmPathExcelFile = Server.MapPath("~/ExtractFiles/10-31-13.P")

    plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""")
    cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [10-31-13]", plmExcelCon)

    Dim dt As System.Data.DataTable
    dt = New System.Data.DataTable
    cmdLoadExcel.Fill(dt)
    plmExcelCon.Close()

When the code hits the line cmdLoadExcel.Fill(dt), I'm only getting an error that says "External table is not in the expected format.". I'm assuming that this has to do with the fact that I'm trying to change the file extension. However, I can't seem to open the file with the extension .P.

Is there a method I'm overlooking here? Or is this just not possible when working from a file with a custom extension.

Changing the extension seems actually to be the problem: http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format You have to make sure that the Excel version matches (query and file refer to the same version). — varocarbas, Nov 07 '13 at 15:14
Thanks for the hint! I found the problem and added the correct code. I'll put up the answer once the "8 hour" rule clears. — Swicky, Nov 07 '13 at 15:47

score 0 · Accepted Answer · answered Nov 08 '13 at 12:40

Alright, I found the answer thanks to the hint by varocarbas. For future reference, double check the OledbConnection against the Excel file type. After I put in the following changes it worked:

plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [10-31-13$]", plmExcelCon)

First, to work with my version of Excel, I needed to use ACE.OLEDB.12.0 and Excel 8.0. Second, I had to add a simple '$' to the end of the table select, otherwise it wouldn't be able to find the right worksheet.

Import data from Excel to SQL Server 2008

1 Answers1