Importing Text to my database

[TKnTexas]

I have run into a problem with what I thought would be a simple task. We have just upgraded to a new version of our accounting software (DOS to Windows). I miss my DOS app.

BACKGROUND:

I need to recreate some things that we could do with our old-version. We use reports that have been spooled to a text file (no delimitations inserted) to do other things with the data. We take the check registers to create the positive pay file that is sent to BoA. We use the AP Edit listing to validate data going in to history.

In the DOS version it was really easy to view the printing report and visualize the datafields: vendor number, name, invoice date, invoice number and invoice amount.

The problem is with the Win-version the re-formatted all of the reports... horribly. They have left justified amount instead of center on the decimal. Additionally, the negative sign for amounts trails instead of leads.

I have brought the files into Filemaker 10.03Advanced. I have successfully parsed all data except for the invoice amounts. The problem is the trailing negative sign. FMP does not see it. Somewhere I read it uses the dash as a word delimiter.

As an example I know the invoice amount starts in position 38 of the row. The amount will not exceed 11 characters including the decimal.

My formula for the invoice amount is:

middle (import_data ; 38 ; 11). I have tried wrapping Trim around it to get rid of rear-padded spaces. I have tried wrapping GetAsNumber around it. I have the calculation set to show result as a number.

I have 3 credit memos that are for 999.99- (using 9 as place holders). So the negative sign is definitely in the 11 character range. But the numbers all show repeatedly as positive number.

I am at a loss of what to do. If I can get rid of this obstacle I can do something with four other AP reports. It will totally impress my boss. It will possibly sell her on using Filemaker Pro within the department instead of Excel worksheets.

[comment]

Try =

Let ( [

t = Trim ( Middle ( import_data ; 38 ; 11 ) ) ;

n = GetAsNumber ( t )

] ;

Case ( Right ( t ; 1 ) = "-" ; -n ; n )

)

[TKnTexas]

Jumping up and down. . .

YAYYY It worked.

One question, can you put it into English what it is doing?

I will be using this alllll over the place.

Thank you thank you thank you

[comment]

1. Extract the 11 characters reserved for the value;

2. Use Trim() to remove the padding spaces;

3. Get the numeric value of the resulting text (since the minus sign is at the end, you actually get the absolute value; I used GetAsNumber(), but Abs() would work too);

4. If the last character of the text is a minus sign, return negative number, otherwise positive.

[TKnTexas]

Thanks a bunch. This has opened a ton of tasks. My boss returns from vacation in a week. I hope I can really impress her with this.