Inserting data from an Excel Spreadsheet into Microsoft Access using C# not working for specific fields

Question

Currently importing data from Excel into a MS Access database using a C# client, which works perfectly the majority of the time.

Having specific instances wheres it doesn't work with no error being thrown. For the life of me i cant understand why.

My SQL Statement:-

INSERT INTO [MS Access;DATABASE=C:\\TempReport.mdb].[Bucket]
(ID,[RefName],[0M],[3M],[6M],[1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[12Y],[15Y],[20Y],[25Y],[30Y],[40Y],[40Y+]) 
SELECT
7,[RefName],[0M],[3M],[6M],[1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[12Y],[15Y],[20Y],[25Y],[30Y],[40Y],[40Y+])  FROM [MainReport$C29:AA48]

ConnectionString:- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FILE];Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";

Problem:- Columns 20Y,25Y,30Y,40Y, [40Y+] never populate even if theres data, with no error being thrown.

Please Help!

Did you execute the "SELECT ..." part independently to check whether reading succeeds? If it does, double check the field types. Does the writing works, if you use literal values instead of reading them from Excel? — Ekkehard.Horner

user497382 user497382 · Accepted Answer · 2011-03-10T17:18:35

Found the answer with some trial and error.

Changed the IMEX setting in the connection string to 1, from 0. This now works.

Inserting data from an Excel Spreadsheet into Microsoft Access using C# not working for specific fields

1 Answers