Hi all - Last resort time.
I'm importing data from a spreadsheet that I receive from one of my vendor
using interop.excel.
The date field in excel is entered as 4/7/2006, but when I retrieve the
value, it returns a double 38449.0.
I can't figure out how to convert this value into a proper date. I have no
Idea what excel is doing with this date. Also, when I enter in 38449 into
that SAME column in excel it DOES convert it to 4/7/2006.
Here is some code in case anyone is interested in what I'm doing. The last
line is where its blowing up. Look for Convert.ToDateTime...
--------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet wks=null;
Microsoft.Office.Interop.Excel.Range rng = null;
int intRows;
object[,] data = null;
object missing = Type.Missing;
System.Data.DataTable
dtMedicalInvoices=dsMedicalInvoices1.Medical_Sales _Tracing_Invoices;
System.Data.DataTable
dtMedicalBranches=dsMedicalBranches1.Medical_Sales _Tracing_Distributor_Branches;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);
//excel.Visible = true;
wb.Activate();
}
catch (COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
wks = (Worksheet)wb.ActiveSheet;
intRows = wks.UsedRange.Rows.Count;
for (int I=2;I<=intRows;I++)
{
//Range of the columns in the worksheet I to AB
// Don't get confused between int I and the worksheet column I
rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());
data = (object[,])rng.Value2;
DataRow x=dtMedicalInvoices.NewRow();
x[0] = Convert.ToDateTime(data[1,2]);
} 2 10035
The following will solve your problem:
double d = 38449.0;
DateTime dt = DateTime.FromOADate(d);
// dt is now a .NET date time.
Basically it's passing it as an OLE Automation Date value. The above static
method will convert it into a DateTime structure.
HTH
- Andy
"jereviscious" <he**@there.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
Hi all - Last resort time.
I'm importing data from a spreadsheet that I receive from one of my vendor
using interop.excel.
The date field in excel is entered as 4/7/2006, but when I retrieve the
value, it returns a double 38449.0.
I can't figure out how to convert this value into a proper date. I have no
Idea what excel is doing with this date. Also, when I enter in 38449 into
that SAME column in excel it DOES convert it to 4/7/2006.
Here is some code in case anyone is interested in what I'm doing. The last
line is where its blowing up. Look for Convert.ToDateTime...
--------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet wks=null;
Microsoft.Office.Interop.Excel.Range rng = null;
int intRows;
object[,] data = null;
object missing = Type.Missing;
System.Data.DataTable
dtMedicalInvoices=dsMedicalInvoices1.Medical_Sales _Tracing_Invoices;
System.Data.DataTable
dtMedicalBranches=dsMedicalBranches1.Medical_Sales _Tracing_Distributor_Branches;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);
//excel.Visible = true;
wb.Activate();
}
catch (COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
wks = (Worksheet)wb.ActiveSheet;
intRows = wks.UsedRange.Rows.Count;
for (int I=2;I<=intRows;I++)
{
//Range of the columns in the worksheet I to AB
// Don't get confused between int I and the worksheet column I
rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());
data = (object[,])rng.Value2;
DataRow x=dtMedicalInvoices.NewRow();
x[0] = Convert.ToDateTime(data[1,2]);
}
Beautiful, Thanks.
"Andy Bates" <an**@ussdev.comwrote in message
news:e1**************@TK2MSFTNGP05.phx.gbl...
The following will solve your problem:
double d = 38449.0;
DateTime dt = DateTime.FromOADate(d);
// dt is now a .NET date time.
Basically it's passing it as an OLE Automation Date value. The above
static method will convert it into a DateTime structure.
HTH
- Andy
"jereviscious" <he**@there.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>Hi all - Last resort time.
I'm importing data from a spreadsheet that I receive from one of my vendor using interop.excel.
The date field in excel is entered as 4/7/2006, but when I retrieve the value, it returns a double 38449.0.
I can't figure out how to convert this value into a proper date. I have no Idea what excel is doing with this date. Also, when I enter in 38449 into that SAME column in excel it DOES convert it to 4/7/2006.
Here is some code in case anyone is interested in what I'm doing. The last line is where its blowing up. Look for Convert.ToDateTime...
-------------------------------------------------------------- Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet wks=null;
Microsoft.Office.Interop.Excel.Range rng = null;
int intRows;
object[,] data = null;
object missing = Type.Missing;
System.Data.DataTable dtMedicalInvoices=dsMedicalInvoices1.Medical_Sale s_Tracing_Invoices;
System.Data.DataTable dtMedicalBranches=dsMedicalBranches1.Medical_Sale s_Tracing_Distributor_Branches;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excel.Visible = true;
wb.Activate();
}
catch (COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
} wks = (Worksheet)wb.ActiveSheet;
intRows = wks.UsedRange.Rows.Count;
for (int I=2;I<=intRows;I++)
{
//Range of the columns in the worksheet I to AB
// Don't get confused between int I and the worksheet column I
rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());
data = (object[,])rng.Value2;
DataRow x=dtMedicalInvoices.NewRow();
x[0] = Convert.ToDateTime(data[1,2]);
} This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Don.Vonderburg |
last post by:
I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:
1. Changing the format of the...
|
by: Richard Holliingsworth |
last post by:
Hello:
Thanks for your quick response.
I'm trying to import a new Excel file into an A2K table and it's
truncating the data. One of the Excel columns is a text field that can
be up to 2000 characters. When I import it into A2K, that field is
truncated. If I try to inport the file into an EXISTING table that I
define myself where that...
|
by: Mike Dundee |
last post by:
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a...
|
by: ScardyBob |
last post by:
Hello,
I am having trouble importing data from an Excel Worksheet. When I try
to import the data, everything works except certain columns that
contain dates, where it replaces the date with a null in the access
table. To import the data I do the following.
1. Create a table based on the excel spreadsheet setup with the
following query...
|
by: Bharathi |
last post by:
Hi,
I got strucked with reading date value from excel file using
C#.NET.
For Jan-2000 the value I am getting is 36526.0.
For all other dates also I am getting some double value like this.
Is there any manipulation so that I can find out the date entered in
| |
by: danibecr |
last post by:
I'm trying to make a table that will daily count the records imported and save them to a seperate table along with the date imported. But as of now after all the processing is complete I delete everything off of the original import tables. I have tried using a form to get the correct counts and dates which work but in order for it to run...
|
by: teser3 |
last post by:
I am trying to import an Excel 2003 sheet into my Access 2003
database.
I get error message saying sheet could not be imported and it referred
me to an Import error table which showed type conversion error for one
of the columns. Please advise what that means and how I can make it
work?
|
by: puruji |
last post by:
while importing date field from excel to oracle using VB6 i got a problem in date format....they do no match...in excel it gives date in format mm/dd/yy but in oracle i need dd-mm-yyyy so? to do.
insert into tbl_cboss_data(mobileno,client,DATE_ACTIVATION_SWITCH,Application,TARIFF,user_name,machineip,machinetime) values('" & rs.Fields(1) & "','" &...
|
by: NEWSGROUPS |
last post by:
I am in the midst of trying to convert about 25 Access 2000 to Access 2003.
The new environment consists of Office/Access 2003 and Outlook 2003. When
converting the back ends I have no problems. When converting the front ends
I get the following issues and error. If I try to import all the objects
into a blank Access 2003 shell I get a missing...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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...
| |
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. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |