Automating a Fixed Width Import from Text and CSV file

[rweakley]

Hello,
I'm using Access 2010
I've created a couple of databases at my new job for maintaining files that are brought in on a daily basis. Most of my work so far has just been with queries to update and format the data as it comes in.

We receive multiple text files and csv files each day. The text files go into one database and csv files in another.

Both files are around 2000 characters long, and are imported using a saved fixed width specification. It hasn't been too much of a hassle to import these manually as they come in, but things have picked up recently and it is becoming time consuming to do each of these files manually.

I'm looking for a way to "automate" this a bit as the import steps are the same each time. I haven't been able to use the "Saved Imports" feature as the files come with different names so I would like a way to still have the "Browse" feature open so that the file can be selected manually.

The Import Steps are:

External Data > From Text > Import into new table > Advanced > Specs > Open > Select Primary Key > Name Table(this is always the same).

I'm not familar with <acronym title="vBulletin">VB</acronym>, but I'm always willing to learn and try and get my hands dirty.

Once this part and built, I will be having other people begin Importing the daily files into this database, so I'd like the end product to be as simple as possible where all thats needed is to select the file being imported and then all the other steps above are done behind the scenes.

Thanks for any help!

 

[xenou]

Does it work without the file dialog? - just hard code in the file path (to test it)

 

[rweakley]

Does it work without the file dialog? - just hard code in the file path (to test it)

Yes, that does seem to work.

After taking another look at the files it appears that the issue is coming from the date convention used on the files.
The files were originally being called MM.DD.YYYY_FileName.csv After changing the convention on one to MM_DD_YYYY_FileName.csv the Import seems to be working fine.

I'm still very new to this, so forgive me if this was something commonly known.

Thanks again for the help, Joe and Xenou!!

 

[Joe4]

Yes, just like spaces, periods in file and folder names can be problematic for code.
A good rule of thumb is to only use letters, numbers, and underscores (and maybe dashes) in file and folder names. Avoid all other punctuation or special characters.