Automate Your Import Process in Access


Import_Files_IHow about 1,2 million rows of data?
Well there you are, Excel specialist and forced to import a text file containing well over 1,2 million rows of data. You do not have the Power BI tools installed nor do you know how to work with those tools. YouTube? right have a look how it is done and repeat the steps. You will repeat the steps but you have no clue as to what you are doing. Why not import the data into Microsoft Access? You can apply the same functionality as you do in Excel when working with large amounts of data. Two million records? Just dunk it into Access and you are good to go. In this blog post I will describe the process of importing well over 1 million rows of data into Microsoft Access. You can see this as a monthly task so to be more specific we are going to save the steps so we can repeat them next month and the month after that.

Importing a flat file with over 1,2 million rows of data in Microsoft Access
So the goal is to import the flat file into an Access table and automate this process so we can repeat the steps every month. The first step would be to create a new blank database (no web stuff just a regular desktop database). When done you will be presented with a new table ready to be used. Close this table and don’t save it. Now on the ribbon click the External Data tab and from the Import and Link group choose Text File:

Import_Files_II

This will present you with an import wizard dialog.

Import_Files_III

We are going to follow the steps from the wizard but at some point we are going to take a side step. Let’s first determine what we are confronted with:

  • We need to provide the location of the .txt file. That’s the easy part. Just browse to that specific directory using the Browse button
  • Next you have three options to choose from. We are on the hunt for importing data into our database so we choose option 1. examine the other options so you know what is available out there.

When you have confirmed the .txt file you want to import click the OK button. You will be presented with the next step from the wizard.

Import_Files_IV

This is where we need to take some time to do some checking. Access tells you that it seems that your data is in a ‘Delimited’ format. It assumed right this time so we click Next. Here is the next step using the wizard.

Import_Files_V

Another dialog we have to check…
First we determine the delimiter used in our text file. In this case the ‘Tab’ delimiter is correct. Most of the users prefer a semicolon (;) which is also available. My data has a first row that contains header information so we check the check box “First Row Contains Field Names”. Now instead of clicking the Next button I am going to click the Advanced… button. Why? because I want to save the import steps I am making for later use. Here is the dialog which will pop up when you have clicked “Advanced…”

Import_Files_VI

In this dialog we determine the data types for the various fields from the text files. Let’s take a look at the different choices we have to make:

  • Language: defaulted to your installation (mine is dutch)
  • Decimal symbol: important for Europeans (US has a dot here)
  • File format: as determined in the previous step
  • Field information: because we are creating a File Import Specification which we would like to save we have to rename the field names to the appropriate names.
    TIP: do not press Enter after you have renamed Field1 to something else. Entering will close the dialog!!
  • I don’t want to import the field LineTotal so I will mark that field as ‘skip’. As you can see I have renamed all field names accordingly

Import_Files_VII

Saving the Import Specification
When you have renamed all fields you click the “Save As…” button. With this button you save all the parameters you have set during the wizard. So next time Access will know the field names and data types you have defined.

Import_Files_VIII

