Trouble pulling data from Excel file

  • Thread starter Thread starter eclipx
  • Start date Start date
E

eclipx

I am trying to pull rows from an Excel file into a dataset. The Excel file
is a long list of customer names, addresses, phone numbers, etc. I thought
everything was working great. It looked like the rows had all imported into
the dataset, but then (seemingly at random) certain rows were missing data.
The majority of each row's data is there but zip codes are missing in my
dataset and clearly available in the Excel file. So far is seems that the
zip code column is all that is affected. I haven't taken too close a look at
any other fields. My code for importing Excel file data into a dataset is
listed below. In order to view this data, I set a datagridview's datasource
property to ExcelDataSet.Tables(0). If you need me to send one of the files
I am working with so you can better reproduce this problem, let me know what
email address to send it to. Thanks for any help you can provide. I am
completely stumped by this problem.

Private Sub LoadFile(ByVal worker As BackgroundWorker)

If remExcelFileName > "" Then
If remExcelSheet > "" Then

ExcelDataSet = New DataSet
ExcelDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM
[" & remExcelSheet & "]", conn)

Try
ExcelDataAdapter.Fill(ExcelDataSet)
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try

Else
MsgBox("You must select an Excel Sheet to load. ",
MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Excel Sheet Selection
Required")
End If
Else
MsgBox("You must select an Excel File to load. ",
MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Excel File Selection Required")


End If

End Sub
 
This may not be exactly what you are looking for, but here are a few
comments I have:

1. I am not sure if it is possible to correctly import the data from an
Excel file without knowing the internal format of the file or having a class
that was designed to read the data, which would probably need to know the
internal format (obviously you have found a way to get MOST of the data, but
when was the last time anybody in technology was satisfied with getting MOST
of something?)

2. I recently had a project where I had to get a list of records from an
Excel file and save them into a database. The solution that I came up with
(it requires an extra step, but it's better than entering a couple hundred
records by hand) was to manually open the Excel file in Excel, and save it
as a *.csv file, which is a comma-delimited text file. This will prevent you
from missing certain records and fields. It does have the drawbacks of being
slower and requiring some string manipulation, but it works.

I have never looked or heard about one, but maybe if you looked hard enough
somebody or some third-party has written software that can read an Excel
file into a DataSet. If you would like to see the code I used to parse the
*.csv file if you choose that path, let me know and I will send it to you.
Good Luck!
--
Nathan Sokalski
(e-mail address removed)
http://www.nathansokalski.com/

eclipx said:
I am trying to pull rows from an Excel file into a dataset. The Excel file
is a long list of customer names, addresses, phone numbers, etc. I
thought
everything was working great. It looked like the rows had all imported
into
the dataset, but then (seemingly at random) certain rows were missing
data.
The majority of each row's data is there but zip codes are missing in my
dataset and clearly available in the Excel file. So far is seems that the
zip code column is all that is affected. I haven't taken too close a look
at
any other fields. My code for importing Excel file data into a dataset is
listed below. In order to view this data, I set a datagridview's
datasource
property to ExcelDataSet.Tables(0). If you need me to send one of the
files
I am working with so you can better reproduce this problem, let me know
what
email address to send it to. Thanks for any help you can provide. I am
completely stumped by this problem.

Private Sub LoadFile(ByVal worker As BackgroundWorker)

If remExcelFileName > "" Then
If remExcelSheet > "" Then

ExcelDataSet = New DataSet
ExcelDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM
[" & remExcelSheet & "]", conn)

Try
ExcelDataAdapter.Fill(ExcelDataSet)
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try

Else
MsgBox("You must select an Excel Sheet to load. ",
MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Excel Sheet Selection
Required")
End If
Else
MsgBox("You must select an Excel File to load. ",
MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Excel File Selection
Required")


End If

End Sub
Click to expand...
 
It sounds to me like a cell formatting issue in Excel. Where you're
missing data, check those rows in the Excel file to see how they're
formatted, and how they differ from the data that is coming into the
dataset correctly. I much prefer using a SQL table to an Excel file,
but I have done it, and understand that you may not be able to in this
case. If you can, import the Excel file into SQL with DTS. It may
also help you locate the offending rows and ill-formatted data during
the import.

Hope this helps.

Danny
 
Back
Top