Results 1 to 4 of 4
  1. #1
    ArchMap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    2

    Import multiple Text files with a Macro

    I have hundreds of text files (CSV) that I need to import into Access and export as dbf files. I can do it one at a time, but it's painstakingly slow. I can concatenate all the files into one text file before import, but when I do that I lose the ability to tell what value is associated with what original file (which is important... think of the file names as dates and the data is XYZ positions).



    What I would like to do is write a macro that imports all the text files in a directory into separate tables, and exports each as a dbf, preserving the original filenames. Or, imports all the texts files into one table, but adds the attribution of the original file name to each record (so I can query by file name later).

    Any thoughts?

    Thanks.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    this can be done with a simple loop through a directory in vba.

    furthermore, why import into access and then export right back out? what's the point? can't you just rename the text files and dbf files? Not sure about that, but you might as well try it. database files of multiple extensions can be opened by multiple programs.

  3. #3
    ArchMap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    2
    I would like to not have to not have to convert my text files into DBFs at all. But the CSVs in text format are the only export from a surveying system we use. And the processing software cannot take the CSVs as they are generated... sigh. So we convert. It's not a big deal when we only have a few files at a time and keep on top of it, but lately I've been very busy and been generating data by the gig. The date and time stamp get written in the file name and not the file itself (hence the need to preserve the original file names somehow).

    The point is, if there is a better way to do a bulk conversion of multiple text files to DBFs that does not require using Access, I'm all ears.

    Scripting in VBA is not one of my strengths. I was playing with the TransferText action. Any advice on what other actions I should use to get the loop going?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    no....transfertext should work fine. that's an importing tool. that'll be fine. just loop your files accordingly. if you need help with it, just let me know.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import multiple text files automatically
    By instructorTek in forum Import/Export Data
    Replies: 30
    Last Post: 10-20-2012, 04:50 PM
  2. Import Text Files without access
    By 95DSM in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 04:36 PM
  3. Replies: 4
    Last Post: 07-15-2010, 05:58 PM
  4. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums