Append a column during import of file?

[Montez659]

I am looking at moving something like 144 excel files into Access to do some analysis on them. Each of the groups of files is broken up into a specific year, so 16 of them are 2005, 16 are from 2006, etc. However, none of them actually have the year included in any columns in the file.

There might be some overlap with codes and such, so I want my PK to be from these codes and the year. Is there any way that I can get the data to append a column of the year while importing, so that I don't have to manually add a column to the 144 files?

If there is no Access answer to this, might there be an Excel answer? If so, I'll post there. TIA

 

[baderms]

Take a look at this http://www.databasedev.co.uk/add_prefix.html

 

[Joe4]

Take a look at this http://www.databasedev.co.uk/add_prefix.html

I don't think that is going to do what they want, as that would be "hard-coded" the prefix in the table ahead of time.

I had have situations like this some years ago, where I needed to capture the file name of each file I imported in a field in the table, since I was importing all the data into one table, but needed a way to identify which file each record came from. So here was the approach that I took (that may work for you):
1. Create a new field at the end of your table (maybe called "myFileName")
2. Create VBA code that loops through all files, and imports them
3. Within each loop, capture the name of the file you are importing in a variable
4. Then create an Update Query that filters on all records where the "myFileName" variable is blank, and update the "myFileName" field value to the name of the file

So, within the loop, it will Import each file, them immediately populate this new field for each record just imported. I am thinking that you could do something similar for each year, provided that the year number is either part of the file or folder name.

 

[Montez659]

Thanks Joe, sounds like an excellent idea! All the file names are the same across all the years, but the folder does contain the year so that would help me distinguish it. In addition to solving my issue, you also pointed me toward a more efficient way of doing the actual import via vba.

Thanks again!

 

[Joe4]

You are welcome!
Glad I was able to help!