Importing Ascii or Tab file into Filemaker

[Canucks]

I am trying to import Ascii or Tab files into FM and I've found a way of doing it but I need all of you guys help to simplify my calculation because the calculation eats up 100% CPU usage as it gets deeper in breaking down the tab blocks.

I have a text field which i named Souce Data and I've imported a line string of Ascii/tab that looks like this.

S0128|2014816830004|HOME HARDWARE - TORONTO|||||2014817930001|ROB BLAKE|Z602W05584|20060221|SA......

Then I create a series of field that contain calculation.

My first field (f1)Calculation looks like this

Left(Source Data;Position(Source Data;"|";1;1)-1)

Second field (f2) calculation

Middle(Source Data;Length(f1)+2;(Length(Left(Source Data;Position(Source Data;"|";0;2)-2))-Length(f1)))

Third field (f3)Calculation

Middle(Source Data;(Length(f1)+Length(f2)+3);(Length(Left(Source Data;Position(Source Data;"|";1;3)-1))-(Length(f1)+Length(f2)+2)))

Fourth (f4)Field

Middle(Source Data;(Length(f1)+Length(f2)+Length(f3)+4);(Length(Left(Source Data;Position(Source Data;"|";1;4)-1))-(Length(f1)+Length(f2)+Length(f3)+3)))

And so on..Everything gets imported to the right field but it gets slower as the calculation gets deeper and at a point of 14 field my computer stop responding. It would be nice if someone out there can help me summarize my calculation without eating so much of the processing power. I know this kind of work can be done using excel to filter before bringing into FM, but there's got to be a easier solution to bring into to FM by it's own filtering.

Edited March 3, 2006 by Guest

[Raybaudi]

Hi

It is easy to understand that the "|" simbol separates fields of a record, but where is the record separator ?

Can we assume that the record separator is "¶" ?

If so, i think that the best way to import that file is to make some parse to the text before with Word, or within FM itself.

You can change the "|" to commas with Word and than open directy the text file with FM (that will make all the next job)

[Canucks]

'It is easy to understand that the "|" simbol separates fields of a record, but where is the record separator ?

Can we assume that the record separator is "¶" ?'

In windows format ascii separator is |. The problem is not the separator. The problem is the logic in the calculation gets deeper where the length of each field gets added into the calculation. I know there's a mathematic term for adding up these series of length.

[Raybaudi]

Ok

I think that I haven't explained me correctly...

1) your first field contains: S0128; what is the contents of that field in the next record ? (can you post two records ?)

2) how many separators do you see in a row ?

[Canucks]

1) your first field contains: S0128; what is the contents of that field in the next record ? (can you post two records ?)

I will attach a file

2) how many separators do you see in a row ?

There are about 20+ separators according from the Excel Sheet.

Like I said in my earlier post. The data are separated to the right fields. The matter is the calculation. I don't have a zip program so just rename the .zip file to .fp7

Test.zip

[comment]

> I don't have a zip program so just rename the .zip file to .fp7

A really bad idea... In any case, your file seems to be empty.

IIUC, you could try this: define a calculation field =

Let ( [

n = 1 ;

text = "|" & yourSourceField ;

start = Position ( text ; "|" ; 1 ; n ) + 1 ;

end = Position ( text ; "|" ; 1 ; n + 1 )

] ;

Middle ( text ; start ; end - start )

)

Now duplicate the calc field as many times as required and change n to 2, 3, 4, etc.

Doing this at the source would be much more efficient, IMHO.

[Lee Smith]

Doing this at the source would be much more efficient, IMHO.

I agree with, aaaaa Comment.

Lee

[sbg2]

Why not replace the Pipes with Returns and use the MiddleValues function?

Pipe_to_Return = Substitute(Source Data;"|";"¶")

f01 = Substitute(MiddleValues(Pipe_to_Return;1;1);"¶";"")

f02 = Substitute(MiddleValues(Pipe_to_Return;2;1);"¶";"")

f03 = Substitute(MiddleValues(Pipe_to_Return;3;1);"¶";"")

............

f20 = Substitute(MiddleValues(Pipe_to_Return;20;1);"¶";"")

Edited March 13, 2006 by Guest

[comment]

That's a good idea (provided there are no carriage returns in the initial string).

[sbg2]

True, I was going under the assumption that any Carriage Returns would indicate a new record and therefore each line would be imported into its own record.