Difficulty reading from excel with .Net

[steelcity_ballin]

Title says it all. I build a file uploaded that works fine, and upon receipt of a file, it passes the file location to the following function:

Code: Select all

Function fillColumnHeaders(filePath)
      Dim h as new hashtable
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader         
      if dr.read then            
      Dim strColNum
         for x = 0 to lookup.columns.count
            strColNum = "col" & x
            h.add(strColNum, dr(x))
            response.write (x & "&nbsp;" & h("col" & x) & "<br/>")
         next
      end if
      response.write("<br/>" & lookup.columns.count)
      cnExcel.Close()
      dr.Close()
   end function


It will write out the proper header row column names up to 9 (which I can't figure out what that is) and if you look at my for loop, the max is set to "lookup.columns.count" - which is what 9 equates to.... unfortunately my file has 11 columns, not nine. If I do lookup.columns.count + 2 if gets it all which leads me to believe that "lookup.columns.count" is not what I want. It needs to be dynamic, and obviously + 2 doesn't work for all files, just this one. I'm not sure what I need to be retrieving as I figured lookup.columns.count would give me the total columns in row 0 - it doesn't. Clearly I'm making a mistake here but it's not clear to me what, only where.

[UberGerbil]

There's nothing wrong with treating an Excel sheet as JET db (as long as it truly contains data consistently laid out that way) but I assume you're going that route rather than using the .net Office assemblies for some reason?

Looking at your code (and keeping in mind it's been years since I did anything like this), these lines puzzle me

Code: Select all

      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.rows(0)("TABLE_NAME") & "]", cnExcel)

What is lookup? Is it declared with a type somewhere else? I'm confused about what the source of this data is, I guess.

Strictly speaking, whenever you have a .Count property you should be iterating from 1 to Count, or from 0 to Count -1.

That you're off by two, or actually 3 (since you're going from 0 to Count) suggests that there's some problem with what you think that lookup variable is giving you.

Did you consider going with an OledbDataAdapter and just binding it to a DataGrid (which you could make read-only if you don't want users messing with the data) rather than manually outputting the rows of cells? This is a pretty primitive way of displaying data, though it might not matter if there aren't many rows.

[steelcity_ballin]

Thanks for the reply. Actually, the count is spot on. I was trying to figure out where the 9 was coming from so I did the following:

Code: Select all

if dr.read
         for each x in lookup.columns
            response.write(x.tostring & "<br/>")
         next
      end if


It actually prints out 9 different things,
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED


so that mystery is solved, but im unsure of how to access the actual count of the number of columns.Those 2 puzzling lines are responsible for creating the connection to my excel file, and then selecting everything in the "table" whose name I won't know, hence the lookup.rows(0)("TABLE_NAME"), which returns the table commonly called [sheet1$] but it could be renamed to something else.

[emorgoch]

There's nothing wrong with treating an Excel sheet as JET db (as long as it truly contains data consistently laid out that way) but I assume you're going that route rather than using the .net Office assemblies for some reason?

The PIA's require the Office to be installed to run. Their purpose is to act as a wrapper to make remote calls to manipulate the programs, similar to how VBA macros run.

[dragmor]

With anything to do with .NET and excel we use http://www.spreadsheetgear.com

Good for just getting data or writing custom excel files from your program. It does NOT require excel to be installed.

[steelcity_ballin]

In case anyone was curious, I finished the first part this morning. I needed to access the FieldCount method of the OleDbDataReader, and in this scenario, it becomes necessary to use the -1 on the count because it returns the index out of bounds if you do not when accessing the last element.

Code: Select all

Function fillColumnHeaders(filePath)
      'Get all header row information
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader
      If dr.Read Then
         For x = 0 To dr.FieldCount-1
                Response.Write(dr(x) & "<br/>")
            Next
      end if      
      dr.Close()
      cnExcel.Close()      
   end function


Now, with this, It doesn't matter what the sheet name is called in the excel document, it will grab it regardless.

[UberGerbil]

In case anyone was curious, I finished the first part this morning. I needed to access the FieldCount method of the OleDbDataReader, and in this scenario, it becomes necessary to use the -1 on the count because it returns the index out of bounds if you do not when accessing the last element.

You know, a few years ago I would've been able to tell you that off the top of my head. But things fade from lack of use. Sorry.

[steelcity_ballin]

I have the entire thing working now. Upload a file, set column headers, allow them to do matching if their column headers aren't named the same thing, then display a preview dataGrid, and insert into my msSql db. I have one tiny minor annoyance left. When I create the dropdownlist items on the fly (see below) I do so in a loop, and outside the loop I do 2 things: First I add a blank value to the 0 position of each dropdown list that has a value of -1, and originally I had that selected. I then found out the client has a format for their clients so that in most cases the 0 position (or in my case now, the 1 position) will always be prefix, firstname is always 2, etc. So I wanted to default these selected Indices. Anyhow, my problem is that it won't work, when I set it, no matter what I try the last index set effects all the other indices - I am sure it has to be something stupid.

Code: Select all

 Function fillColumnHeaders(filePath)
      Dim dbConn as SqlConnection = new SqlConnection(configurationsettings.appsettings("connMK"))
      Dim rs as SqlDataReader
      Dim strSql as SqlCommand
      'first delete any existing records for this orderID if they exist.
      dbConn.open
      strSql = New SqlCommand("delete from tblMailingList where orderID ='" & session("orderID") & "'", dbConn)
      strSql.executeScalar
      dbConn.close   
      
      gridOuterHolder.visible = false
      listAccept.visible=false
      session("filePath") = filePath
      'remove any existing list items if they upload a different list
      prefix.items.clear()
      FirstName.items.clear()
      LastName.items.clear()
      title.items.clear()
      CompanyName.items.clear()
      Address.items.clear()
      Address2.items.clear()
      City.items.clear()
      State.items.clear()
      Zipcode.items.clear()      
      
      'Get all header row information
      Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
      cnExcel.Open()
      Dim lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
      Dim cmdExcel As New OleDbCommand("SELECT * FROM [" & lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
      Dim dr As OleDbDataReader
      dr = cmdExcel.ExecuteReader
      If dr.Read Then
      Try
         For x = 0 To dr.FieldCount-1
                Dim y as new ListItem            
            y.text = dr(x)
            y.value = x
            'Add the item to all the dropdowns
            Prefix.Items.Add(y)
            FirstName.Items.Add(y)
            LastName.Items.Add(y)
            Title.Items.Add(y)
            CompanyName.Items.Add(y)
            Address.Items.Add(y)
            Address2.Items.Add(y)
            City.Items.Add(y)
            State.Items.Add(y)
            Zipcode.Items.Add(y)            
            Next
         
         'Insert blank first selection option
         Prefix.Items.Insert(0,new ListItem("",-1,true))
         FirstName.Items.Insert(0,new ListItem("",-1,true))
         LastName.Items.Insert(0,new ListItem("",-1,true))
         Title.Items.Insert(0,new ListItem("",-1,true))
         CompanyName.Items.Insert(0,new ListItem("",-1,true))
         Address.Items.Insert(0,new ListItem("",-1,true))
         Address2.Items.Insert(0,new ListItem("",-1,true))
         City.Items.Insert(0,new ListItem("",-1,true))
         State.Items.Insert(0,new ListItem("",-1,true))
         Zipcode.Items.Insert(0,new ListItem("",-1,true))   
            
         'default dropdown selection to format specificed by massey
         prefix.selectedIndex = 1
         firstname.selectedIndex=2
         lastname.selectedIndex=3
         title.selectedIndex=4
         companyName.selectedIndex=5
         address.selectedIndex=6
         address2.selectedIndex=7
         city.selectedIndex=8
         state.selectedIndex=9
         zipcode.selectedIndex=10
         
      Catch e as exception
         errorText.text="There was a problem with the file you uploaded. Please contact customer support."
      finally
         dr.Close()
         cnExcel.Close()   
      end try   
      end if               
   end function