?
Solved

Upload Excel files from folder to SQL

Posted on 2005-03-16
8
Medium Priority
?
288 Views
Last Modified: 2010-04-23
Hi everyone:

I have a folder in a directory that is filled with 28-30 Excel documents each month. All Excel documents have the same schema. The names of Excel documents are different. My assignment is to open a folder grab one by one Excel documents, strip headers and load data into SQL table.

How to automate the process to get Excel documents from the folder and load data into SQL? Your help would be greatly appreciated.

RGDS
Sam
0
Comment
Question by:BOOBIC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13562469
Hi Sam,
Well it can be automated of course. Important thing here will be reading the Excel file properly. That will depend upon the format of the file. As you say "Strip Some headers" what are these headers, are they the columns names ?? Are they always on the First Row (or any particular Row)??

0
 

Author Comment

by:BOOBIC
ID: 13566480
Hi Arif:

The first row for all Excel files is a column name. No other headers after the first row. Stating from the second row it's allways data. The format for all columns are General.

RGDS
Sam
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13571617
Right
I'll just try out a sample code and post...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Accepted Solution

by:
arif_eqbal earned 2000 total points
ID: 13571821
Here's what I got.
I assume there's a directory where Excel Files keep coming. I have to process all files get it's data into a table.

There are two ways to do it...
One:

    Const ExcelPath As String = "D:\ExcelFiles"
    Private Sub ReadExcel()
        Dim DT As New DataTable
        Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=##Your dataBase Path ##;Persist Security Info=False"
        Dim DA As New OleDbDataAdapter("Select * From Master", CnStr) 'A dummy Table I made
        Dim CB As New OleDbCommandBuilder(DA)
        Try
            DA.Fill(DT)

            Dim FlName As String
           'read all the file in this directory
            For Each FlName In Directory.GetFiles(ExcelPath, "*.xls")
                If ProcessFile(FlName, DT) Then
                    'After the file is Read move the file to say Archive Folder, so that it is not processed again
                    File.Move(FlName, FlName.Replace(ExcelPath, ExcelPath & "\Archive"))
                End If
            Next
            DA.InsertCommand = CB.GetInsertCommand
            DA.UpdateCommand = CB.GetUpdateCommand
            DA.DeleteCommand = CB.GetDeleteCommand
            DA.Update(DT)
            MsgBox("Done")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            DA.Dispose()
        End Try
    End Sub


    Private Function ProcessFile(ByVal FName As String, ByVal DT As DataTable) As Boolean
        Dim XL As New Excel.Application
        Dim Dr As DataRow
        Dim WB As Excel.Workbook = XL.Workbooks.Open(FName)
        Dim CurrSheet As Excel.Worksheet = WB.Worksheets(1)
        Dim TotalRows As Integer = CurrSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
        If TotalRows > 2 Then
            'Start From Row 2 as first row is header
            For i As Integer = 2 To TotalRows
                Dr = DT.NewRow
                'I used only Two Cols, there can be as many
                Dr("ID") = CurrSheet.Range("A" & CStr(i)).Value()
                Dr("Name") = CurrSheet.Range("B" & CStr(i)).Value()
                DT.Rows.Add(Dr)
            Next
        End If
        WB.Close()
        WB = Nothing
        XL.Quit()
        XL = Nothing
        Return True
    End Function

'This is a somewhat manual way of doing it as we are manually visiting every cell, it gives us the benfit that in case there's some error in the cell's data we can correct it then and there.

Second: This way is to directly read using Excel Provider

    Private Sub CmdReadEx_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdReadEx.Click
        Dim FlName As String
        For Each FlName In Directory.GetFiles(ExcelPath, "*.xls")
            If FillDataTable(FlName) Then
                File.Move(FlName, FlName.Replace(ExcelPath, ExcelPath & "\Archive"))
            End If
        Next
    End Sub


   Private Function FillDataTable(ByVal FlName As String) As Boolean
        Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & FlName & ";" & _
           "Extended Properties=""Excel 8.0;"""
        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter("Select * from [Sheet1$]", CnStr)
       'It is Smart Enough to Ignore the Column header
        Try
            DA.Fill(DT)
            MsgBox(DT.Rows(0).Item(0))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True

    End Function
0
 

Author Comment

by:BOOBIC
ID: 13592596
Hi Arif:

Your Second Code works like a charm. I'm trying to modify your First Code and post the code for your revue.

RGDS
Sam
0
 

Author Comment

by:BOOBIC
ID: 13653939
Hi Arif:

Sorry for being not cooperative. I been working  whole week at client's site. I'll try today to wrap it up in order to come with the code.

RGDS
Sam
0
 

Author Comment

by:BOOBIC
ID: 13665422
Hi Arif:

This is my messy code. I'm trying to read data from Excel file and transfer data into SQL table (it works) and then I want after finishing to read Excel file transfer that file to another folder (this is where I have a problem). I feel like my Excel file is in a memory and I should to clean the memory first in order to move Excel file to another folder. You can find error message below.

***********************************************
The process cannot access the file "D:\My Projects\AA.xls" because it is being used by another process.
**********************************************

   Private Sub ReadExcel()
        Dim DT As New DataTable
        Dim FlName As String

        For Each FlName In Directory.GetFiles(ExcelPath, "*.xls")
            If FillDataTable(FlName) Then
   
                Dim conSQL As SqlClient.SqlConnection
                Dim cnSQL As SqlConnection
                Dim myOLEDBcn As OleDbConnection
                Dim strSQL As String
                Dim cmSQL As SqlCommand

