Using OLEDB / Microsoft Jet driver causes “Number

Question

I'm having a problem using OLDEDB from C# to write entries into an excel file. Essentially I want to use an excel template file for formatting some information. The idea is that users can run a query, it'll populate the results into an excel file, where the user can then continue to use excel to maniulate the results.

To do this, I create a "raw_data" tab and use OLEDB to write to it like a table. However I'm running into a problem where the preset calculations. Some columns are text, others are numbers and forumals should reference these columns as soon as the sheet is opened.

Excel Version 2003 C#/.Net 3.5

Here's my connection string

string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filename};Extended Properties=""Excel 8.0;HDR=YES;IMEX=0""";

{filename} get's replaced later in the code with the path to the file

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
  connection.ConnectionString = connectString;
  using (DbCommand command = connection.CreateCommand())
  {

    connection.Open();
    query = "INSERT INTO [raw_prod$] (correlationid, created, ipaddr, nai, started, delta, csid, bytesin, bytesout, cause, bsid, servopt, svzone) VALUES (\"{correlationid}\", \"{created}\", \"{ipaddr}\", \"{nai}\", \"{started}\", {delta}, \"{csid}\", {bytesin}, {bytesout}, {cause}, \"{bsid}\", \"{servopt}\", \"{zone}\")";
    //Replace {} code with actual values skipped
    command.CommandText = query;
    command.ExecuteNonQuery();
  }
}

When this run's the data get's populated as expected, but excel treats the data as text, so stuff like sum(c2:c100) don't work. If I simply highlight an entry and hit enter, it will automatically become text. I tried formatting the column to number in the template, and as you can see I remove the double quotes in the sql.

How can I get excel to treat these entries as numeric right off, so that all the calculations will work?

Thanks

Answer 1

I figured it out, it was actually kind of stupidly annoying. In my excel file for rows 2 - 8 for any column I wanted to be a number I just filled in some dummy numbers. This way the Jet driver when it scans those columns will see the numbers and consider those columns numbers. My inserts from C# are then started on row 9, and I edited the template accordingly to only start calculations from row 9 onward. Really a pain, but it seems to work.

Answer 2

Have you tried modifying the reg setting for TypeGuessRows? HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows should be where to look for Jet 4.0. For debugging purposes you could set it to a number larger than the default of 8, (or ,if absolutely certain the first row is correct , set it to 1.)

Some links to aid in debugging Daily Does and MSDN

Aha! Just spotted the example at the bottom of your code. Try removing the quotes around those values which are numeric

\"{correlationid}\", \"{created}\", \"{ipa

I am guessing would be

{correlationid}, \"{created}\", \"{ipa

Answer 3

I had the same problem with Excel 2007 and solved it by doing two things:

• Using the CREATE TABLE method mentioned by philw above
• Modifying the connection string and adding "xml" after the excel 12.0 part

See the following code sample

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" + scratchFile 
    + @""";Extended Properties=""Excel 12.0 xml;HDR=YES""";   

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    using (DbCommand command = connection.CreateCommand())
    {
        connection.Open();

        command.CommandText = @"CREATE TABLE [RawData$] " + 
            "([Organization] varchar(255), " +
            "[Department] varchar(255), [TotalSales] int, [TotalHours] int)";
        command.ExecuteNonQuery();

        command.CommandText = @"INSERT INTO [RawData$] " +
            "(Organization,Department,TotalSales,TotalHours)" +
            "VALUES('Organization','Department',700,70)";

        command.ExecuteNonQuery();

    }
}

Answer 4

You do it by explicitly telling Jet what type the columns are when you create the table for export:

 "create table [tabName] ([col1] varchar(255), [col2] double)"

The only catch is that if you then try to stick character data into a numeric column Jet will throw when you insert, which is no surprise.

A related issue is how you handle nulls: an empty string is not null as far as Jet's concerned: if you try to insert it into a numeric column, Jet throws up.