Import CSV to a specific table based on part of file name

[sxschech]

Trying to see if this can be automated...
We'll be receiving email attachments that are downloaded (extracted) locally. The file names are named the same as the subject header which could either be like

Photography 3 More Inquiries from xyz.com on 07_27_2006

-OR-

http://www.AllPhotography.com

3 More Inquiries from xyz.com on 07_27_2006

If more than one email file is received with the same subject, it will have an incremental number and look like this:

http://www.AllPhotography.com

3 More Inquiries from xyz[red]_1[/red].com on 07_27_2006

Presently we get about 12 of these email files a day and there are 4 different groups. Photography, Film, Nature, Archit. For example, we could get 1 Photog, 3 Film, 6 Nature and 2 Archit.

Any thoughts on how I can load these csv files into the correct table in access with VBA. They need to be in their own table because the data structure differs between the 4 groups.

The subject header is the way it is because it is provided to us from an outside vendor and this is how they send it to all their clients, so probably won't be able to change it. We are using Eudora as we don't have Outlook.

 

[Remou]

If all these files are in a specific to-be-processed directory, you could use Dir to select all the files with 'photography' in the name for processing and so on for each of the groups. Very roughly:

'Not real code
astrList = Split("Photography,Nature,Film,Architecture",",")

For i = 0 To Ubound(astrList)-1
   strFile=Dir("C:\ToBeProcessed\*" & astrList(i) & "*.csv"
   Do While strFile <>""
       DoCmd.TransferText acImportDelim, , "tbl" & astrList(i), strFile
       strFile=Dir
   Loop
Next

 

[MichaelRed]

but ... a small caution ... it is poor practice to directly import foregin files into your db. the prefered approach would be to import to a temp structure (file, udf, ... ) and do some V&V on the temp structure and then append the ("Good") records to your production tab;es. Any records which FAIL the V&V process may be A) examined / corrected and appended; B) Discarded; or C) returned to the source (sender) for explination / re-release ...



MichaelRed

 

[sxschech]

Thanks guys. I'll try the code and let you know. The reason I'm using Access is to combine the like csv files into one of the four groups for loading via a web page (easier to load 4 than 12), so in effect, the tables are temp structures since they will be cleared before the next load. I was thinking of using the combine command in an MS-Dos window, but since the files contain header information, I figured Access would be a better way to combine since importing won't put the header into the data rows.