Importing Fixed Width Text Files, with a Caveat

[JoeM]

I am creating an automated Access application to import a large fixed-width text file into Access to do some validations a few times a week. Should be simple, right? Just create an Import Specifidation based on the specs to import it into my table (I have done this hundreds of times).



Here is the kicker. The data begins in column/position 7 of my text file (the first 6 spaces on every records are supposed to be blank). However, the first line is usually missing two blank spaces at the beginning, so it only has 4 blank spaces and actually starts in position 5. So, the first record is two characters shorter than all the other records (everything is shifted over two spaces to the left).

The question is, how to account for this when importing into Access? I thought of trying to create some process to check the record length of the first record, and add two spaces at the beginning of row 1 if necesssary. But I am note sure what the best way of doing this might be.

Anyone have any ideas?

Thanks

[RuralGuy]

It should be relatively easy to check the 1st record and adjust the length using VBA. How long are the files?

[JoeM]

The files have well over 100,000 records in them, though it is just the first record that is problematic.
Each record should be 353 characters long. The "bad" first one is 351 characters long.

I would like to check the length first before adding two spaces to the beginning of the first record in the event that the vendor fixes the issue, I won't need to re-adjust the program.
My thought was to fix the original text file, then import it.

[RuralGuy]

Still, it is not very difficult to add those characters when needed and even at 100K records the process should not take very long.

[JoeM]

OK, I looking for efficient code to do that precise thing. I haven't programmed Access VBA that directly edit text files before. I have only imported or exported text files via Access VBA code.

[ssanfu]

Well, FWIW, you could try this code. You will have to set a reference to Microsoft Scripting Runtime"

What it does is read from one file, test the length of the first line and writes to another file.
Loops through reading and writing the remaining lines.
Deletes the original text file
Renames the new text file toi the old text file name.
There are ways to read in the complete text file, edit it, then write it out to the same file...... but I don't know how to do that..

---- Warning - try on a test Text file -----
( this runs in A2K - don't know about A2K7 )

Option Compare Database
Option Explicit

Public Sub Filetest()

   Const ForReading = 1
   Const ForWriting = 2

   Dim objFSO As Object
   Dim OrigName As String
   Dim NewName As String
   Dim objFileOrig  'Orig file
   Dim objFileNew  'New file
   Dim ThePath As String
   Dim iLineNumber As Long
   Dim strLine As String
   Dim strNewContents As String

   'could use open/save code to select a file check/import
   OrigName = "TestFixedFile.txt"

   'any name not in use. It will be overwritten. And renamed
   NewName = "AAA123.txt"

   ThePath = CurrentProject.Path

   Set objFSO = CreateObject("Scripting.FileSystemObject")

   Set objFileOrig = objFSO.OpenTextFile(ThePath & "\" & OrigName, ForReading)
   'create the new text file
   Set objFileNew = objFSO.CreateTextFile(ThePath & "\" & NewName, True)
   objFileNew.Close
   Set objFileNew = objFSO.OpenTextFile(ThePath & "\" & NewName, ForWriting)

   iLineNumber = 0

   Do Until objFileOrig.AtEndOfStream
      strNewContents = ""
      strLine = objFileOrig.ReadLine
      iLineNumber = iLineNumber + 1
      If iLineNumber = 1 Then
         If Len(strLine) <> 353 Then
            strNewContents = Right("0000000" & strLine, 353)
         End If
      Else
         strNewContents = strLine
      End If
      objFileNew.WriteLine strNewContents
   Loop
   objFileOrig.Close
   objFileNew.Close

   ' delete original text file
   Kill ThePath & "\" & OrigName

   'rename file to original text file name
   Name ThePath & "\" & NewName As ThePath & "\" & OrigName

   '-----------------------
   ' for testing comment out or remove
   MsgBox "Done"
   '-----------------------

   'continue with automation import code


End Sub

[JoeM]

There are ways to read in the complete text file, edit it, then write it out to the same file...... but I don't know how to do that

Yeah, I am in the same boat. I know there must be ways to do that, but am not sure how to do that either.

Thanks for the code though, that should be an acceptable workaround.

In an unexpected twist of fate, I just found out that the vendor was able to correct their file (which usually doesn't happen that quickly), so it looks like I may not end up needing the code now after all. Still, it is good stuff to know, as it might come in handy for future projects.

Thanks again!

[ssanfu]

Excellent.

I was fun putting together the code. I haven't used filesystem object before.