excel import vb.net date converting into numbers

Question

0.00/5 (No votes)

See more:

i am importing excel into my vb.net application

the problem here is date is converting into numbers

i want to prevent date value for converting into numbers as i dont know which column of excel may contain date

please help!!!

my code below

VB

'    Dim conExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & txtPath.Text & " '; Extended Properties=Excel 8.0;")
         Dim conExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & ofdImport.FileName & "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'")
         Dim excelSelect As New OleDbCommand
         Dim excelAdp As New OleDbDataAdapter
         Dim excelSchemaDt As DataTable
         Dim dset As New DataSet

         If conExcel.State Then conExcel.Close()
         conExcel.Open()
         excelSelect.Connection = conExcel

VB

Try
                  excelSchemaDt = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                  Dim sheetName As String = excelSchemaDt.Rows(0)("TABLE_NAME").ToString()

                  excelSelect.CommandText = "select * from [" & sheetName & "] "
                  excelAdp.SelectCommand = excelSelect
                  excelAdp.TableMappings.Add("Table", "TestTable")
                  excelAdp.Fill(dset, "TestingTabl")

m getting all the data in DAtaset dset.Tables("TestingTabl")

Posted 27-Nov-13 21:25pm

Omkaara

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-11-27T21:44:00

Solution 1

Solution 1
To avoid that behaviour, please define the collection of columns:

SQL

SELECT StringField1, IntField2, FORMAT(DateField3, 'MM/dd/yyyy') AS Field3 
FROM [Sheet1$]

or

SQL

SELECT StringField1, IntField2, CDate(DateField3) AS Field3 
FROM [Sheet1$]

Above code should works too ;)

For further information, please see:
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]
Microsoft Jet Database Engine Programmer's Guide - Introduction [^]

Solution 2
Before you start importing data, please, format cells as a date.

More: How to control and understand settings in the Format Cells dialog box in Excel[^]

Posted 27-Nov-13 21:44pm

Maciej Los

Comments

Omkaara 28-Nov-13 5:09am

i do not know which column in excel is date field it may change every time i import
i tried formatting cells as date not working

Maciej Los 28-Nov-13 5:30am

Why don't you know which column is a date type?
"Not working" - is not informative at all ;(

Omkaara 28-Nov-13 5:50am

i import excel and dynamically table is creadted excel is diffrent every time so position of date column changes i cannot detect it from vb 2010 which cell contains date andd which cannot
and i tried formatting column of excel

i wanted to know tht how can i prevent date converting into numbers so that as it is date values are imported

Maciej Los 28-Nov-13 11:55am

In this case i don't know how to prevent conversion date into numeric value... ;(