Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

VB.Net - Read Excel file into Oracle table Error - External Table is not the expected format

I am trying to read a csv file into an Oracle table using BulkCopy.  I had done this with SQL and pretty straight forward, but with Oracle, once I get to the code
excelstrCon.Open()
I get the error that
Table is not the expected format.

So not sure what's going on.  I am reading an excel csv file using Office 2003.  I did install the AccessDatabaseEngine.  Not sure what else I need to do.  Can someone look at the below code snippit and let me know what I am missing?

 Try
            'Connection string
            Dim excelCon As String
            Dim conString As String = GetConnectionString()


            '2007 & 2010 connection string
            excelCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFile.Text + ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"""

            Dim excelstrCon As New OleDb.OleDbConnection(excelCon)
            excelstrCon.Open()


            'Create objects and get data
            Dim cmd As New OleDb.OleDbCommand("SELECT [ID], [NAME], [ADDRESS], [CITY], [STATE], [ZIPCODE] FROM [sheet1$] ", excelstrCon)
            Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader


            'open the destination data
            Dim dbCon As OracleConnection = New OracleConnection(conString)

            dbCon.Open()

            Dim bulkCopy As OracleBulkCopy = New OracleBulkCopy(dbCon)

            bulkCopy.DestinationTableName = "CUSTOMER_ADDRESS"
            bulkCopy.BulkCopyTimeout = 500
            bulkCopy.WriteToServer(reader)
            reader.Close()

            MsgBox("Complete")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Open in new window

0
holemania
Asked:
holemania
  • 3
1 Solution
 
David Johnson, CD, MVPOwnerCommented:
that error means that the database table is expecting say a date format and you are passing to it a string
0
 
holemaniaAuthor Commented:
Sorry I missed the word "External" in my error I mentioned.  The error indicate "External Table is not the expected format".  It does not even get to the part where it is reading the query and doing the bulkload.  I get that error as soon as it gets to the following code.

excelstrCon.Open()

So it does not even know which table from Oracle I am doing the data load.  Something up with reading from the excel spreadsheet and thinking it's my connection string with Excel that caused the error.
0
 
holemaniaAuthor Commented:
Okay that was dumb of me.  It's a CSV file, once i change it to xls format, it's fine.
0
 
holemaniaAuthor Commented:
Found out the issue was formatting of the excel file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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