How to read multi Excel sheets files with the same formate in one folder and produce MS Access table

How to read multi Excel sheets files with the same format in one folder and produce MS Access table. One example of the excel sheets files are attached and the other files will be similar but with different data. the well1 and rig1 should be added as data for two columns in the MS access table (Well and Rig). also the MS ACCESS table that should be created is also attached for clarifications.


Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
Is this a one-time thing, or something that will need to be constantly adding in new data?

If it is a one-time thing, then you can export the data from each Excel worksheet into a CSV file, then import the data directly into Access.  It looks like the columns are pretty much the same all the way across, so this should be easy enough.

If it is an ongoing process, then you'll want a programmatic way to deal with it.  Having done similar conversions before, I would recommend a simple VBScript process that reads the Excel spreadsheets directly, extracts the values into a comma-delimited file, and then inserts the rows into the Access database.  The process is the same, but it just takes some effort to codify it.  

Extracting the data from VBScript is pretty easy, and there are code samples on the 'Net.  Essentially, it looks like this:
' Open the Excel Spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputFile)
Set objWorkSheet = objWorkbook.Worksheets(2)
' Select the starting row...
intRow = StartingRow 
' Do the work...
Do Until objWorkSheet.Cells(intRow,1).Value = ""
    WriteToFile WellNumber
    WriteToFile RigNumber
    WriteToFile objWorkSheet.Cells(intRow, 2).Value				' DateTime
    WriteToFile objWorkSheet.Cells(intRow, 3).Value				' Remarks
    'Repeat above for each column in the spreadsheet
    intRow = intRow + 1

Open in new window

Obviously, you'll need to add a file selector that can pick up the file names from the folder and extract out the well number and rig number, but this is an exercise left for the reader.

Once you have the reader portion done, the writer portion looks very similar.  Open an ODBC data connection to the Access database, read from the CSV, and start inserting rows.  Note that with an interim CSV file, you might run into problems with commas in the Remarks field, so if this becomes an issue, then could also simplify things by using a single script to read ALL of the fields (you'll need a variable for each column, of course) and directly build the INSERT statements for the ODBC target database. Again, sample VBScript code for ODBC inserts abound on the 'Net, so finding a starting point should be easy enough.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TransferSpreadsheet imports a worksheet with a single statement.  The best method is to do the import on one file manually and create an import spec in the process.  This lets you define the data types and give names to columns if the first row doesn't already contain column names.  On the next to last page of the dialog, press the advanced button and save the spec.  Give it a name you will remember.  Then reference the import spec in the TransferSpreadsheet method.

You will also need to use the File System Object to loop through the files in a specified directory.  When you find one that you want to process, use the TransferSpreadsheet to import it.
Here's the code to choose a directory:
Public Function fChooseDirectory()

    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    Dim fd As Object
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

Open in new window

I've attached a sample database that reads word files and opens them to extract their bookmarks.   The concept of what this app does is the same as what you need to do.  The difference is that you will use the TransferSpreadsheet method to import the data rather than opening a doc to look for bookmarks.
Mohammed DallagPetroleum ConsultantAuthor Commented:
The shared folder of the excel sheets will be updated from time to time with new excel sheets with the same format and different data.
I need this script to run at midnight by the windows schedule task to update and append the data to MS Access table.


Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Bill BachPresident and Btrieve GuruCommented:
Merging the basic functions should do it for you:
- Write a script that scans the folder, reading all files in it
- For each file, read the data and insert all data records into the Access database
- Move the data file to a "Processed" folder so that it doesn't get done a second time.
- Loop until all files are processed.

Are you looking for help in getting started, or someone to actually write the entire script for you?  Many experts (like myself) are happy to help with quick issues like this and give you pointers to get started, but providing complete integration solutions is something that we also get paid for.

If you are looking to simplify this process and also perform other integration tasks, you may wish to consider a full-blown ETL tool.  This class of tools (including Informatica, Actian's DataIntegrator, Pentaho, and Jaspersoft, among others) allow you to easily iterate over file sets, extract data, transform it as needed, and then load the data into a target database -- all from a GUI interface without having to write any real scripts.  These tools are definitely recommended, as they will make maintenance of the integration task substantially easier, too.
One step at a time.  Did you understand the example I posted?  Can you translate it to working with Excel?
Mohammed DallagPetroleum ConsultantAuthor Commented:
I am doing that and I will let you know. Thank you for your help.


Mohammed DallagPetroleum ConsultantAuthor Commented:
I did it manually but I need some one to help me to write the script please


Bill BachPresident and Btrieve GuruCommented:
There are two completely different paths being offered here.  Before anyone can help with the script, we need to know which path you are choosing.  If you have any part of it written already, please post it -- even if it is only pseudocode.  Otherwise, there are simply too many variables to completely write it for you without going back & forth another 10 times.
Bill, Access can import spreadsheets directly.  There is no need to export to .csv first.

Dallag,  Did you review the example I posted.  I realize that it is finding Word documents rather than Excel documents but that is really a minor change.  Please give it a try and let us know how you do.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.