Solved

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

Posted on 2016-11-19
4
13 Views
Last Modified: 2016-11-24
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
Comment
Question by:holemania
  • 3
4 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
that error means that the database table is expecting say a date format and you are passing to it a string
0
 

Author Comment

by:holemania
Comment Utility
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
 

Accepted Solution

by:
holemania earned 0 total points
Comment Utility
Okay that was dumb of me.  It's a CSV file, once i change it to xls format, it's fine.
0
 

Author Closing Comment

by:holemania
Comment Utility
Found out the issue was formatting of the excel file.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 44
Excel 2013 Problem 12 47
Maintaining Oracle Managed Accounts 2 16
Import data into excel from web page 10 0
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now