ODP.NET

Hi All -

I am loading a dataset with several datatables. These tables are filled with an ODBC Connection to a Excel file. For every worksheet in the excel file, I am doing a select * from into a new datatable which is added to my dataset. I'm having an issue where the datatables are being loaded properly from the ODBC source, however, when OracleBulkCopy loads the datatable to a table in the Oracle Database, the number data changes. IE:

This is what my excel file looks like (only one sheet, named "Sheet1".)

TestColumn
1
2
3
4
5
6
7
8
9
10


I load this data into a datatable starting from line 2, line 1 is used as my datatable header. The data that is loaded into the datatable also looks like this:
1
2
3
4
5
6
7
8
9
10

However, when the data is bulk loaded into Oracle, the table in Oracle looks like this:
0
0
0
0
10
10
10
10
10
0

Here is my code that properly loads the datatables with CORRECT data.

String strConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + this.FileLocation + ";ReadOnly=0";
OdbcConnection objConn = new OdbcConnection(strConn);
objConn.Open();

foreach (string s in tables)
{
OdbcDataAdapter adp = new OdbcDataAdapter("SELECT * FROM [" + s + "$]", objConn);

string table = cleanName("TEMP_" + s);
if (workbookSheets.Tables.Contains(table))
table = "TEMP_" + (workbookSheets.Tables.Count + 1).ToString();

System.Data.DataTable t = new System.Data.DataTable(table);
adp.Fill(t);
workbookSheets.Tables.Add(t);
}

objConn.Close();

Here is my code for bulk loading that has the issue it seems and loads the data INCORRECTLY:

public void BulkLoad(System.Data.DataTable dt)
{
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default))
{
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.BulkCopyTimeout = 120;
bulkCopy.BatchSize = 50000;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}

All of the columns in Oracle and the Datatable are one to one mappings, the data types correlate and the column indexes correlate also. I can't figure out what can be causing this issue? I'm using version 2.111.7.20 of Oracle.DataAccess (11.01.00.07 for odbc driver installed with odac)

Edited by: user5812170 on Apr 7, 2010 9:26 AM

Edited by: user5812170 on Apr 7, 2010 9:26 AM

Edited by: user5812170 on Apr 7, 2010 9:28 AM