none
urgent (External table is not in the expected format)

    Question

  •  

    Hi,

         I have 7 excel files that i have to import to sql server database and in that 6 of them work fine. But when i try to import the 7 one i get an

     

    oledb exception saying that External table is not in the expected format.

     

    So can i someone please help me with this error.

     

    Regards

    Karen

     

     

     

    Friday, August 10, 2007 12:53 PM

Answers

  • Did you try to do a Select * to see if the columns you are expecting exists in the excel file ? Maybe there are some mis-written column names which cannot be found in the excel file which is assume. For debugging purposes use a try catch and execute the command with Select * in the catch path to see if the (in the command) mentioned columns exist.

    Jens K. Suessmeyer


    ---
    http://www.sqlserver2005.de
    ---

    Friday, August 10, 2007 3:47 PM

All replies

  • How do you import them, using a TSQL command / DTS package / Import Wizard etc. ?

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Friday, August 10, 2007 3:26 PM
  • I am importing them thru my asp.net website and this is my code

     

    Code Snippet

    If (flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".xls") Or flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".csv")) Then

    flSubAdvisor.PostedFile.SaveAs(location6)

    ' Connection String to Excel Workbook

    Try

    Dim excelConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location6)

     

    '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=location;Extended Properties=Excel 8.0"

    ' Create Connection to Excel Workbook

    Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(excelConnectionString)

    Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select Cusip,FundName,ChartHeader,Dates,NAV FROM [Sheet1$]", connection)

    connection.Open()

    ' Create DbDataReader to Data Worksheet

    Using dr As Data.Common.DbDataReader = command.ExecuteReader()

    ' SQL Server Connection String

    Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION) '"Data Source=.; Initial Catalog=Test;Integrated Security=True"

    Dim myconnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))

    Dim query As String = "Truncate Table Growth"

    myconnection.Open()

    Dim cmd As New SqlCommand(query, myconnection)

    cmd.CommandType = CommandType.Text

    cmd.ExecuteScalar()

    myconnection.Close()

    ' Bulk Copy to SQL Server

    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)

    bulkCopy.DestinationTableName = "Growth"

    bulkCopy.WriteToServer(dr)

    End Using

    End Using

    connection.Close()

    End Using

    Catch ex As Exception

    Throw ex

    End Try

    End If

     

     

     

     

    It stops at connection.Open()

     

    Regards,

    Karen

    Friday, August 10, 2007 3:29 PM
  • Did you try to do a Select * to see if the columns you are expecting exists in the excel file ? Maybe there are some mis-written column names which cannot be found in the excel file which is assume. For debugging purposes use a try catch and execute the command with Select * in the catch path to see if the (in the command) mentioned columns exist.

    Jens K. Suessmeyer


    ---
    http://www.sqlserver2005.de
    ---

    Friday, August 10, 2007 3:47 PM
  •  

    I have made sure that the column names match the excel spread sheet

    I have also eliminated most of my columns and just kept one column but the problem still exists. the connection part in a try and catch block and will see what happens

     

    Regards

    Karen

    Friday, August 10, 2007 3:51 PM
  • Thanks a lot,

     

    I found out the bug, and it was on this line

    flGrowth10K.PostedFile.SaveAs(location6)

     

    Had given flSubadvisor.PostedFile.SaveAs(location6) instead of the above

     

     

    and there was no file in  flSubadvisor.PostedFile.SaveAs(location6) ... so it was giving me that problem.

     

    Regards

    Karen

     

    Friday, August 10, 2007 4:01 PM