Importing 58 csv files into 58 Access tables

[Deirdre]

Good morning. Yes, I know the 58 tables aren't ideal but it is the hand I have been dealt.

Each month, I will receive 58 csv files. These files will all be contained in one folder which will vary each month by name (e.g. 20170331, 20170430, 20170531, etc).

The file names will not change nor will their 4-column structure. The will always be csv.

I would like a macro that will import/append the 58 files into their respective tables.

For example:
DoNotCall.csv would be imported into DoNotCall table
DoNotEmail.csv would be imported into DoNotEmail table

I have searched many threads, but none quite seem to do exactly what I need.

Any and all assistance is appreciated.

Thank you.

 

[Joe4]

These files will all be contained in one folder which will vary each month by name (e.g. 20170331, 20170430, 20170531, etc).

DoNotCall.csv would be imported into DoNotCall table

So what exactly do these files names look like?
It sounds like they may have datestamps, but you did not show that in the example names.

And do all of these tables have the exact same table structure?

 

[Deirdre]

Good morning and thank you!

The file names will always be the same and will not have a date modifier. They are as I have described, "DoNotCall.csv", "DoNotEmail.csv", "DoNotBVM.csv", etc. The folder in which they will reside will have the date qualifier. I suspect I would use a command that will ask where/which folder the files reside.

The table names within access have the same names as the files (e.g. DoNotCall.tbl, DoNotEMail.tbl, DoNotBVM.tbl, etc). So, ideally, I would like something that will prompt me to identify a folder, then import/append all of the files within that folder to their respective tables. File names and structure are constant. In fact, the file structure is the same in all 58 files: Division, Region, Count, MonthEnd

I hope I've been able to explain it better.

 

[Joe4]

Which version of Access are you using?

And do you know how to set up an Import Specification for the files?

 

[Norie]

If all the files are structured identically why are you importing them into 58 separate tables?

In fact, why do you have 58 separate tables with identical structure?

 

[Joe4]

If all the files are structured identically why are you importing them into 58 separate tables?

In fact, why do you have 58 separate tables with identical structure?

That was my first thought too, but then I noticed this line:

Yes, I know the 58 tables aren't ideal but it is the hand I have been dealt.

So it sounds like an issue outside of her control.

 

[Deirdre]

That was my first thought too, but then I noticed this line:

So it sounds like an issue outside of her control.

^ Correct

I am hoping with a new or updated CRM (in the works)I can stop the madness before it begins! For now, I'm just working with what's thrown my way (and trying to not have to work very hard because of it!)

I've used ImportSpecification for stuff like formats, delimiters, etc. The part that really escapes me is how to tell VBA to append File X to Table X and File Y to Table Y.

 

[Deirdre]

Which version of Access are you using?

And do you know how to set up an Import Specification for the files?

My apologies for missing this. I am using Access 2013.

 

[Norie]

How are you currently importing the files?

If you were to use VBA you could use DoCmd.TransferText where you can specify the table to append to using the TableName argument.

 

[Joe4]

I've used ImportSpecification for stuff like formats, delimiters, etc. The part that really escapes me is how to tell <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> to append File X to Table X and File Y to Table Y.

I am whipping up some VBA code for you. It just take a little time.