Automating a Fixed Width Import from Text and CSV file

[rweakley]

Hello,
I'm using Access 2010
I've created a couple of databases at my new job for maintaining files that are brought in on a daily basis. Most of my work so far has just been with queries to update and format the data as it comes in.

We receive multiple text files and csv files each day. The text files go into one database and csv files in another.

Both files are around 2000 characters long, and are imported using a saved fixed width specification. It hasn't been too much of a hassle to import these manually as they come in, but things have picked up recently and it is becoming time consuming to do each of these files manually.

I'm looking for a way to "automate" this a bit as the import steps are the same each time. I haven't been able to use the "Saved Imports" feature as the files come with different names so I would like a way to still have the "Browse" feature open so that the file can be selected manually.

The Import Steps are:

External Data > From Text > Import into new table > Advanced > Specs > Open > Select Primary Key > Name Table(this is always the same).

I'm not familar with <acronym title="vBulletin">VB</acronym>, but I'm always willing to learn and try and get my hands dirty.

Once this part and built, I will be having other people begin Importing the daily files into this database, so I'd like the end product to be as simple as possible where all thats needed is to select the file being imported and then all the other steps above are done behind the scenes.

Thanks for any help!

 

[Joe4]

The good news is, you don't need to know VBA in order to do this. Create a Macro.
All you have to do is Create a new macro and add the "TransferText" Action (this is the command to import text files). Simply fill out the arguments for that action (they tell you what each one is and you can even hit F1 to get more detailed help).
Then, in order to run it, all you need to do is click the Macro each time.

 

[rweakley]

The good news is, you don't need to know VBA in order to do this. Create a Macro.
All you have to do is Create a new macro and add the "TransferText" Action (this is the command to import text files). Simply fill out the arguments for that action (they tell you what each one is and you can even hit F1 to get more detailed help).
Then, in order to run it, all you need to do is click the Macro each time.

Thanks, Joe. I do have a couple of more questions though.

I'm using Access 2010, it looks liek TransferText was changed to ImportExportText. I'm running into a couple of issues though. Is there a way to have the macro pop open a window to browse and select the file needing to be imported, rather than pre-specifying a file name? Also, is there a way to select a Primary Key as part of the macro, I'm not seeing that option in the arguments.

 

[Joe4]

I have not seen Access 2010, but I imagine the command should work roughly the same.
Based on what you want to do, it looks like you are probably going to have to get your hands dirty and dive into VBA.
Here are some tips.

1. You cannot browse to files with Macros, you can only import a hard-coded file name. If you want the added flexibility of browse to the file name, you will need VBA. Access will do a lot of this for you. First create a Macro that imports some example file. Then, highlight the Macro and select "Convert Macros to Visual Basic" from the Macros ribbon. This will give you a lot of the VBA code you require, and give you a good start. If you Google "Microsoft Access browse file", you should be able to find VBA code snippets you can insert into your VBA code to do what you want (this is a good way to find lots of VBA code on the internet).

2. Are you recreating your table each and every time? I would recommend creating a table shell, where you can pre-define your Primary Key and all field formats and properties. Then instead of importing into a new table every time, import into this pre-made shell. Then you do not need to worry about things like setting the Primary Key. Be sure to manually import a file and save your Import Specification to use in your Macro/VBA code.

To wipe the table clean every time before using it on the next run, a simply VBA code will do that, i.e.

DoCmd.RunSQL "Delete [TableName].* From [TableName]";

 

[rweakley]

They way the database is working right now is that I'm importing the data on a file by file basis into a table named "Import" recreating the table each time. A make table query is then ran on the Import table to format it into understandable information. (The data comes across in a strange way, some letters have to be converted to numbers and calculations performed to figure out other dates and fields). After the data is formatted it is then appended to a Master list of records. At first I had a table where the imports were appended to a list, and then formatted and appended to the formatted Master list. Due to the amount of records the list was getting the formatting query started to take too long. I didn't think about creating anything to wipe the table clean after import.

I'll give this a shot and see if I can get it to work. Thanks again for the help, Joe!

 

[rweakley]

I got the browse file code to work in VBA, but I'm running into an issue. If the file is saved locally(in my downloads or on my desktop) the import works fine. However, when the file is on a network drive(which is where it will be during actual use) I am getting the following error:
"The Microsft Access database engine could not find the object 'filename'. Make sure the object exists and that you spell its name and the path name correctly. If 'filename' is not a local object, check your network connection or contact the server administrator."

 

[Joe4]

If you insert a line like this before the line returning the error:

MsgBox filename

What does it return?
Does the file path it is returning exist?

 

[rweakley]

If you insert a line like this before the line returning the error:

MsgBox filename

What does it return?
Does the file path it is returning exist?

Function Import_Export_Text()
On Error GoTo Import_Export_Text_Err
Dim fileName As Variant
Set fileName = Application.FileDialog(3)
If fileName.Show - 1 Then
strFileName = fileName.SelectedItems(1)

        DoCmd.TransferText acImportFixed, "Specification1", "T_Import", strFileName, False
   End If
Import_Export_Text_Exit:
    Exit Function
Import_Export_Text_Err:
    MsgBox Error$
    Resume Import_Export_Text_Exit
End Function

Is the current code I'm using. When it is ran it is opening the Browse Window to select a file, selecting the file and opening(if it is on a network drive) then gives the error I mentioned earlier. The actual filename in this case is testfile so the error states exactly:

"The Microsft Access database engine could not find the object 'testfile.csv'. Make sure the object exists and that you spell its name and the path name correctly. If 'testfile.csv' is not a local object, check your network connection or contact the server administrator."

Adding MsgBox strFileName does give the correct path of:
L:\Folder\User\SubFolder\2013\11 November\testfile.csv
Clicking OK on that message box then returns the error above

 

[Joe4]

Is there a space between "11" and "November"?
Spaces in folder and/or file names can sometimes wreak havoc with code. I recommend using underscores in lieu of spaces in naming folders and files.

 

[rweakley]

Is there a space between "11" and "November"?
Spaces in folder and/or file names can sometimes wreak havoc with code. I recommend using underscores in lieu of spaces in naming folders and files.

Removing the spaces didn't seem to fix the issue. Still resulting in the same error as before.