Parsing a byte array with VBA using MS Access

Question

I have a 100 million character text file that I'm trying to import into an MS Access database. The file does not have any linefeeds so it is just one giant line of text. I tried loading it into a string variable but that did not work because of the size. I then successfully loaded it into a byte array, but I'm unsure of how to parse it the way I need it. The file has fixed length records, but has more than one type of record. One type may be 180 characters of data and 220 characters of filler, and another may be 100 characters of data and 300 characters of filler. I would like to the different record types into separate tables. I was thinking of rewriting the data to a new text file in 400 character chunks, and then maybe using Trim to get just the data I need with no filler. From there I could test the line length and import.

Here is what I have, but it does not work.

Public Sub modMain_ParseAQTFiles()

Dim bytFile(400)    As Byte
Dim intFileIn       As Integer
Dim intFileOut      As Integer
Dim intFileOut1     As Integer
Dim intFileOut2     As Integer
Dim intFFIn         As Integer
Dim intFFOut        As Integer
Dim lngBytePos      As Long
Dim dblStartChar    As Double
Dim lngNoRecs       As Long
Dim lngIndex        As Long
Dim strFileIn       As String
Dim strFileOut1     As String
Dim strFileOut2     As String
Dim strLineOfText   As String
Dim strTextLine     As String
Dim strUserName     As String

'Get username
strUserName = Environ("Username")

'Set file paths
strFileIn = "C:\Users\" & strUserName & "\Desktop\Pooltalk\aqt.txt"
strFileOut1 = "C:\Users\" & strUserName  & "\ Desktop\Pooltalk\            AQT_Quartiles_Header-out.txt"
 strFileOut2 = "C:\Users\" & strUserName & "\Desktop\Pooltalk \AQT_Quartiles_Detail-out.txt"

'Reads data into byte array
intFFIn = FreeFile
intFFOut = FreeFile
dblStartChar = 1
Open strFileIn For Binary Access Read As #intFFIn
lngNoRecs = LOF(intFFIn) / 400
For lngIndex = 1 To lngNoRecs
     Get #intFFIn, dblStartChar, bytFile
     strLineOfText = StrConv(bytFile, vbFromUnicode)
     Open strFileOut For Binary Access Write As #intFFOut
     Put intFFOut, dblStartChar, strLineOfText & vbCrLf
     Debug.Print strLineOfText
     dblStartChar = dblStartChar + 400
Next lngIndex
Close #intFFIn
Close #intFFOut

End Sub

I would be happy to hear if anyone has any advice to get this working. Thanks.

EDIT:

Here is one record type:

1004569 AS20170431360FCE319840701

34 characters of data and 366 blanks

Here is the second record type:

200456906875{06875{06875{06875{06875{06875{07I07I07I07I07I07I40B40B40B40B40B40B0000630000{0000630000{0000630000{0000630000{0000630000{0000630000{48{48{48{48{48{48{05926{05926{05926{05926{05926{05926{01250{01250{01250{01250{01250{01250{06875{06875{06875{06875{06875{06875{16875{16875{16875{16875{16875{16875{

307 characters and 93 blanks.

Here is my final code:

Public Sub modMain_ParseAQTFiles()
Dim intFileIn       As Integer
Dim intFileOut1     As Integer
Dim intFFIn         As Integer
Dim intFFOut        As Integer
Dim lngNoRecs       As Long
Dim lngIndex        As Long
Dim strFileIn       As String
Dim strFileOut1     As String
Dim strUserName     As String
Dim strRecord      As String
Dim dblStartChar   As Double
Dim lngCharNo     As Long

strUserName = Environ("Username")

'Set file paths
strFileIn = "C:\Users\" & strUserName & "\Desktop\Pooltalk\aqt.txt"
strFileOut1 = "C:\Users\" & strUserName & "\Desktop\Pooltalk\AQT_Parsed.txt"

strRecord = Space$(400)
dblStartChar = 1

'Reads data into byte array
intFFIn = FreeFile
Open strFileIn For Binary Access Read As #intFFIn
intFFOut = FreeFile
Open strFileOut1 For Binary Access Write As #intFFOut

'Find number of records
lngNoRecs = LOF(intFFIn) / 400

For lngIndex = 1 To lngNoRecs
    Get #intFFIn, dblStartChar, strRecord
    strRecord = Trim(strRecord)
    Put intFFOut, , strRecord & vbCrLf
    dblStartChar = dblStartChar + 400
    strRecord = Space$(400)
Next lngIndex

Close #intFFIn
Close #intFFOut

MsgBox "Done!"
End Sub

open the output file before the For loop. .... would you be able to post an example of a couple of the records (different types) .... overwrite sensitive data before you do — jsotola
– jsotola, Commented Jul 18, 2017 at 2:31
if all the records are the same length, then you could do the 400 byte chunks ... maybe the data could be sorted into different tables after it is imported, just by using cleverly crafted sql insert statements — jsotola
– jsotola, Commented Jul 18, 2017 at 2:34
Ryan, no just one giant line of text with no linefeeds. I have the file specification which indicates how many characters each field takes up and the data type. — user8321081
– user8321081, Commented Jul 18, 2017 at 13:58
@jsotola, I have posted some sample records above. I guess I need a way to get data from a byte array. Something like, get position 1 to 400, then get position 401 to 800, then write this data line by line to one file, then trim them to the proper line lengths. Thanks for your reply. — user8321081
– user8321081, Commented Jul 18, 2017 at 15:06

Andre · Accepted Answer · 2017-07-18 15:22:05Z

0

If all records are 400 characters long, I would read them directly into a string variable of that length.

Dim strRecord As String
Dim x As Long
' Get reads as many characters as are in the target variable
strRecord = Space$(400)
Get #intFFIn, dblStartChar, strRecord 
' Find first 0-byte character
x = Instr(strRecord, Chr$(0))
' and trim off the fillers
strRecord = Left$(strRecord, x-1)

See https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/get-statement at the bottom (before the example).

answered Jul 18, 2017 at 15:22

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user8321081 Over a year ago

This helped me get it working. I had trouble with the Chr$(0), so that must not be the character used as filler. I did use Trim to get rid of the filler, and then set strRecord back to equal Space$(400) before reading in another record. I posted the code above. Thanks for your assistance.

Andre Over a year ago

Ah yes. I'm actually not sure where I got the idea that the filler bytes were 0-bytes = Chr(0). If they are spaces/blanks, Trim() is indeed the way to go.

user8321081 Over a year ago

Thanks again for your help Andre.

Collectives™ on Stack Overflow

Parsing a byte array with VBA using MS Access

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related