?
Solved

Read Data from Excel with Mixed Data Type

Posted on 2005-05-06
8
Medium Priority
?
521 Views
Last Modified: 2010-04-23
Good Day gurus,

Please help...
I am using below connection to read in data from excel file into a dataset.

objconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";Extended Properties=Excel 8.0;"

There are mixed data type in some of the columns, which cause missing data.
Example:

Data in Excel :--
Column1Header  Column2Header  Column3Header
A            1            1,000
B            2            2,000
C            3            3,000

But the return data in dataset:--
ColumnHeader1      Null            Null
A            1            1,000
B            2            2,000
C            3            3,000

How to read in all data from Excel that consists of mixed data type in a column ?

Thankss many


0
Comment
Question by:Howard888
  • 4
  • 3
8 Comments
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 300 total points
ID: 13943614

Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"

HDR=Yes; indicates that the first row contains columnnames, not data
IMEX=1; tells the driver to always read "intermixed" data columns as text
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943624
?? does it err in getting the ColumnHeaders??
because the data seems to be same

Also When you read the data in Dataset it automatically judges the dataType, for eg in your case the Second Column should automatically be Integer/Double (it goes for Double as it is safer in case large values are to be accommodated)

0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943630
I think HDR=Yes is the default is it ???

because I didn't give it in the Connection String and it still picked up the Column headers properly....

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943645
Hi Arif_eqbal, Yes it should be the default, but since he still gets it in his dataset. Unless it is not the first row. Thats why I also included the IMEX=1
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943665
right
0
 

Author Comment

by:Howard888
ID: 13956346
Hi,

I tried to include Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
but problem could not be solved.

Infact, my Excel sheet NOT only first row contains columnnames (refer below example).
The 1st column contains all Text datatype, so no problem.
But column2 and column3 contain mixed datatype, it will be treated as Number columns hence return NULL for the headername.
If manually reformat the column2 and column3 as Text datatype, then I can get the columnheader
but all the numbers data would become NULL.
Not sure if I have made myself clear....any help would be much appreciated. Thanksss


=================
Data in Excel :--
=================
Column1Header  Column2Header  Column3Header
A                          1                          1,000
B                          2                          2,000
C                          3                          3,000

Column1Header  Column2Header  Column3Header
X                         4                          4,000
Y                         5                          5,000
Z                         6                          6,000

=======================
But the return data in dataset:--
=======================
Column1Header  Null                            Null
A                          1                          1,000
B                          2                          2,000
C                          3                          3,000

Column1Header  Null                           Null
X                         4                          4,000
Y                         5                          5,000
Z                         6                          6,000
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13957510
I don't know but I tried this code I am posting with a file with Mixed dataType as you say, and it's picking up things just fine.
It smartly takes the headers and the data.

Try this code just pass your file name to the function and see if the DataGrid is filled properly

Private Function FillDataTable(ByVal FlName As String) As Boolean
        Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & FlName & ";" & _
           "Extended Properties=""Excel 8.0;"""
        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter("Select * from [Sheet1$]", CnStr)
        Try
            DA.Fill(DT)
            Me.DataGrid1.DataSource = DT

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True
    End Function
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13957660
Yep, I tried it too with your data, and it works just fine.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=c:\test.xls; " & _
                  "Extended Properties=""Excel 8.0;IMEX=1;HDR=YES""")

        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter("Select * from [Sheet1$]", MyConnection )
        Try
            DA.Fill(DT)
            Me.DataGrid1.DataSource = DT

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True
    End Function
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question