import excel data into access database using vb.net

[gayzlein]

hi all
i want a program that allow me to import excel data into access database using vb.net
knowing that the excel data contains duplicates and blanks.my table looks like this:

Reference quantity unit_cost turnover
once the data is imported, I want to perform calculations to calculate the cumulated turnover and their percentage. basing on these informations I should classify products into categories (A, B, C) according to their contribution to turnover (%)
By the end I have to show a userform that contains the products by adding the contribution of revenue and categories. the company changed the history of sales each year and must have the program that calculates that automatically
it's long, I'm sorry

I must do it using vb.net,
can you help me please

[Stuugie]

I don't understand why you must use VB.NET when Access imports nicely form Excel and likewise Excel imports nicely from Access. Have you tried using Access's import?

I've never understood why people would want to re-invent the wheel.

[ss125]

@Stuugie,
FYI:
There are several areas where we nweed to import or export inside an application. Even I had went accros the problem. Recent times, some clients of our company also asks application with features like these.

@gayzlein,
It can be done my friend. I have done that using c#. You can also do that in vb.net. Here are some link that can help you...

Link1
Click Here

Hope this helps you.

Next time while posting your problem, provide us with the codes you tried. So that we can help you in a easy way.

Have a happy coding...:-D

[gayzlein]

thank you ss125 for your understanding
this is my code, as i said, the excel data table contains duplicates and blanks, this code work when i have excel data without duplicates and blanks. and export excel data to listview(i want to export it to access database)
and the second problem, how to do the operations that I mentioned earlier.

Public Class Historique
    Private Structure ExcelRows
        Dim c1 As String
        Dim c2 As String
        Dim c3 As String
        Dim c4 As String
        Dim c5 As String
        Dim c6 As String
        Dim c7 As String
        Dim c8 As String
        Dim c9 As String
        Dim c10 As String
        Dim c11 As String
        Dim c12 As String
        Dim c13 As String
        Dim c14 As String
        Dim c15 As String
        Dim c16 As String

    End Structure
    Private Sub Historique_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.OpenFileDialog1.FileName = Nothing
        If Me.OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Me.TextBox1.Text = Me.OpenFileDialog1.FileName
        End If
        If getInfo() = True Then
            For Each xitems In excelRowList
                Dim lvitem As ListViewItem
                lvitem = Me.ListView1.Items.Add(xitems.c1)
                lvitem.SubItems.AddRange(New String() {xitems.c2, xitems.c3, xitems.c4, xitems.c5, xitems.c6, xitems.c7, xitems.c8, xitems.c9, xitems.c10, xitems.c11, xitems.c12, xitems.c13, xitems.c14, xitems.c15, xitems.c16})


            Next

        End If
    End Sub
    Private excelRowList As List(Of ExcelRows) = New List(Of ExcelRows)
    Private Function getInfo() As Boolean
        Dim completed As Boolean = False
        'open
        Dim myExcel As New Excel.Application
        myExcel.Workbooks.Open(Me.TextBox1.Text)

        'extract
        myExcel.Sheets("Feuil1").activate()
        myExcel.Range("a2").Activate()
        Dim thisRow As New ExcelRows
        Do
            If myExcel.ActiveCell.Value > Nothing Or myExcel.ActiveCell.Text > Nothing Then
                thisRow.c1 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                thisRow.c2 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                thisRow.c3 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                thisRow.c4 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c5 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c6 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c7 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c8 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c9 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c10 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c11 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c12 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c13 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c14 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c15 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)
                thisRow.c16 = myExcel.ActiveCell.Value
                myExcel.ActiveCell.Offset(0, 1).Activate()
                excelRowList.Add(thisRow)

                myExcel.ActiveCell.Offset(1, -16).Activate()

            Else
                completed = True
                Exit Do

            End If
        Loop




        'close
        myExcel.Workbooks.Close()
        myExcel = Nothing
        Return completed
    End Function

[saguni]

My friend @gayzlein,

Your code is working fine. I dont know where your problem is. But I can suggest you to follow some code reusability in your code.

Regarding this problem, try to debug the application line by line and see whats the data passed and check where the empty data is passed.

[gayzlein]

hi saguni
did you try this code?
i tried it and it work with small data table, but when i import excel data table without blanks and duplicates it doesn't run. the data are sales history, that mean that there are many rows an columns.
what do u mean please by following code reusability, can you explain more?
thank you for your suggestion

[ss125]

I suggest you to use loops and arrays that is regarding the code resuability. I think @saguni also said that only i hope.
I didnt tried that in vb.net. I tried the same in c#.

And, regarding the code you said

but when i import excel data table without blanks and duplicates it doesn't run

Can you please post that too. because I have to confirm on which logic you are checking the empty records.

Sorry for the Late response

[gayzlein]

hi ss125
thank you for help.
for this "when i import excel data table without blanks and duplicates it doesn't run"
i wanted to say "when i import data table with and duplicates it doesn't run". Sorry
can you show me please your code C#, this code handles the blanks and the duplicates?
for the excel file, sorry ss125, i can't post it, because the company don't allow me to share it, but i can describe it,
the file contains 16 columns each column contain so many lines,:
thes are the columns

Reference     unit-sold    Clients     Id_clients     units_cost      turnover ........

some References don't have unit_sold then there are no turnover.
i hope i gave you a global idea about my excel file.
Thank you again.

[ss125]

Well this is my excel upload file...

if (FileUpload1.HasFile)
                {
                    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                    string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                    string fileLocation = Server.MapPath("~/App_Data/" + fileName);
                    FileUpload1.SaveAs(fileLocation);
                    //Check whether file extension is xls or xslx
                    if (fileExtension == ".xls")
                    {
                        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    else if (fileExtension == ".xlsx")
                    {
                        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }

                    //Create OleDB Connection and OleDb Command
                    OleDbConnection con = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection = con;
                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                    DataTable dtExcelRecords = new DataTable();
                    con.Open();
                    DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                    cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                    dAdapter.SelectCommand = cmd;
                    dAdapter.Fill(dtExcelRecords);
                    con.Close();
                    GridView1.DataSource = dtExcelRecords;
                    GridView1 .DataBind();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("The error is"+ex);
            }

To be frank, I used validation at front end to write in the excel file. So there is no need for checking the empty values of data. But based on your problem you need to check the excel file itself(since it is unknown to me.)

So, I can suggest a way to populate the dataset manually.

The following code shows how to populate the record manually

DataTable workTable = new DataTable("Customers");

DataColumn workCol = workTable.Columns.Add("CustID", typeof(Int32));
workCol.AllowDBNull = false;
workCol.Unique = true;

workTable.Columns.Add("CustLName", typeof(String));
workTable.Columns.Add("CustFName", typeof(String));
workTable.Columns.Add("Purchases", typeof(Double));

after that you have to declare a loop like

while (csv.ReadNextRecord())
        {
            for (int i = 0; i < fieldCount; i++)
                worktable.add(string.Format("{0} = {1};",
                              headers[i], csv[i]));//to add columns

            //Code area


        }

inside the code area you have to declare another loop like the same to get the data and store all the data on a temporary variable. Check whether any of the temporary variable is empty using if condition.

if any of temp data is empty then omit the record. else save the data in the temporary variable to the columns of the dataset.

And finally populate the grid with the dataset.

Hope this helps you...

Have a happie coding...

[gayzlein]

hello ss125, your code work well, and that helped me a lot, thanks a lot
and so sorry for the very late response