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 10020
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...
|
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...
|
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. ...
|
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...
|
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.
...
|
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...
|
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...
|
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....
|
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....
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |