Edit and Import text files in VBA Access 2007

Posted on 2007-08-03
Last Modified: 2012-06-27
I have 6 .txt files which i need to edit (subtitute a / for a , to make it fully comma delimited) and Import the text files one by one into an Access 2007 table. Thre is no header row in the incoming files. This needs to be done up to 5 times a day so it needs to be imported through code.  Snipett of code would go along way to helping me solve this one, many thanks in advance.
Question by:downehouse
    LVL 77

    Expert Comment

    You can specify the import if you go through it once for real; there is no need to edit the file, just set the field delimiter to the / as you create the specification.

    Author Comment

    The delimiters are mixed some are already a comma some are a /
    eg ADC/34,7UJKMIO,890,765,456/90
    The result I need is ADC,34,7UJKMIO,890,765,456,90
    I know you can store an import routine in Access 2007 but how do you call it in VBA code?
    LVL 77

    Expert Comment

    Are the / and , consistent in where they appear?
    If not you will have to read the files in through file handling code and sort out the fields.

    If you are able to develop a spec then you can use :
    to import the data and you can use a named spec in that command.

    However you can't use this unless the record structure is consistent.

    Author Comment

    Unfortunately the position changes by 1 or 2 places across the total import. Yes I feel I need to handle each line programatically.
    LVL 18

    Expert Comment

    This is what I use for multiple file specs, Transfertext statement has True for Column header. I have a file built that includes text file names, table to send to and import spec name used

    Public Sub GetMilFiles()
    Dim MyARFile As String
    Dim MyTable As String
    Dim MyFileSpec as String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblMilARFileList ;", dbOpenSnapshot)
        Do While Not rs.EOF
            MyARFile = rs!MilARFiles
            MyTable = rs!TableName
            MyFileSpec = rs!UseSpec
            DoCmd.TransferText acImportDelim, MyFileSpec, MyTable, MyARFile, True, ""

    End Sub

    Author Comment

    Thats great, thanks. But I still need to edit the text file line by line before inport or am I missing something??
    LVL 18

    Expert Comment

    You could fire off a VB script at the start of the routine to edit every file in the folder and change whatever character you choose, like below:

    'Script Name = FindAndReplace.vbs
    'Windows Scripting must be enabled for this script to run
    'This VB script searches a folder and files by specified type, and replaces every occurance of
    ' of that string in every file in that folder of that type

    Const ForReading = 1
    Const ForWriting = 2

    Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
    'Enter folder to search on the line below ==================================Enter Param Below
    Set folder = fso.GetFolder("J:\Acct\IC_Data\Reports\ARTB\")
    Set files = folder.files
    'Supply text to search for on the line below ===============================Enter Param Below
    varText = " "
    For Each f1 In files
    'Select file type to search and replace below ==============================Enter Param Below
        If InStr(f1,".txt") > 0 Then
        Set f = fso.OpenTextFile(f1,ForReading)
        If Not f.AtEndOfStream Then
    'Supply text to replace with on the line below =============================Enter Param Below
            varNew = Replace(f.ReadAll,varText,"")
            Set f = fso.OpenTextFile(f1,ForWriting )
            f.Write varNew
        End If
        Set f = Nothing
        End If

    'MsgBox "Change OK"
    LVL 77

    Expert Comment

    No, you will have to do it programmatically - and it sounds like your original idea of reaching a record, replacing / with , and then parsing the result is probably the best route.

    But it's not my strength so I'll try to rustle up some better expertise.

    LVL 18

    Expert Comment

    You can stick this at the top of the routine

    Sub DirtyHarry()
          Dim retval As Long
          retval = ExecCmd("J:\Acct\IC_Data\Utilities\GL\FindAndReplace.vbs")
          'MsgBox "Process Finished, Exit Code " & retval
     End Sub

    But you're going to have to shell and wait for the script to finish, so you'll need :

    Option Compare Database
    Private Type STARTUPINFO
          cb As Long
          lpReserved As String
          lpDesktop As String
          lpTitle As String
          dwX As Long
          dwY As Long
          dwXSize As Long
          dwYSize As Long
          dwXCountChars As Long
          dwYCountChars As Long
          dwFillAttribute As Long
          dwFlags As Long
          wShowWindow As Integer
          cbReserved2 As Integer
          lpReserved2 As Long
          hStdInput As Long
          hStdOutput As Long
          hStdError As Long
       End Type

          hProcess As Long
          hThread As Long
          dwProcessID As Long
          dwThreadID As Long
       End Type

       Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
          hHandle As Long, ByVal dwMilliseconds As Long) As Long

       Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
          lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
          lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
          ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
          ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
          lpStartupInfo As STARTUPINFO, lpProcessInformation As _

       Private Declare Function CloseHandle Lib "kernel32" _
          (ByVal hObject As Long) As Long

       Private Declare Function GetExitCodeProcess Lib "kernel32" _
          (ByVal hProcess As Long, lpExitCode As Long) As Long

       Private Const NORMAL_PRIORITY_CLASS = &H20&
       Private Const INFINITE = -1&

       Public Function ExecCmd(cmdline$)
          Dim proc As PROCESS_INFORMATION
          Dim start As STARTUPINFO

          ' Initialize the STARTUPINFO structure:
          start.cb = Len(start)

          ' Start the shelled application:
          ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
             NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)

          ' Wait for the shelled application to finish:
             ret& = WaitForSingleObject(proc.hProcess, INFINITE)
             Call GetExitCodeProcess(proc.hProcess, ret&)
             Call CloseHandle(proc.hThread)
             Call CloseHandle(proc.hProcess)
             ExecCmd = ret&
       End Function
    LVL 28

    Accepted Solution

    Another method:
    Open file.  Save contents as string.
    replace "/" with "," in string
    save newstring to a new file (same prefix, extension 'csv'

    Following will do this to all files with txt extension in folder.

    Function addcomma()
    Dim vfile As String
    Dim voutfile As String

    Dim vpath As String
    vpath = "c:\pathToFolderWithTextFiles\"
    Dim fh As Integer
    fh = FreeFile
    Dim fh2 As Integer
    fh2 = FreeFile
    Dim vstring As String
    Dim fullstring As String
    vfile = Dir(vpath & "*.txt")
    Do While vfile <> ""
    vfileout = vpath & Left(vfile, InStr(vfile, ".")) & "csv"
    Open vfile For Input As #fh
    Do Until (EOF(fh))
    Line Input #fh, vstring
    fullstring = fullstring & vstring & vbCrLf
    Close #fh
    Open vfileout For Output As #fh2
    fullstring = Replace(fullstring, "/", ",")
    Print #fh2, fullstring
    Close #fh2
    fullstring = ""
    vfile = Dir()

    End Function
    LVL 58

    Assisted Solution

    > I feel I need to handle each line programatically.

    In that case, nothing beats direct read of the text. It's not that hard and surprisingly fast.


    Sub ImportText(pstrPath As String)

        Dim recImport As DAO.Recordset
        Dim strLine As String
        Dim strFields() As String
        Dim intF As Integer

    On Error GoTo Problem

        ' open recordset, open text file:
        Set recImport = CurrentDb.OpenRecordset("tblImported", dbOpenDynaset)
        Open pstrPath For Input As #1
        ' read text line by line
        Do Until EOF(1)
            Line Input #1, strLine
            ' adjust separators and split
            strLine = Replace(strLine, "/", ",")
            strFields = Split(strLine, ",")
            ' append record
            With recImport
                ' skip field(0), which is an autonumber
                For intF = 0 To UBound(strFields)
                    .Fields(intF + 1) = strFields(intF)
                Next intF
            End With
        ' cleanup
        Close #1
        Exit Sub
        MsgBox Err.Description
        On Error Resume Next
        GoTo Done
    End Sub


    This is purely positional, of course. It can throw many different errors, including too many fields, type conversion, etc. You might need to add some code to handle some fields specially, for example dates.

    I assumed that the import table would have an autonumber as key field in the first position; data from the text file is thus written in fields 1...n.

    Good luck!

    Author Comment

    jerryb30: This looks just want I want but it errors on line ' Open vfile for input as #fh
    any clues??
    LVL 28

    Expert Comment

    make sure you have vfile populated.
    Can you post yout code as amended, and maybe a file name?
    Does it compile?  Not sure if you need any special reference for FreeFile.

    Author Comment

    jerryb30: Thanks, got this work now. String handling mistake. Really fast!

    harfang:Can you explain how you decypher the string of each line to determine when one field starts and the next begins ie a comma. I can then use you idea with jerry30 for the complete solution.

    Thought I might as well add individual text lines to main import table as we go with an open record set.

    This is really appreciated.
    LVL 58

    Expert Comment

    > I can then use you idea with jerry30 for the complete solution.

    In fact, mine is a complete solution. If you go with jerry30's code, you will basically create six new files, which you later import using an import file specification.

    My suggestion imports one file and appends it to a table, accepting both the '/' and the ',' as separator, without actually altering the file in any way. Additional code can be added if the problem grows more complex than you first thought.

    Some comments on relevant lines:

            ' this reads one line from the file into the variable strLine
            Line Input #1, strLine

            ' any manipulation can be done on that line
            ' here all '/' are replaced with ',':
            strLine = Replace(strLine, "/", ",")

            ' split() is used to split the line into smaller strings, using the ',' as separator
            ' the result is a string array
            strFields = Split(strLine, ",")

    a bit further down

                    ' copy array element n into field n+1
                    .Fields(intF + 1) = strFields(intF)

    As you see, this performs exactly what you requested: an edit and import of a text file, on the fly.

    Which you prefer is really more a matter of taste and maintainability than anything else.

    LVL 28

    Expert Comment

    Markus is right. One or the other. Both work. If your input is static.  (The difference is maybe only in doing multiple files.)

    Author Comment

    Bothe excellent solutions so split points to be fair, thanks again to all.
    LVL 58

    Expert Comment

    Welcome, and success with your project -- (°v°)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now