File Import Automation

[capjlp]

Hi Folks

I am trying to setup a daily process where files are picked from a directory and imported into a table then process run to move that data to the correct places.

I am getting around 16 files a day from different organization and I need to import the file that came in today. Or the latest file.


The file are stamped with the time and date so I can't really set a hard coded file name.

Example

I have a directory T:\Inbound

Under that I have several Directories.



Example

\Organization1
\Organization2

all the way to 16

The files may look like

ORG120161103150600.csv
ORGAN2201611031935.csv


I want to run a saved import process and use the file name it finds from the latest file.

I have done some searching and see people trying it but it hasn't been explained well enough for me to replicate it.

Any help is appreciated.

Thank you

[ssanfu]

Is the structure of the all of the CSV files the same?
Or are all thethe headers all the same?

In general steps:
The first thing you need to do is select the folder where the files are stored, unless you want to hard code the path to be "T:\Inbound". Or you can default the initial folder to "T:\Inbound" and still allow you to pick a different folder.

Then you need to create a list of all the CSV files in the folder and all of the subfolders. The list can be a table, list box, array, ....
Next you will loop through the "list" of file paths & names to import each of the files.

then process run to move that data to the correct places.

Your process to move the data to other tables can be run after each import or after all files have been imported.

use the file name it finds from the latest file.

You will have to explain what you mean by the "latest" file.
After you import the file, I would move it to a different folder if you need to keep it for a while or delete it so it doesn't get imported a second time.



-------------------------------------
You can use FSO to select a folder.

Here are two sites with code to call the Dir command recursively.
http://www.ammara.com/access_image_f...er_search.html
http://allenbrowne.com/ser-59.html

You could use the "TransferText" command with an import spec to import the files.

Last is you code to process the files.

[capjlp]

Is the structure of the all of the CSV files the same?
Or are all thethe headers all the same?

In general steps:
The first thing you need to do is select the folder where the files are stored, unless you want to hard code the path to be "T:\Inbound". Or you can default the initial folder to "T:\Inbound" and still allow you to pick a different folder.

Then you need to create a list of all the CSV files in the folder and all of the subfolders. The list can be a table, list box, array, ....
Next you will loop through the "list" of file paths & names to import each of the files.

Your process to move the data to other tables can be run after each import or after all files have been imported.



You will have to explain what you mean by the "latest" file.
After you import the file, I would move it to a different folder if you need to keep it for a while or delete it so it doesn't get imported a second time.



-------------------------------------
You can use FSO to select a folder.

Here are two sites with code to call the Dir command recursively.
http://www.ammara.com/access_image_f...er_search.html
http://allenbrowne.com/ser-59.html

You could use the "TransferText" command with an import spec to import the files.

Last is you code to process the files.

Hi

I want this to be an automated process that runs daily. So I want to hard code the folder location for the initial T:\Inbound the file structures are all the same they are using a specified file format. By the latest file I mean the one they upload today.

Thanks for the help.

[ssanfu]

I have a directory T:\Inbound

Under that I have several Directories.

Example

\Organization1
\Organization2

all the way to 16

The files may look like

ORG120161103150600.csv
ORGAN2201611031935.csv

Q1) Is the structure of the all of the CSV files the same?


Q2) Do you need to save the file after it is imported?

Q3) Break down the file name...how do you know if the file is today's file?

Q4) Do you have any VBA code for what you are trying to do?