【Question】

I've come up with a bit of code that extracts certain values in a text file using VBA. I need it to loop as many times as a sequence of characters'INFORMATION DE BASE ET DONNÉES DE CALCUL'appears in the text file. Here's my code so far:

Example of text file:

INFORMATION DE BASE ET DONNÉES DE CALCUL

 

Nom de l'individu ................. Louis Boutel

Sexe .............................. Homme

Numéro d'assurance sociale ....... 323-423-123

No employé ........................ 14023

Date de naissance ................. 1969-03-22

Date d'emploi ..................... 1998-09-28

 

INFORMATION DE BASE ET DONNÉES DE CALCUL

 

Nom de l'individu ................. Morin laprise

Sexe .............................. Homme

Numéro d'assurance sociale ....... 123-012-012

No employé ........................ 14023

Date de naissance ................. 1959-06-14

Date d'emploi ..................... 1996-10-22

 

I expect to have in Excel:

INFORMATION DE BASE ET DONNÉES DE CALCUL

Date d'emploi 1998-09-28

INFORMATION DE BASE ET DONNÉES DE CALCUL

Date d'emploi 1996-10-22

 

Code:

Sub test()

Dim myFile As String, text As String, textline As String, DDC As Integer, i As Integer, sArray(4) As String

 

myFile = "C:\Users\mark\Desktop\C0010DET.txt"

Open myFile For Input As #1

Do Until EOF(1)

Line Input #1, textline

text = text & textline

Loop

Close #1

 

 

 i = 1

 

Dim vItm As Variant

Dim aStrings(1 To 2) As String

 

aStrings(1) = "INFORMATION DE BASE ET DONNÉES DE CALCUL": aStrings(2) = "INFORMATION DE BASE ET DONNÉES DE CALCUL"

 

For Each vItm In aStrings

DDC = InStr(text, "Date d'emploi")

Cells(i + 1, 1).Value = Mid(text, DDC, 14)

Cells(i + 1, 2).Value = Mid(text, DDC + 36, 10)

Next vItm

 

End Sub

Do you know how can I run this sequence using a loop so I can retrieve all information needed?

 

【Answer】

Group the text file by the repeatedly-appeared characters and each group makes a record; then union the 1^st members and the 8^th members from every group. The algorithm involves grouping operation and order-based operation. It’s difficult to code it in VBA. Without further requirements, you can manipulate it in SPL (Structured Process Language) with simple and easy to understand code:

	A
1	=file("d:\\data.txt").import@si()
2	=A1.group@i(left(~,11)=="INFORMATION")
3	=A2.conj(~(1)|~(8))
4	=file("d:\\result.xlsx").xlsexport(A3)

A1: Read in data.txt.

A2: Put data from a sequence of characters starting with "INFORMATION" in same group until next same characters appear.

A3: Union the 1^st members and the 8^th members of every group.

A4: Export the final result to a specified Excel file.