Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10

    Unanswered: Importing txt file using vb

    I help oversee a rather simple Acess 2000 databse in which files are downloaded into two separate tables in order to run reports. The files are comma-delimited, daily archive files from an automated med dispensing system, and have extensions that 2000 will not recognize. At present, we chnge the extensions to ".txt" in DOS command. The file names are the dates that are archived e.g. 030404a.txt, 030504a.txt etc. To import we open a TransferTxt macro in design view and change the names on the source line to each date file on the floppy. It's not a very efficient system. Would like to find a code that will change the extension (already found this on this forum) then loop thru the files on the floppy and download into appropriate tables. There are four different file types for each day, so on a floppy containing a week's worth of data, there are 28 different files, but we download only two types of files into two different tables. Consequently, we probably need two nearly identical modules for the specific files. I am a real amateur at using VB, so it is really a struggle for me. Any help would be GREATLY appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13
    You have two different file structures. Therefore you need to create two new Import Specifications.

    Import Specs can be created when you manually import Data:- File\Get External Data\ and then click on Advanced (after you have located a sample file. You need to save the Import Spec.

    Then you can use this code.

    _________________________

    Sub temp()
    DoCmd.SetWarnings False
    strTblName = "imp_Call Type"
    strImportSpec = "Call Type"
    ImportFiles

    strTblName = "imp_Dial Codes"
    strImportSpec = "Dial Code"
    ImportFiles


    strTblName = "imp_Period"
    strImportSpec = "Period"
    ImportFiles

    DoCmd.SetWarnings True

    End Sub
    ___________________________________

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13

    Continued

    Sub ImportFiles()
    Dim strImpPath As String
    Dim strImpFileNameExt As String
    strImpPath = "A:"
    strImpFileNameExt = Dir(strImpPath & "*.txt")
    While (strImpFileNameExt <> "")
    DoCmd.TransferText acImportDelim, strImportSpec, strTblName, strImpPath & strImpFileNameExt, 1
    strImpFileNameExt = Dir
    Wend
    End Sub
    _____________________
    The first Sub imports three different Specs into three different files using the same tables and same text files. If your text files have different formats you will have to alter slightly (e.g. use sub folders on the A: Drive)

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13

    Continued ... 2

    This answers the second part of your query (importing and looping through the files). You may be able to import in their current format. If not then use the Name Statement. Access XP help as below
    ______________________________________
    Name Statement Example
    This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, “HD:” is the default drive name and portions of the pathname are separated by colons instead of backslashes.

    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
    Name OldName As NewName ' Rename file.

    OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
    Name OldName As NewName ' Move and rename file.
    ________________________________

    You would have to loop through files as in Reply (1)

  5. #5
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Thanks for the help. Will try it out on Monday.

  6. #6
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13
    No problems .. let me know if you can't get it to work :-)

  7. #7
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Sorry it took so long to reply. The import section worked great, but could not get the renaming code to work because it apparently did not like a wildcard in filename. I was trying OlName = a:*.PYX MewName = *.TXT. and it kept coming up with an error code saying the file was not found.
    I was wondering if there was a way to rename all of the "*.PYX" files on the floppy to "*.TXT", run the import code, then change the names back to "*.PYX. I could have another sub on a button click that would change the "*.DTX" files to "*.TXT", import the files, and then change these files back to the original file extension. That would eliminate the need to create folders on the floppy and move the files.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •