How to import multiple text file data sets into their own table.

[Miguel Valenzue]

I collect traffic data from a machine that outputs text files with the data.
I want to import each text file as it's own table into an Access database and do it without having to run the import wizard for each one.

As part of this function, I'd like to strip the first 15 lines of data from the dataset, parse in the first 5 columns, and name the table after the information on line 3 which is the name of the street the data was taken from.

I know this is a tall order but I'm hoping for a nudge in the right direction.

I'm new at Access and know something like this is possible probably through some VBA script.
I've included the original dataset.

[TheSmileyCoder]

Ive put together some code for you.
To use it, call it with the full path of the txt file, for example:

importTxt("C:\Trafikdata\20100823_Kniveveien_detaljert.txt")

NOTE: You need to have a reference set to "Windows Script Host Object Model"
Visual Basic->Tools->References
You must also have created an importspec to use.

If you have any questions about the code, and what it does, please ask.

Public Sub importTxt(strPath As String)
'****************************************************************************
'Purpose: To open a txt file, get the road name, and import parts of the txt file to access
'
'Author: TheSmileyCoder @ Bytes.Com
'Copyright: You may copy and use this code, as long as this notice is left intact
'
'Input: strPath, the full path of the text file, for examle "C:\ImportFiles\example.txt"
'
'Output: None, a table is created or appended too if table allready exists
'****************************************************************************
    'Open the file
        Dim fs As filesystemobject
        Set fs = CreateObject("scripting.filesystemobject")
        Dim f As TextStream
        Set f = fs.OpenTextFile(strPath, ForReading)
 
    'Go to line 3 and get road name
        f.SkipLine
        f.SkipLine
        Dim strTableName As String
        'Replace any spaces with underscores
        strTableName = "tbl_" & Replace(f.ReadLine, " ", "_")
 
    'go to line 12, where the table start
        Dim i As Integer
        For i = 3 To 12
            f.SkipLine
        Next
 
 
    'Read the rest of the file into a string
        Dim strContent As String
        strContent = f.ReadAll
 
    'Save this to a textfile
        Dim strOutputPath As String
        strOutputPath = "C:\Documents and Settings\AEC.OSS\Desktop\Bytes DBs\ImportText\ImportFile.txt"
        Dim f2 As TextStream
        Set f2 = fs.CreateTextFile(strOutputPath, True, False)
        f2.Write strContent
        f2.Close
        Set f2 = Nothing
 
    'Now import the file, note a importspec must be specified
        DoCmd.TransferText acImportDelim, "tso_ImportSpec", strTableName, strOutputPath, True
 
    'cleanup
        fs.DeleteFile strOutputPath, True
        Set fs = Nothing
        Set f = Nothing
 
End Sub
 

[Miguel Valenzue]

You're in Denmark? I'm in Norway. How's your Norwegian?
Thanks for putting this together. Now I have to figure out exactly how to run it. Where to stick it into access etc. Thanks for your help.

[TheSmileyCoder]

Yes, I am in Denmark.
The code should probably go into a seperate module.
Your welcome.

[Miguel Valenzue]

Hi SmileyCoder,
I copied the text into a module and set the reference set by opening the reference window and checking the box next to "windows Script Host Object Model"
I then hit the play button to run the module and get a pop up window asking which macro to run and the import code I listed isn't in there so it doesn't run.

I created an import spec and named it Import1 and rewrote the line of code as such.
'Now import the file, note a importspec must be specified
DoCmd.TransferText acImportDelim, "Import1", strTableName, strOutputPath, True

[TheSmileyCoder]

I haven't used the F5/Play button much, but looking at it shortly it seems it only suggests subs that require no input argument.
The sub I wrote for you requires a input argument, the path to the file. In the VBA immediate window (ctrl-G opens the window if you are in the editor) you could simply write:

importTxt "C:\MyFolder\MyFile.txt"

to test the function.
For more advanced functionalty the code could be tied in with a form, and a filebrowser, but tahts for a seperate topic.

[Miguel Valenzue]

Well I feel like I'm a lot closer. I created a seperate macro and inserted
importTxt (""C:\Documents and Settings\mvadrm\My Documents\Traffic Data\20100823_Kniveveien_detaljert.txt")
and set the strOutputPath also.
Now I'm getting an error that states that it cannot find the import spec.
"Run-time error '3625'
The text files specification 'ImportSpec' does not exist. You cannot import, export, or link using the specification.

I have an import spec named ImportSpec saved so I'm not sure exactly what's happening. But I'm very happy with the progress thus far and I appreciate your help.

[TheSmileyCoder]

Can you post your Docmd.TransferText line as it looks now?

[Miguel Valenzue]

DoCmd.TransferText acImportDelim, "ImportSpec", strTableName, strOutputPath, True

The name of the Import spec is ImportSpec.

[TheSmileyCoder]

I dont know what to say. It worked for me.

From the error message you are getting it sounds like the importspec has not been saved properly. I would try to save it again, possibly under a new name.

[Miguel Valenzue]

Uh, as a novice I apologize. When you run the import wizard it asks you to save the import steps. So I saved it as ImportSpec and thought that I was running the importsteps. The import specs are located within the advance button (as you know) and so once I created the proper import spec and saved it in the wizard, I ran it and it worked. Completely awesome.
Now I have to write a quick macro for prompting me for the file and I'm off.

[Miguel Valenzue]

I want to expand upon this now (of course) and during the import, take that road name and dataset name and insert it into a seperate table. This will allow me to link all the data from the imported tables. Should I start a seperate thread for that?

[NeoPa]

Miguel:
Should I start a seperate thread for that?

The short answer to that is "Yes".

By the time you read this I will probably have moved the posts on the new question to a separate thread (Merge Tables into One), whose link will be posted in here for ease of access.