How do I "clean up" an Excel 2003 file and then insert it into a database?

[axman1000]

I will get an Excel 2003 file everyday via email. This file has 5 columns. The first for columns have static headers and text in them. The 5th column is the date of the report and below that is a list of values in the same column, followed by an empty row, and then the sum of all the values in the column.

I would like to clean it up in the following manner:

Directly import the first 4 columns, without changing it. The date, which is the header of the 5th column, should be extracted and should form the entire 5th column in my table in the database. The values in the 5th column should form the 6th column in the database and the sum of the values should be ignored. Also, the number of rows is variable and so how to make the code relative? This is a file to which I will not have direct access to, nor can the format be changed.

How can I achieve this using VB.Net? I read up a few articles and they all said that using VB is better than C# for this purpose. I'm a complete noob to Excel automation and really need some help. Thanks!

[Animal Mother]

Rather than going the root of "cleaning" the sheet just get the info that is needed, which is possible
without cleaning the sheet if you use some crafty coding.

Since you know where all the info is on the sheet create the variables you'll need (preferable all strings)
allocate their values from the excel sheet (as follows)

strVariable = (xlWorkSheet.Cells(1, 2)).value

The above code will give your variable the value of whatever is in row 1, column 2.

To get the used rows and columns in the excel sheet do the following.

Dim range As Excel.Range = xlWorkSheet.UsedRange
Dim intRow As Integer = range.Rows.Count
Dim intCol As Integer = range.Columns.Count

Now you have the amount of rows and columns that has been used so you can do a for loop for the
amount of records in your excel sheet.

If you need more specific info let me know.

[Animal Mother]

Oops sorry I almost forgot you want to know how to write your variables to the database.

First create your Database insert command.

Dim dbInsert As New OleDb.OleDbCommand

The create parameters for your insert function

dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var1"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var2"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var3"

Then give the parameters their values that will be written to the database.

dbInsert.Parameters.Item("Var1").Value = strVariable1
dbInsert.Parameters.Item("Var2").Value = strVariable2
dbInsert.Parameters.Item("Var3").Value = strVariable3

The values that you allocate to the parameters above are the values that you got form the excel sheet.

[axman1000]

Thanks for your reply! :) How do I, however, track and ignore the cell that contains the sum of all the values in the 5th row? The one which has a row's gap between the values above it and itself?

Oops sorry I almost forgot you want to know how to write your variables to the database.

First create your Database insert command.

Dim dbInsert As New OleDb.OleDbCommand

The create parameters for your insert function

dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var1"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var2"
dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Var3"

Then give the parameters their values that will be written to the database.

dbInsert.Parameters.Item("Var1").Value = strVariable1
dbInsert.Parameters.Item("Var2").Value = strVariable2
dbInsert.Parameters.Item("Var3").Value = strVariable3

The values that you allocate to the parameters above are the values that you got form the excel sheet.

[Animal Mother]

It's kinda hard to imagine this sheet but what I understand is that the records stop then there is a empty row and following that empty row is the sum of all the values in that column.
So if I'm understanding this correctly I'll have to advise you to do a for loop for the amount of records in the sheet less 1. This means the for loop will exit before it hits the sum row.
Example

For i = 1 To intRow -1
   strVariable1 = (xlWorkSheet.Cells(i, 1)).value
   strVariable2 = (xlWorkSheet.Cells(i, 2)).value
   strVariable3 = (xlWorkSheet.Cells(i, 3)).value
Next

This means that you will have to handle the writing to your database whithin this loop aswell.

Hope this Helps

[axman1000]

Thanks for the perfect answer! :)

It's kinda hard to imagine this sheet but what I understand is that the records stop then there is a empty row and following that empty row is the sum of all the values in that column.
So if I'm understanding this correctly I'll have to advise you to do a for loop for the amount of records in the sheet less 1. This means the for loop will exit before it hits the sum row.
Example

For i = 1 To intRow -1
   strVariable1 = (xlWorkSheet.Cells(i, 1)).value
   strVariable2 = (xlWorkSheet.Cells(i, 2)).value
   strVariable3 = (xlWorkSheet.Cells(i, 3)).value
Next

This means that you will have to handle the writing to your database whithin this loop aswell.

Hope this Helps