C# Excel INSERT Error using OleDb and the Access Database Engine

Question

All, there are many question on this subject but none solve my problem. I have written a fairly involved routine to export a passed DataSet/DataTable to Excel (I have Office 2010 running under Win7) using OleDb and the Access Database Engine. The problem is no matter how I define the columns to be written to Excel all values are exported as TEXT/STRING fields.

I am using the OleDbConnection string

string fileName = @"F:\SomePath\MyExcel.xlsx";
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
    {0};Extended Properties=""Excel 12.0 Xml;HDR=YES;MaxScanRows=0;IMEX=0""", fileName);

and have tried numerous other connection string options with no luck.

I generate the Excel definitions in code, but explicitly these are produced as

CREATE TABLE [MetaTab] ([Table] TEXT,[Field] TEXT,[Seq] NUMBER,[DataLevel] NUMBER)

I then generate the code for inserts, for the example above this is

INSERT INTO [MetaTab$]([Table],[Field],[Seq],[DataLevel])VALUES('B1A','EstabID','1','9')

This works, but all values are written as TEXT. How can I force Excel to take other data formats?

Note: I have tried removing the apostrophes for non-string but this does not work either. I am genuinely stuck and any ideas would be greatly appreciated. Thanks for your time.

Have you tried with HDR=NO, I think that if you create the table, the first row doesn't contain any header — Steve
– Steve, Commented Apr 27, 2012 at 10:44

Andras Zoltan · Accepted Answer · 2012-04-27 10:51:23Z

1

I don't think you can. An excel format is not actually analagous to a database column data type - ultimately the underlying value is always either a number or a string; and then the format determines how it is displayed.

Even if I'm wrong - I personally much prefer to use http://epplus.codeplex.com/ to generate my Excel spreadsheets - you can do a lot of very advanced stuff in it as well as simple stuff like formatting.

answered Apr 27, 2012 at 10:51

Andras Zoltan

42.4k13 gold badges109 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MoonKnight Over a year ago

But I have seen code where there is an existing Excel file and an INSERT statement is used identical to the above and the data is inserted with the correct formatting. I suppose this suggests that the formatting is pre-established in the workbook before the INSERT takes place. It seems odd to me that you can specify the column type with CREATE TABLE and this is not adhered to upon the INSERT...

Andras Zoltan Over a year ago

@Killercam - the oledb provider doesn't have to support every feature of the SQL DDL that you can fire at it - it just has to intepret it correctly. In the case of excel, it makes sense that inserting rows into an existing sheet will use Excel's intelligent formatting propagation behaviour; but beyond that SQL data types are not analogues for excel formats so it makes sense that the provider doesn't interpret them. Is it convenient though? Absolutely not!

MoonKnight Over a year ago

I appreciate that the provider does not have to support the SQL DDL, but the CREATE declaration will not work with out specifying the required column format. This clearly suggests that Excel is working with these types in order to correctly build the table structure - this ofcourse does not seem to be happening. I have had a look at your link and I like it alot! I currently use the 'Microsoft.Office.Interop.Excel' library to export my formatted Excel files and this is slooow and clunky! I like your answer, but I don't belive that you can't do what I want... Thanks for your time.

MoonKnight Over a year ago

I think you are rigth here. I have now attempted editing some core registry values that deal with the default data types - this did not work. Thanks for your help...

2 revs · Accepted Answer · 2012-04-29 06:03:15Z

It is not possible to give the any hint(syntax) to OleDbCommand to insert numeric values while inserting values in excel sheet. It will not display the numeric values as numeric in sheet when you open the sheet.

There is an work around, when first record is created in the excel file then go to first record and reenter the same value in the cell (where numeric data want) of already existing records.

or

You can put a default record with numeric values in that excel sheet.

Sample code is mentioned below to convert the data type to numeric after first record has been created in sheet. Call once the ChangeFormat function when any of the record has been inserted in sheet, further data will be kept in proper format.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {


        System.Data.DataTable xlsData = new System.Data.DataTable();
        string xlsConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\romil.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;\"";
        System.Data.OleDb.OleDbConnection xlsCon = new System.Data.OleDb.OleDbConnection(xlsConnectionString);
        System.Data.OleDb.OleDbCommand xlsCommand;
        int recUpdate;
        int recordsinSheet;

        xlsCommand = new System.Data.OleDb.OleDbCommand("Select count(*) as RecCount from [Sheet1$]");
        xlsCommand.Connection = xlsCon;
        xlsCon.Open();
        recordsinSheet =Convert.ToInt32( xlsCommand.ExecuteScalar());

        xlsCommand=   new System.Data.OleDb.OleDbCommand("Insert into [Sheet1$] (Field1,Field2) values ('123',2)");
        xlsCommand.Connection = xlsCon;

        recUpdate = xlsCommand.ExecuteNonQuery();
        xlsCon.Close();

        if ((recordsinSheet + recUpdate) == 1)
            ChangeFormat();

        Console.ReadKey();

    }

    private static void ChangeFormat()
    {
        string filename = "d:\\romil.xlsx";

        object missing = System.Reflection.Missing.Value ;

        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

        Microsoft.Office.Interop.Excel.Worksheet wsh=null;
        foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
        {
            if (sheet.Name == "Sheet1")
            {
                wsh = sheet;
                break;
            }
        }

        for (int rCnt = 2; rCnt <= wsh.Rows.Count; rCnt++)
        {

            if  ( wsh.Cells[rCnt, 2].Value== null)
                break;

            wsh.Cells[rCnt, 2] = wsh.Cells[rCnt, 2].Value;
        }    

        wb.SaveAs(filename, missing,
            missing, missing, missing, missing,
           Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing,
            missing, missing);
        wb.Close();
    }
}

}

+1 Thanks very much forr the answer. However, this presumes the user has Office installed and the whole point of using OleDb for me in this case is to avoid the use of Interop. I already have a routine that writes to Excel using 'Microsoft.Office.Interop.Excel'. Again thanks, it is a good work around I might use in the future...

Collectives™ on Stack Overflow

C# Excel INSERT Error using OleDb and the Access Database Engine

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related