[RESOLVED] bizarre issue importing Excel data

[silverblatt]

My application needs to import Excel data and drop it into a ListView control; the data may be in .xls, .xlsx, .csv or .psv formats. I'm using ReadExcelSheetData() to import .xls and .xlsx files, and ReadTextFile() to import .csv and .psv files. Everything works as it should with one bizarre exception: when I import .xls or .xlsx files, about 95% of the time, the data in the 7th and 8th cells of the first row (i.e., cells G1 and H1) is coming in as blank, regardless of the actual content of the cells. When I save the .xls or .xlsx file to either .csv or .psv formats and then import the .csv or .psv file, everything comes in exactly as it should.

At first, I thought it was some issue with a particular Excel file being corrupted, or with items in the first row containing pound signs, so I've spent hours and hours creating fresh files with nothing but letters in the first rows, but the problem persists. I am not applying any formatting to the Excel files - I'm just entering (or in some cases copying and pasting) the data into the cells and saving the files in the normal way.

It also occurred to me that the data may be coming in properly, but perhaps the issue is due to how I'm handling it or loading it into the ListView control, so I used the 5 lines (now commented out) in ReadExcelSheetData() to see exactly what's coming in before I load it into the ListView or do anything else with it. Doing so confirmed that, for whatever reason, ONLY the data from cells G1 and H1 is coming in blank from the .xls and .xlsx files, even though those cells are all properly populated with text when I view the files in Excel.

I'm about at wit's end, and so I'll greatly appreciate any suggestions.

    Private Sub ReadExcelSheetData()
        Dim I As Int32, J As Int32, clbi As ComboListBoxItem, dr As DataRow, lvi As ListViewItem
        Try
            InitializeListView()
            'read the data from the sheet and load it into the listview
            Using cn As New OleDb.OleDbConnection(ConnectionString)
                clbi = cboWorksheet.SelectedItem
                Using cmd As New OleDb.OleDbCommand("SELECT * FROM [" & clbi.URL & "]", cn)
                    cmd.CommandType = CommandType.Text
                    Using da As New OleDb.OleDbDataAdapter(cmd)
                        Using dt As New DataTable
                            da.Fill(dt)
                            For I = 0 To dt.Rows.Count - 1
                                'add the row number to the listview
                                lvi = New ListViewItem((I + 1).ToString)
                                lvi.ForeColor = SystemColors.WindowText
                                lvi.BackColor = ListViewItemBackColor
                                lvi.UseItemStyleForSubItems = False
                                lvwMain.Items.Add(lvi)
                                'add all the columns in the row as individual subitems
                                dr = dt.Rows(I)
                                For J = 0 To dr.ItemArray.Count - 1
                                    'If I = 0 Then
                                    '    Dim ob As Object = dr.ItemArray(J)
                                    '    Dim st As String = ob.ToString
                                    '    MsgBox("J: " & J.ToString & "; Item: " & st)
                                    'End If
                                    AddListViewSubItem(lvwMain, EmptyStringIfNull(dt.Rows(I).Item(J)).ToString.Trim)
                                Next
                            Next
                        End Using
                    End Using
                End Using
            End Using
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub
    Private Sub ReadTextFile()
        Dim FileSpec As String = lblImportFileSpec.Text, RowCount As Int32 = 0, LineText As String(), lvi As ListViewItem, I As Int32
        Try
            InitializeListView()
            Using tfp As New Microsoft.VisualBasic.FileIO.TextFieldParser(FileSpec)
                tfp.TextFieldType = FileIO.FieldType.Delimited
                tfp.SetDelimiters(If(GetFileNameExtension(FileSpec) = "csv", Comma, Pipe))
                While Not tfp.EndOfData
                    RowCount += 1
                    LineText = tfp.ReadFields
                    lvi = New ListViewItem((RowCount).ToString)
                    lvi.ForeColor = SystemColors.WindowText
                    lvi.BackColor = ListViewItemBackColor
                    lvi.UseItemStyleForSubItems = False
                    lvwMain.Items.Add(lvi)
                    For I = LineText.GetLowerBound(0) To LineText.GetUpperBound(0)
                        AddListViewSubItem(lvwMain, EmptyStringIfNull(LineText(I)).ToString.Trim)
                    Next
                End While
            End Using
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub
    Private Sub InitializeListView()
        Dim I As Int32
        Try
            'clear the listview and set up its columns
            lvwMain.Clear()
            lvwMain.BackColor = Color.LightBlue
            AddListViewColumn(lvwMain, "Row #")
            lvwMain.Columns(0).Width = 50
            For I = 1 To ColumnCount
                AddListViewColumn(lvwMain, ExcelColumns(I))
                lvwMain.Columns(I).Width = 66
            Next
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub

