How to import a file with lines of different format?

[evgeniosc]

I want to import a text file (fixed column length) with lines of multiple formats.
This file has one header line that has the number of records of each format.
e.g

Header line: 10 11 12
This header line says to us that follow 10 lines of format A, 11 lines of format B and 12 lines of format C

Lines 2-11 have the format A
Lines 12-22 have the format B
Lines 23-32 have the format C

Is there any sample code that imports such a file?

I already though of an algorithm that does this but I think that someone else might already has programmed something like this.

Algorithm
---------
Read the first line and retrieve the number of records of each format
Read the lines 2-11 and put them in a file A.txt
Read the lines 12-22 and put them in a file B.txt
Read the lines 23-32 and put them in a file C.txt
Import the 3 files in seperate tables based on different import specification

 

[JerryKlmns]

evgeniosc

I haven 't read any other solution in TT, to such a problem like yours.

Of course if the length of the line is less than a text field limit of 255 chars, you could import everything in a temp table, read the first imported record, split it with the space char, delete the record, read the X first number of records (SELECT TOP ...), write them to table A, delete them and move to the next set of Y records and so on. Then clear the table.

But I think transforming the original file should be the way to go in any case. Faster and wont bloat the mdb more than needed.

Of course if you find ONE solution to a problem OTHERS do exist.

 

[CautionMP]

evgeniosc,

If your going to build a mechanism to split the original file, why not just import the data instead of writting new files?

Another thought along the same line that JerryKlmns proposed. Can you link the text file, as is, and then setup three different queries in the correct format and filtered to only pull the appropriate format?

BTW, do the three different formats end up in three different tables?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Golom]

Depends on how "different" formats "A", "B" and "C" are.

If they are all variations on a theme, for example, similar records differing only in the field count, then the import to a temp table and using SQL to split them may work. If they are very different, and the files are not too large then

Dim n As Long
Dim Lines() As String
Dim B(2) As String
Dim FSO As New FileSystemObject
Dim tst As TextStream

Lines = Split(FSO.OpenTextFile("myfile.txt").ReadAll, vbCrLf)

For n = 0 To UBound(Lines)
   If n = 0 Then
      [COLOR=black cyan]' Ignore the first line[/color]
   ElseIf n <= 10 Then
      B(0) = B(0) & Line(n) & vbCrLf
   ElseIf n <= 21 Then
      B(1) = B(1) & Line(n) & vbCrLf
   Else
      B(2) = B(2) & Line(n) & vbCrLf
   End If
Next

Set tst = FSO.OpenTextFile("FmtA.txt", ForWriting, True)
tst.Write B(0)
tst.Close

Set tst = FSO.OpenTextFile("FmtB.txt", ForWriting, True)
tst.Write B(1)
tst.Close

Set tst = FSO.OpenTextFile("FmtC.txt", ForWriting, True)
tst.Write B(2)
tst.Close

and then import the three tables created.