Text to Table

[jive6243]

Hello! I have written a small piece of code for an Access 97 database to import multiple text files into 1 master table on a weekly basis. Each text file contains approximately 10 columns and 96 rows. Now for the tricky part:

Each text file may contain rows that are identical within another text file. Which means, when they are all imported into the master table, there will be duplicate rows.

SO...what I am looking for is a bit of code that will take the name of the text file and append it into a "FileName" column in the master table for each row (row # may vary by file) in the text file.

Can anyone help me?!

 

[The_Doc_Man]

Since you are in VBA, here are some thoughts:

The file name is typically of the form...

dev:\path\path-step\...\path-step\last-step\name.typ
(or the same using / instead of \)

If you get this name from a FindFile loop, this is more or less what you will see for each file you find.

So if all you want is the name and not any other part, you can find the name by parsing backwards from the last character of the full file spec. The last character of the name is the location of the DOT character - 1, while the first character of the name is the location of the rightmost \ (or /) + 1. Which means the MID$ function can take that out for you if no other methods are available.

When you import the file, are you using a bit of code that loops through the records to split apart the fields to load each record in a recordset, or are you just using a DoCmd.TransferText to load the table in bulk?

If the former, you have the chance to do your insert at the record level. If the record contains a field called "FileName" then you can load it with recset.fields("FileName") = {the name you find}

If the latter, and if the tables are unique per file imported, you have the chance to run some SQL code that says, in effect, "update all rows in this table with this file name in the FileName field."

If the tables are NOT unique per file, then you have to run that SQL code modified to say, "update all rows in this table that now have a BLANK file name in the FileName field to have THIS file name in the FileName field"

 

[jive6243]

Thank you for your reply Doc Man. I apologize for not being more clear about the code. I am doing a transfertext command which is appending the tables to the master. So for the SQL command, are you saying that the filename field should be appended to the rows while they are still in the text file and BEFORE they are put in the master table?

How would I do this?

 

[The_Doc_Man]

Do the text tables exist before you do the import? (Exist but empty, presumably.)

If so, then when you create the new table, make its default for the source name to be a blank or null string. If the spreadsheet does not include a column for its own name, then all you have to do is run an update query that dumps the desired name in all records in the table for which the filename field is currently equal to that default value.

If the method you are using creates a new table with only the fields available in the text file, then you need to look at the Help Files for methods that create a new field in your new table. Look up the following topics:

Tabledefs Collection (and Tabledef object)
CreateField method as applied to tabledef object.

Remember that the way to access a named object in a collection is collection-name("object-name").

Once you create the field you can populate the records with the name of the file as suggested earlier.

 

[jive6243]

Doc Man - thank you so much for your help. I will definately try this! Thanks again!