Save you import specification by giving it a name. Now follow the regular steps to finish the wizard (we don’t need to take any further action, we don’t need a primary key so you can set those to no primary key. Choose a table name at the and, and your table will be created containing the data from the .txt file

Import_Files_XIII

Save the import steps?
At the end you will be asked if you want to save these import steps. Confirm and click OK after you have given it an appropriate name. Why do we need to save the steps twice? I am going to explain right now..

Import_Files_IX

Two steps to automate
Developers will not use the file import steps you have saved as the image above shows. They will use the File Import Specification file we have created earlier. Strange thing is that this file can not be found in the combo box when you creat an automation macro. Let me show you what I mean:

Automate this process using the autoexec macro
Every Access developer knows that you can create an automation process by naming your macro “autoexec”. So we are going to create a macro which will fire the import steps as described above using the saved import steps. From the ribbon choose create macro. You will be presented with the following screen:

Import_Files_XIV

Activate additional options
By default you will not see all available options. You have to click the “Show All Actions” button. This will reveal more available options with an exclamation mark in front of it. Can you figure out what that means? Anyway we choose the option ImportExportData and on the center screen we are presented with a combo box to choose from. Remember what I said about the Import Specification File we save during the wizard? You won’t find that file in the combo box here. What you see here is the filename we presented right at the end of the wizard. Now save the macro and name it “autoexec”. Delete the table you created earlier and close and reopen the database. Your table will be created automatically for you.

Automate from code (VBA)
This step takes a little more work but gives just that little more flexibility. First create a module (Ribbon – Create module). Now type the following bit of code as you see in the image:

Import_Files_XV

 

Import_Files_XVI

Explanation
Although it doesn’t return anything the macro editor expects a function here so we create a function. The first line of code tells Access to delete the table. The second line of code tells Access it should import the text file from that specific location using the Import Specification we have saved during the wizard steps. By not deleting the table this piece of code will do the clean up first for you. Save the module (give it a name you like)

Now create the macro
Instead of using ImportExport commands choose the Macro commands option and look for RunCode. In the text box in the center of the screen type in your function name and you are good to go. Name your macro (autoexec would be great, but be aware that you already have a autoexec macro from the previous steps. Rename that for now to something different).

Import_Files_XVII

Close and reopen the database and you data will be imported accordingly.

Concluding: If Excel hasn’t got the room to fit your needs Microsoft Access might be able to offer you just that little more. If you have imported the data into the table you will see that Access also offers all filter capabilities you have in Excel. Why not use both?

 

 

 

Advertisements

4 thoughts on “Automate Your Import Process in Access

  1. Srihari

    Hi

    I want to load multiple text files save in one folder. File names examples are below

    ACT_AP_Apr.txt
    ACT_AP_May.txt
    ACT_AP_Jun.txt
    ACT_AP_Jul.txt

    Can you please advise to automate the loading of all files

    Reply
  2. Keith

    Some time ago I was doing a course which was using Word, Excel and Access. The course was
    ICAIT30199 Certificate III in IT – Software Applications (here in Australia) I was in the Access section and basically hit a wall. The task was to create a one click macro/ action to import 99 records from a csv/ txt file. Then it had to find records where tblheading_Instrument was Trumpet and change it to Wind Section, and where tblheading_Age was Parent or Adult change it to Supervisor… I think there was another one or two requirements. I was able to get the action/ macro to work on all records individually, but when it came to all 99 it kept dropping 5 of the records and I was never able to determine where I was going wrong – or – what I was not seeing. The tutors were able to see it but as it was an assessment, they could not help, but they did say it was easy to overlook, I was advised to look at SQL as we were only meant to use vba. I would love to try and find out what i didn’t see, but my files were on 3.5 inch floppies and I no longer have them, nor can I find the source material/ files as it was 2004 – 2005. The course has been changed and concentrates more on Digital Media and no longer looks at these functions in Access. While it does touch on some Advanced Access it does not look at that far advanced functions. Yes I am holding on to it for a while, but I hate being beaten, and I am determined to solve the problem, Unfortunately, I don’t think these tasks are included in the current Courses. If you have something that I can work with I’d appreciate it. The tasks were originally written for office 97 and office 2000. I just hate being beaten and “demand” a re-match if you get my meaning. I have learnt a reasonable amount of SQL having done Web development linking Webs to DBs with PHP and MySql and writing queries. Apologies for the lengthy explanation but I figure the more the detail the better result.

    Reply
  3. Joe

    Hi, thank you so much for this how-to. It almost worked perfectly from following your steps, however I did notice conflicting information in your “Automate this process using the autoexec macro” section. In the paragraph and on the image you instruct to use the ImportExportData action. I tried this a couple times and the combination box was different from yours. Nonetheless, I filled out the fields Access was requesting but I kept getting a failure on import. When I looked back carefully I noticed that the actual action in your image used was the RunSavedImportExport action. I selected that choice in the Action Catalog and it worked flawlessly on the first try. I thought I would leave this comment to help anyone else that comes across this issue. Thank you so much for taking the time to post this how-to! It is much appreciated!

    Reply
  4. Kevin Lee

    Hiya,

    Great post. Can’t get any more straight forward than this article. Thanks!
    I’m trying to copy ALL data from a particular worksheet in workbook “A” and simply paste that exact same worksheet’s data into a particular worksheet in workbook “B”.
    I’ve set handle 1 as the “destination” workbook, and handle 2 as the “source” workbook. In the MS Excel VBO “Copy and Paste Worksheet Range” object, I’ve added the source workbook and source worksheet names, as well as the source range such as “A1:B5” and did the same thing for destination. I’m confused about what to put in the “Handle” row. In the inputs section of the VBO, which handle is the input? The source or the destination or neither?
    When I try to run it I get “Failed to copy worksheet: Invalid index. (Exception from HRESULT : 0x8002000B (DISP _E_BADINDEX))
    All I want to do is simply copy and paste one worksheet to another. I don’t need to manipulate the data so I don’t want to bring it into Blue Prism if possible as a collection because it’s too slow, if possible.
    What am I doing incorrectly? Prior to the copy/paste action, I’ve created instances for both workbooks, opened the workbooks, and show them on blue prism certification dumps vegas.
    MS Excel VBO – Copy and Paste Worksheet Range?

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
    Please keep providing such valuable information.

    Best Regards,
    Kevin

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s