[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4330
  • Last Modified:

Edit and Import text files in VBA Access 2007

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.
  • 6
  • 3
  • 3
  • +2
2 Solutions
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.
downehouseAuthor Commented:
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?
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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

downehouseAuthor Commented:
Unfortunately the position changes by 1 or 2 places across the total import. Yes I feel I need to handle each line programatically.
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
downehouseAuthor Commented:
Thats great, thanks. But I still need to edit the text file line by line before inport or am I missing something??
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"
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.

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
      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 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
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
> 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!
downehouseAuthor Commented:
jerryb30: This looks just want I want but it errors on line ' Open vfile for input as #fh
any clues??
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.
downehouseAuthor Commented:
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.
> 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.

Markus is right. One or the other. Both work. If your input is static.  (The difference is maybe only in doing multiple files.)
downehouseAuthor Commented:
Bothe excellent solutions so split points to be fair, thanks again to all.
Welcome, and success with your project -- (°v°)

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now