How to Import a txt fixed with file with multiple data specifications it it.

[jnordfors]

I have a fixed with txt file that has multiple data specifications in it. I am using MS access 2013 to parse out the data.

Is there an easy way to apply the different specifications to the file without having to build separate queries in Access to parse it out?

[twinnyfo]

jnordfors,

Welcome to Bytes!

I "think" i know what you mean by "multiple data specifications" but it would be much more helpful if you were more descriptive.

If you mean that you have a fixed width text file in which some lines are laid out one way and other lines are laid out another, then I would abandon the idea of using queries to parse out this file.

I would use VBA and open the file as a text stream, input each line of text, evaluate each line and then import it accordingly. We manage hundreds of text files like this. The challenge is making sure you know the layout of each line of text and hope that the text format doesn't ever change.

However, for standardized text files, this process has proven extremely successful.

Hope this hepps!

[jnordfors]

That sounds like what I am dealing with. I do have the layouts of all the different record types.

However, each record type can have a different number of records to it depending on the file.

I can have 600 records in layout "A" on one day and 1000+ on a second day, this is the same with every layout.

Since I am a newbie to VBA is there some information you can point me to about this?

[twinnyfo]

I will give you the absolute basics. We can work through details if you need....

You will need to add "Microsoft Scripting Runtime" to your VBA Editor references.

Here are the basics:

Private Sub OpenMyTextFile()
    Dim fso     As FileSystemObject
    Dim ts      As TextStream
    Dim strText As String

    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile(FileName:="My File Name")
    strText = ts.Readline

[Do whatcha gotta do here with the text string]

    Set ts = Nothing
    Set fso = nothing
End Sub

This will get you started, but I can only imagine that you will have much work to do. My code, which imports various text files runs about 1500 lines. Yours may be a more simple import. But, just be aware, that this will take some effort, depending upon how complicated and complex your data import is.

Hope this hepps!

[ADezii]

Sample Files, along with a Description of the Layouts, would be extremely helpful.

[jnordfors]

Hi

I built an import specification to import the text file as 1 column in a table in the database with a dialog box to be able to choose an import format and file location.

This is because we have different users that can parse out this data depending on the project they are working on.

I guess I need some help reading through the table once it is in access.

The format is attached, It is only for 2 record types. It looks like the first few elements up to the "Record Identifier" are standard across all the layouts some have an additional Identifier as in the "Field Type"

[jnordfors]

There are about 60 different specifications.

[twinnyfo]

In general, using the code from post #4 (with modifications, of course), it looks like you simply need to evaluate characters 30-31 of each line of text you import. If it is "01" it is for one type of record, if it is "02" then it is another type. Knowing this, you can pull any specific data based upon character locations and use the data as you wish.

I am not exactly sure what you mean by "60 different specifications. " So that may require greater detail.

However, if all the specifications have characters 30-31 as the record identifier, you may be good to go with what I've just described.

Unless I am missing something obvious (which wouldn't be the first time).

[ADezii]

Unless I am mistaken, this is a fairly complex scenario. There are 60 distinct Specifications that have to be considered. The number of Records in each Specification is irrelevant. A Text File, conforming to any of 60 Specifications is Imported into a single Field in Access and is subsequently parsed based on that Specification details. If what I have stated is true, you may be able to accomplish this with two Related Tables and a Public Function. The Code, however, may be a little complicated, but there will be no need for any Queries or Import Specifications. Just let me know if my previous assumptions are correct, and I will see what I can come up with.

P.S.- The step of Importing each line into a single Field would also be eliminated. Each line would be added directly into the Master Table as Long as the Spec is known.

[ADezii]

One more question, do the Text Files have Field Names in the first row?

[jnordfors]

Hi ADezii,

You are correct in your assumption.

There are no field names in the first row.

I have been able to create a form that allows the user to browse for the file location and choose witch specification to use while importing

Thanks

[ADezii]

1. I am envisioning a System that will:
   1. Completely eliminate the need for any Queries.
   2. Eliminate the need for any Specifications, although the details of each Specification would need to be known just like in your Upload.
   3. Totally eliminate the step where each Line of the Fixed Width File is Imported into a single Field in a Table then parsed.
2. How I intend to go about this (not a simple Task as I see it):
   1. Use Low Level IO Functions to read each Line of the Fixed Width File, Parse each Line, then append the individual Values directly to a Table, all in a single step.
   2. The Field Names and Start and Stop positions for each Field would be contained in Reference Tables then read into the Code. This is the difficult part.
3. I created simplified Demo to illustrate how this can be done. The Logic makes some major assumptions which may/may not be true. Only you can make that determination.
4. Rather that post the Code, which is actually simple at this point, I will Upload the Demo.
5. Extract both the Demo (.mdb) and the Fixed Width File (.txt) into the 'same' Folder, or else it will not work. The Demo is partially based on the Participant Dates Specifications.
6. Interesting Project, let me know how you make out.

[twinnyfo]

Adezii,

Your approach is very similar to what I use. It is straightforward , but not simple. Thanks for fleshing this out for the OP!

Great work!

[ADezii]

Thanks twinnyfo, the hard work is yet to come!(LOL)

[ADezii]

1. I created a more detailed Demo for you that will:
   1. Allow you to select from one of two Specifications in a Combo Box.
   2. Opens a File Dialog Box filtered for Text Files (*.txt) only.
   3. Via the Office File Dialog, allows you to select a Text File. Once a File has been chosen, it will Open that File and use the selected Specification Parameters to populate a Table with it's contents, line-by-line.
   4. I have no way of knowing what Text Files would go with what Specifications, so that is something that you would have to deal with. Should you select a File that does not match a Specification then you will either generate a Run Time Error or gibberish will be displayed in the Table.
   5. For this Demo, it is obvious what File goes with what Specification.
2. The Code is somewhat complex, unorthodox, very detailed, and specific.
3. The advantages are that it does not require any Queries or named Specifications, and performs the Opening of the File, Parsing it, and Appending the Data into a Table in a single operation.
4. Should you have any questions, I imagine that there would be, feel free to ask. I can't speak for twinnyfo, but I am sure that he would be willing to assist also.