I have a excel xlsx workbook that needs to be loaded into various SQL tables after the file's data has gone though various business logic checks. There can be multiple excel files as input to the program so i'm using the standard connection string and a data adapter to append the entire sheet into a DataTable so all the data can be checked at the same time.
The code below works for this (sort of).
excelCon = new OleexcelCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";");
excelCon.Open();
strExcelSheetNames = GetSheetNames(excelCon);
for (int x = 0; x < strExcelSheetNames.Length; x++)
{
excelCmd.CommandText = "SELECT * FROM [" + strExcelSheetNames[x] + "]";
excelCmd.CommandType = CommandType.Text;
excelCmd.Connection = excelCon;
excelAdapter = new OleDbDataAdapter(excelCmd);
excelAdapter.Fill(dtExcelConvert);
}
I'm having an issue with the number columns however, if the number in the xlsx file is 612561120001 and it's displayed as 6.12561E+11 when i read it from the data table and convert it to a number it appears as 612561120000.
Here's an example of what i mean.
I have tried to convert the file to a csv file and read it in but they're comma's in a lot of the fields which cause issues when splitting the data.
Any help on this would be great.
Thanks