[netcoder1337]

It's possible that the problem you're having could be due to the way the data is being read from the Excel file using OleDb.
Sometimes, OleDb can have trouble reading cells that have certain types of data, such as special characters or formulas.

It's also possible that the problem could be caused by the particular version of OleDb you're using, or by the way the data is being loaded into the ListView control.

There are a few different things you could try to fix the issue. One option would be to switch to a different library to read the Excel files, like the ExcelDataReader library. It might be more reliable than using OleDb.
Another option would be change the way you're loading the data into the ListView control. You could try loading the data into a DataTable object first, and then binding the DataTable to the ListView. This could help make sure that the data is being loaded correctly.

You could also try saving the Excel file in a different format and then importing it again to see if that fixes the problem. If the issue only happens with certain types of Excel files, it might be related to the format of those files specifically.

[silverblatt]

netcoder1337, thanks for your suggestions. I'm pretty sure that the problem is not connected with how I'm loading the data into the ListView, because the data elements in question are already missing before I do the load into the ListView. And while the problem goes away when I import using .csv or .psv files, part of the functionality of the application is to also import directly from .xls or .xlsx - the application is intended for non-technical users, many of whom don't want (or may not know how) to convert a file to a different format.

Regarding the ExcelDataReader library, I'm not seeing that in the References dialog under either Assemblies or COM - how can I access it?

[schoemr]

I had the exact same issue. As it turned out the problem was that the data types of the source data was the problem. You can use Excel Power Query to ensure that your source data is formatted correctly before the import. In Power Query you only have to do that once and all subsequent source data will be correctly formatted as Power Query will remember what you did. If you never used Power Query before, trust me it is worth the effort to get to know it.

Or else you have to specify the correct data types for each column in your method above.

https://youtu.be/0aeZX1l4JT4

[silverblatt]

Thanks, schoemr. If I understand you correctly, Power Query is a tool for correctly formatting Excel data BEFORE it is imported by another program. If that's correct, it won't help me in this situation because my application needs to work with Excel files created by essentially naive users who typically won't have the inclination, patience or expertise to use Power Query. If I've misunderstood, please correct me.

You mentioned that I could also "specify the correct data types for each column". What would I need to do differently, given that the cells I'm having problems with contain string data exclusively and I'm already using the ToString() method for every cell?

[schoemr]

How do your application get the raw data in the first place? Meaning someone must place the files somewhere?

I am typing from my phone now, but there are serval ways to explicitly specify a data type. One such way you can try is to change your select statement to SELECT CAST([Column1] AS INTEGER) AS Column1 etc

[silverblatt]

The application's main purpose is to do collection management for hobbyists. The primary means of getting the data in will be the user simply typing it into various data entry screens. The import feature is intended to allow users who may already have their data stored in Excel files to directly import those files into the application rather than having to enter the data manually one record at a time. Part of the challenge of designing the import feature is that the exact format of those spreadsheets is completely unpredictable - how many columns, which particular data element is stored in which particular column, how each cell/row/column is formatted, etc., etc. So what I'm doing is first reading in the list of all of the file's worksheet names and displaying them in a drop-down, then having the user select a worksheet name, at which point the entire contents of the selected worksheet is displayed in the ListView control. Then, while viewing the contents of the worksheet, the user makes selections from a series of 7 more drop-downs, each of which indicates the column associated with a particular data element to be imported. So I need to read and display all columns and rows first to allow the user to pick which 7 columns go into which seven columns in my database table.

I'm not sure I could specify instances of "CAST([Column_number] AS STRING) AS [whatever]" in my SELECT statement because I have no way to know in advance how many columns there are - if I specify too few, some data will be missed; if I specify too many, the program throws an exception when it attempts to read a non-existent column. That's why I'm using "SELECT *" to just grab however many columns there are and put them in a DataRow object, at which point I can reference DataRow.ItemArray.Count to know how many items there actually are in that row.

[schoemr]

Hi Silver,

Maybe what you can do is take a sheet that produce the error, and (in excel) format everything to text and see if you still get the same error. If not then at least you have identified the issue and you are halfway out of the woods.

I did a bit of search and they talk about GetSchemaTable method but I'm afraid that level of programming is above me.. (atm)

[silverblatt]

schoemr, your suggestion about formatting worked. All the items had been entered as text (and could not have been interpreted as numeric, or as anything except text), but for whatever reason applying Text formatting to all the cells did the trick. So I will display a prominent message when the user enters the import screening, warning them to check for missing text, and suggesting the formatting trick and/or converting the file to .csv/.psv to resolve any issues. Thanks so much for your help!