How 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:
This will present you with an import wizard dialog.
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.
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.
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…”
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
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.
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
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..
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:
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:
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).
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?