Text File Import. Need help

General
#1

Hi everybody,
I have to import a text file into two different db tables.
The problem is that the text file I have to import the data from, has uneven rows and columns except a precise pattern from block to block, is followed (fixed length text).
In the end, I am struggling about the strategy to follow in order to choose the right rows to import.
Any suggestions?

The text file shows something like the following pattern:

=======================================================

  1. Orders. 1st Block
    =======================================================

Names | Countries | Products | OrderDates

Name | Country | Product | OrderDate
Name | Country | Product | OrderDate
Name | Country | Product | OrderDate
etc….

  1. Product. 2nd Block
    =======================================================

Origin | Makes | Colors | Cost

Origin | Make | Color | Cost
Origin | Make | Color | Cost
etc……

=======================================================
3) etc….

etc……

thanks for any help.

#2

If each block has a fixed text width, and the text width is known for each, then all you need to do is read the text lines in one by one.
Do a select case on the length of the line

Inside each case, split the text into an array of variants
insert a new row into the appropriate table

pseudocode:

while not endoffile
read a line into the row

split the row into the fields()

select case len(the row)

case 100
insert the fields into table 1
case 150
insert the fields into table 2

case else
msgbox “oops”
end select

wend

OR since all rows have 4 fields, put them all into one landing table , but flag each rows with the ‘type’ in a spare column.
You can then move rows of the wrong type to the second table if you want to when the job is complete.

#3

Jeff, good idea.
It is a very good starting point that need just a little adaptation, as the actual text is slightly different from the above. At some point, I might have to deal with the following issue: a string laying on two different rows, such as in the following example where the word United kingdom is on two different rows.

=======================================================

  1. Orders
    =======================================================

Names | Country | Products | OrderDates

Name | Germany | Product | OrderDate
Name | United | Product | OrderDate
Kingdom
Name | France | Product | OrderDate

#4

where the word United kingdom is on two different rows.<<
What muppet came up with a file format like that?

It is definitely not >>a precise pattern from block to block (fixed length text).<<

#5

Jeff, I agree with you. it’s very weird.
It happens, luckily, very few times, with strings with a total length > than the field they are usually intended for.
Anyway I already gave a try to the code and seems it works. So I just have to deal with the remaining weird stuff. I have already some in mind. though.

#6