Microsoft Access VBA Macro Programming

Else

temp1 = temp1 & Mid(temp, m, 1)

End If

Next m

Print #fnum1, temp1

Loop
Close fnum
Close fnum1

End Sub

This code opens up the big file as an input file and opens an output file. It then iterates
through the entire big file, reading each row into the variable temp. It then moves through the
file, temp character by temp character. If it finds a tilde (~), it replaces it with a comma (,). If
it finds a single quote mark (‘), it does nothing, so the character disappears. If it is any other
character, it just concatenates it to the new string that is being created for that row (temp1).
It then writes the string temp1 to the output file and loops back around.
If you are removing single quote marks, you may run into problems if there is text with a
quote mark that is meant to be housed there. An example might be a person’s name, such as
O’Leary.
This procedure took me about 90 minutes to run, but at the end of it, I had a CSV (Comma
Separated Values) file with no text qualifiers in it. Access is far more at home with CSV files,
and I was able to get a working link to my new text file. However, using a simple query on
the linked file still caused problems.
The answer was to split the file into two files and have two linked files on my Access
database. I could create a query for each one to pull out the relevant data and then use a union
query to join the two outputs together. See Chapter 12 for more information on SQL queries.
My code to create the output file was modified to the following:

Sub CreateSplitFiles()
Dim temp As String, temp1 As String, temp2 As String, n As Long

n = 1
fnum = FreeFile

Open "C:\MyBigFile.txt" For Input As fnum
fnum1 = FreeFile

Chapter 30: Handling Large Text Files 309