473,382 Members | 1,258 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

convert Excel to dataset

Hi
when i convert Excel file to dataset using the following code, i find
that, some col. such as

Col1
------
404
403
NOT
222
is converted as

col1
-----
404
403
<null>
222

..I use this code to convert
Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"data source='" + SaveLocation + " '; " + "Extended Properties='Excel
8.0;IMEX=1;'")
Dim da As New OleDbDataAdapter("select * from
[Sheet1$]", cnn)
da.TableMappings.Add("Table", "TableCom")
Dim ds As New DataSet
da.Fill(ds)

Please help me in this

Aug 4 '06 #1
2 6299
On 4 Aug 2006 06:06:40 -0700, "karups" <al***********@gmail.comwrote:

¤ Hi
¤ when i convert Excel file to dataset using the following code, i find
¤ that, some col. such as
¤
¤ Col1
¤ ------
¤ 404
¤ 403
¤ NOT
¤ 222
¤
¤
¤ is converted as
¤
¤ col1
¤ -----
¤ 404
¤ 403
¤ <null>
¤ 222
¤
¤ .I use this code to convert
¤ Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
¤ "data source='" + SaveLocation + " '; " + "Extended Properties='Excel
¤ 8.0;IMEX=1;'")
¤ Dim da As New OleDbDataAdapter("select * from
¤ [Sheet1$]", cnn)
¤ da.TableMappings.Add("Table", "TableCom")
¤ Dim ds As New DataSet
¤ da.Fill(ds)
¤

The IMEX argument should have taken care of the mixed mode column issue.

You could also try changing the TypeGuessRows setting in the registry to 0. It is found at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

I'm assuming your connection string is being evaluated properly but you may want to try double
quotes instead of single quotes:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SaveLocation & ";Extended Properties=""Excel 8.0;IMEX=1"""
Paul
~~~~
Microsoft MVP (Visual Basic)
Aug 4 '06 #2

Paul Clement wrote:
On 4 Aug 2006 06:06:40 -0700, "karups" <al***********@gmail.comwrote:

¤ Hi
¤ when i convert Excel file to dataset using the following code, i find
¤ that, some col. such as
¤
¤ Col1
¤ ------
¤ 404
¤ 403
¤ NOT
¤ 222
¤
¤
¤ is converted as
¤
¤ col1
¤ -----
¤ 404
¤ 403
¤ <null>
¤ 222
¤
¤ .I use this code to convert
¤ Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
¤ "data source='" + SaveLocation + " '; " + "Extended Properties='Excel
¤ 8.0;IMEX=1;'")
¤ Dim da As New OleDbDataAdapter("select * from
¤ [Sheet1$]", cnn)
¤ da.TableMappings.Add("Table", "TableCom")
¤ Dim ds As New DataSet
¤ da.Fill(ds)
¤

The IMEX argument should have taken care of the mixed mode column issue.

You could also try changing the TypeGuessRows setting in the registry to 0. It is found at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

I'm assuming your connection string is being evaluated properly but you may want to try double
quotes instead of single quotes:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SaveLocation & ";Extended Properties=""Excel 8.0;IMEX=1"""
Paul
~~~~
Microsoft MVP (Visual Basic)


ya that worked for me now
Thats great
I changed in the registry and its fetching the records correctly..
Thank u paul

Aug 8 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Chris | last post by:
Could someone please provide me an effective means of exporting data from a data set (or data grid) to Excel?
9
by: Paul | last post by:
Hi all Arggghhh........... The problem.....I want the user to be able to create an excel document and name particular cells in the document where they want the data to be placed and then save...
7
by: kscdavefl | last post by:
I am running the following code to retrieve a DataSet: public DataView CreateGroupSource() { string groupConnect = Session.ToString(); string groupSelect = "Select grpname from Maxusergroups...
1
by: TaeHo Yoo | last post by:
How to convert a dataset to excel spreadsheet in windows based application?? Thanks a lot in advance. *** Sent via Devdex http://www.devdex.com *** Don't just participate in...
1
by: SL | last post by:
Hi - I'm fairly new to manipulating xml and I have an excel workbook and on one of the sheets is apparently a listobject... using it's xmlmap.export function I get an xml file that looks like this...
2
by: Ronald S. Cook | last post by:
Hi, I need to open an Excel file from within my ASP.NET app and read data. I'm guessing the most like format in ASP.NET would be a DataTable. Regardles,, how do I get the data into my ASP.NET...
2
by: karups | last post by:
Hi when i convert Excel file to dataset using the following code, i find that, some col. such as Col1 ------ 404 403 NOT 222
2
by: Mad Scientist Jr | last post by:
>From an asp.net web page I want the user to open the results of a SQL query in Excel, as automatically as possible (ie not having to loop through columns, rows, in code). For this,...
3
by: binny | last post by:
i use jet engine ,and oledb database but with this i get four errors which r compile time errores,plz help me to solve these errors i wrote this programe bt it is not working . using System.Web;...
1
by: DennisBetten | last post by:
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel. What does this code do: As the title says, this code is capable of...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
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...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
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 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.