Excel datatypes

[Jan Agermose]

Im writing information into an existing excel document using a connection
string like:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Filename +
";Extended Properties=\"Excel 8.0;HDR=yes;\"";

and one of to methods for inserting data. First I simply tried building
insert statements as strings

"insert into [Ark1$] ([MyText], [MyInt]) values ('Some text', 42)"

And I also tried

com = OleDbCommand(insert into [Ark1$] ([MyText], [MyInt]) values (?,?));
com.Parameters.Add("@MyText", OleDbType.VarChar).Value = "test";
com.Parameters.Add("@MyText", OleDbType.Int).Value = 42;


Now both methods do insert the values into the excel, but there is one very
big problem. When you open the excel document, the int values are inserted
as text! Excel markes the column with an warrning and this allowes you to
convert the "text" into prober integer values. The real problem is, that if
you fail to make this conversion, and enter/alter data in the "int-column"
you will be unable to load the excel data using an OleDbCommand("select *
from [Ark1$]");

Does anyone know a solution - or a nother newgroup to post to, if this is
not the place?

Jan Agermose

 

[Dino Chiesa [MSFT]]

Jan,

How has the table been created?
When you do the "create table", you can specify the datatypes.
Eg,
string strConnect=
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Filename + ";" +
"Extended Properties=\"Excel 8.0;HDR=yes;\"";

System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection(strConnect);
string strSql = "CREATE TABLE SampleTable ( ix NUMBER, CustName
char(255), Stamp datetime )";
System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();

conn.Close();


then you can insert as you like, eg:

string strSql = "insert into [SampleTable] ([ix],[CustName],[Stamp])
values(@p1,@p2,@p3)";

System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(strSql, conn);

System.Random r= new System.Random();
cmd.Parameters.Add("@p1", System.Data.OleDb.OleDbType.Numeric).Value =
r.Next(42);
cmd.Parameters.Add("@p2", System.Data.OleDb.OleDbType.VarChar).Value =
"Some text";
cmd.Parameters.Add("@p3", System.Data.OleDb.OleDbType.Date).Value =
System.DateTime.Now;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

If the document already exists and you do not get a chance to explicitly
create the table (and specify datatypes at that time), then I don't know
what you need to do. At one point the driver was set up to inspect the
first 8 rows of the data and use a heuristic to set the datatypes for the
columns, but I don't know if that is still true.

-Dino