myOLEDBcn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & FlName & ";"Extended Properties=""Excel 8.0;""")

                myOLEDBcn.Open()

                Dim myOLEDBCommand As New OleDbCommand

                myOLEDBCommand.Connection = myOLEDBcn
                Dim myOLEDBAdapter As New OleDbDataAdapter(myOLEDBCommand)

                myOLEDBAdapter.SelectCommand = myOLEDBCommand

                Dim ExcelSheet As DataTable = myOLEDBcn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
 Dim SpreadSheetName As String = "[" + ExcelSheet.Rows(0)("TABLE_NAME").ToString() + "]"

                With myOLEDBCommand
                    .CommandTimeout = 30
                    .CommandText = "Select * From " & SpreadSheetName & ""
                End With

                myOLEDBAdapter.SelectCommand = myOLEDBCommand

                Dim myOLEDBDataSet As DataSet = New DataSet

                myOLEDBCommand.ExecuteNonQuery()
                myOLEDBAdapter.Fill(myOLEDBDataSet, SpreadSheetName)

                Dim strOne As String

                strOne = myOLEDBDataSet.Tables(0).Rows(0).Item(0).ToString()

                Dim i As Int32

                For i = 0 To myOLEDBDataSet.Tables(0).Rows.Count - 1
                    strOne = myOLEDBDataSet.Tables(0).Rows(i).Item(0).ToString()

Dim oSQLCmd As SqlCommand = New SqlCommand
Dim connection As New SqlConnection("workstation id=AAAA;packet size=4096;user id=sa;password=""SSSS"";data source=REPORT;persist security info=True;initial catalog=Financial")
                    connection.Open()

                    oSQLCmd.Connection = connection

                    Dim sSQL As String

               sSQL = "INSERT INTO InsertExcel (CompanyID) VALUES ('" & strOne & "')"

                    oSQLCmd.CommandText = sSQL
                    oSQLCmd.ExecuteNonQuery()
                    connection.Close()

                Next

                File.Move(FlName, FlName.Replace(ExcelPath, ExcelPath & "\Archive"))

<<<< THIS IS WHERE I'M GETTING AN ERROR  >>>>

            End If
        Next
    End Sub

    Private Function ProcessFile(ByVal FlName As String, ByVal DT As DataTable) As Boolean
        Dim XL As New Excel.Application
        Dim Dr As DataRow
        Dim WB As Excel.Workbook = XL.Workbooks.Open(FlName)
        Dim CurrSheet As Excel.Worksheet = WB.Worksheets(1)
        Dim TotalRows As Integer = CurrSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
        If TotalRows > 2 Then
            'Start From Row 2 as first row is header
            For i As Integer = 2 To TotalRows
                Dr = DT.NewRow
                'I used only Two Cols, there can be as many
                Dr("CompanyID") = CurrSheet.Range("A" & CStr(i)).Value()
                Dr("HMOCompCode") = CurrSheet.Range("B" & CStr(i)).Value()

                Dr("HPCode") = CurrSheet.Range("C" & CStr(i)).Value()
                Dr("HPName") = CurrSheet.Range("D" & CStr(i)).Value()

                Dr("ASSOCIATE") = CurrSheet.Range("E" & CStr(i)).Value()
                Dr("PCPID") = CurrSheet.Range("F" & CStr(i)).Value()
                Dr("Provider Name") = CurrSheet.Range("G" & CStr(i)).Value()

                Dr("Current MMs") = CurrSheet.Range("H" & CStr(i)).Value()
                Dr("Retro MMs") = CurrSheet.Range("I" & CStr(i)).Value()

                Dr("Total MMs") = CurrSheet.Range("J" & CStr(i)).Value()
                Dr("Total Cap") = CurrSheet.Range("K" & CStr(i)).Value()

                DT.Rows.Add(Dr)
            Next
        End If
        WB.Close()
        WB = Nothing
        XL.Quit()
        XL = Nothing
        Return True
    End Function

    Private Function FillDataTable(ByVal FlName As String) As Boolean

        Dim myOLEDBcn As OleDbConnection

        myOLEDBcn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & FlName & ";" & _
   "Extended Properties=""Excel 8.0;""")

        myOLEDBcn.Open()

        Dim DT As New DataTable
        Dim ExcelSheet As DataTable = myOLEDBcn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim SpreadSheetName As String = "[" + ExcelSheet.Rows(0)("TABLE_NAME").ToString() + "]"

   Dim DA As New OleDbDataAdapter("Select * from " & SpreadSheetName & "", myOLEDBcn)
        'It is Smart Enough to Ignore the Column header
        Try
            DA.Fill(DT)
            'MsgBox(DT.Rows(0).Item(0))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ReadExcel()

    End Sub

RGDS
Sam
0
 

Author Comment

by:BOOBIC
ID: 13674174
Hi Arif:

Don't laugh. As usual it was just stupid mistake. I forgot to close my conection to Excel.

            myOLEDBAdapter.Dispose()
            myOLEDBcn.Close()
            myOLEDBcn.Dispose()

Everything works nice. Your points are refunded.
Thank you for your help.

RGDS
Sam
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question