473,847 Members | 1,452 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel datatypes

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

strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + Filename +
";Extended Properties=\"Ex cel 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(in sert into [Ark1$] ([MyText], [MyInt]) values (?,?));
com.Parameters. Add("@MyText", OleDbType.VarCh ar).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("s elect *
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
1 10632
Jan,

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

System.Data.Ole Db.OleDbConnect ion conn = new
System.Data.Ole Db.OleDbConnect ion(strConnect) ;
string strSql = "CREATE TABLE SampleTable ( ix NUMBER, CustName
char(255), Stamp datetime )";
System.Data.Ole Db.OleDbCommand cmd= new
System.Data.Ole Db.OleDbCommand (strSql, conn);
conn.Open();
cmd.ExecuteNonQ uery();

conn.Close();
then you can insert as you like, eg:

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

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

System.Random r= new System.Random() ;
cmd.Parameters. Add("@p1", System.Data.Ole Db.OleDbType.Nu meric).Value =
r.Next(42);
cmd.Parameters. Add("@p2", System.Data.Ole Db.OleDbType.Va rChar).Value =
"Some text";
cmd.Parameters. Add("@p3", System.Data.Ole Db.OleDbType.Da te).Value =
System.DateTime .Now;

conn.Open();
cmd.ExecuteNonQ uery();
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******** ********@TK2MSF TNGP09.phx.gbl. ..
Im writing information into an existing excel document using a connection
string like:

strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + Filename +
";Extended Properties=\"Ex cel 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(in sert into [Ark1$] ([MyText], [MyInt]) values (?,?));
com.Parameters. Add("@MyText", OleDbType.VarCh ar).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("s elect *
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5971
by: Jenni | last post by:
Hi again, What I need to do is get an entire table from access into excel. It would have to over-write the existing data on the spreadsheet, and it should not run unless it is asked to. (this is why the linked table or MS Query method will not work.) Is it possible to make this code write directly to Excell instead of to the new table?
2
1645
by: RustyR | last post by:
Hi, I have 5 excel spreadsheets that are exported to me weekly. At first, I could not get them to import. Then I did a File -> Save as and noticed that the Filenames of the spreadsheets contained Quotes around them - hence the problem. Is there any way to remove those quotes prior to import (other than "Save As")???
8
13961
by: Horst Walter | last post by:
I create an Excel worksheet in C# (should be similar in VB) Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; Create string: "CREATE TABLE S1 (Dt date, St char(40), Cr currency)" Works fine, but the worksheet is named _S1 (UNDERSCORE!) and not S1.
14
2291
by: Raoul Snyman | last post by:
Hi, I need to be able to programatically create excel spreadsheets in ASP.NET (C#), WITHOUT having MS Office on the server. Does anybody have any advice/links/whatever for me? Thanks in advance, Raoul.
1
328
by: Jan Agermose | last post by:
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 (, ) values ('Some text', 42)"
2
3509
by: madeleine | last post by:
I'm hoping the answer to this is that I'm just doing something silly, but I'm really scratching my head over this one. I'm importing data from multiple workbooks, each workbook has a sheet called SubSAT and I need to get specific data from that into the database. I'm finding that if I have the workbook open on my desktop then the SubSAT % complete and the High level plan % come in fine, but as soon as the workbook is closed they come...
6
8558
by: elangovan.cse | last post by:
Hi all, I'm exporting a datagrid to an excel file. I'm using the following source code to export. DataTable dt = new DataTable(); DataAccess.StoredProcedure sproc = new DataAccess.StoredProcedure("sproc_getdata"); sproc.Run(dt);
4
2133
by: indrajith_varma1 | last post by:
Hi I am working on an application to retrieve data from excel and insert into SQL Server database. Yesterday, I faced a strange problem. In a column in the excel, if 1st 8 rows are number, then as soon as an alphanumeric or string value is encountered, the string becomes null and put into the data set. IMEX property of the excel connection object is set as 1. Is there any solution in the .NET code to fix this issue? In anticipation...
7
12084
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9490
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7888
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7061
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.