467,892 Members | 1,875 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,892 developers. It's quick & easy.

Excel datatypes

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


Jul 21 '05 #1
  • viewed: 10236
Share:
1 Reply
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
"Jan Agermose" <ja*@topix.dk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Jenni | last post: by
8 posts views Thread by Horst Walter | last post: by
1 post views Thread by Jan Agermose | last post: by
2 posts views Thread by madeleine | last post: by
6 posts views Thread by elangovan.cse | last post: by
4 posts views Thread by indrajith_varma1 | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.