Very Confusing & HUGE Challenege..need help.

[denileigh]

One of our key customers (GE) has demanded that we begin to submit our estimates in their transmission 160 character format.

Basically it it a txt file that looks like this all on one line.
1ABCDWXYZ0502EST TEST123456T050208654321C 00010851803098518031 000000E 0000000000000D CAR SHOPPED AS CLEAN-PROCEED WITH

Only a couple of those characters are in my database and will change. Other than that items will continously be the same. The items that are optional and we don't have not only have to be blank but have to have spaces there instead...not 0's.

How do I set up a query to kick all of this out? I tried something like:

GEString:2UPRR YYMM00&""&WorkOrderID yada, yada, yada

but it didn't work....any ideas? I also need the YYMM to be the current year/month and I have to pull the decimals out of the estimated billing costs.

Is there a better way to do this? As you can imagine...typing 5+ pages of this for each job would be a NIGHTMARE!

 

[ScottGem]

One of our key customers (GE) has demanded that we begin to submit our estimates in their transmission 160 character format.

Basically it it a txt file that looks like this all on one line.
1ABCDWXYZ0502EST TEST123456T050208654321C 00010851803098518031 000000E 0000000000000D CAR SHOPPED AS CLEAN-PROCEED WITH

Only a couple of those characters are in my database and will change. Other than that items will continously be the same. The items that are optional and we don't have not only have to be blank but have to have spaces there instead...not 0's.

How do I set up a query to kick all of this out? I tried something like:

GEString:2UPRR YYMM00&""&WorkOrderID yada, yada, yada

but it didn't work....any ideas? I also need the YYMM to be the current year/month and I have to pull the decimals out of the estimated billing costs.

Is there a better way to do this? As you can imagine...typing 5+ pages of this for each job would be a NIGHTMARE!

I would create this piecemeal instead of trying to get the whole string at once. You can then use a fixed width format in your export.

for example:

Field1: "2UPRR"
Field2: Format (Date(),"YYMM") & "00"
Field3: Format(Int([BillingCost]),"0000000") & Format(([BillingCost]-Int(BillingCost])*100,"00")

 

[denileigh]

That helps ALOT but can I reserve the right to ask more questions? *grin*

 

[ScottGem]

That's what we are here for.

 

[denileigh]

K - I think I got it but the currency is outputting 000860 instead of 086000 and I tried to copy/paste the other code and it isn't working.

 

[denileigh]

Second problem...the service descrption MUST BE 40 characters so if the service was cleaning how do I tell it to count the characters in cleaning (8) and then add 32 spaces before the next field?

 

[Len Boorman]

This is a really dog of a job but I have done it before.

Basically it is very tedious but first you need a query that gets the bits of data you actually need.
Then you use the LEN function to see how long that are.
From this you can work out how many lead characters you need by using say 10-Len function.

A table with pad characters like
Needed Chars
1 0
2 00
etc

You can then link you query to this table and extract the padding
You then add the padding to the data

You then add the static characters
You then string it all together in a report
Use tools to Publish with MS Word
Save as a Text file

Job done

Did this pattern to extract data and create a thing called a T File. Have used similar to extract tags and wrap it in SGML tags.

Do small bits at a time
Tedious to set up but verypossible

Len

 

[ScottGem]

Second problem...the service descrption MUST BE 40 characters so if the service was cleaning how do I tell it to count the characters in cleaning (8) and then add 32 spaces before the next field?

Space(40-Len([string])) & [String]

will pad with leading spaces, reverse the order to add trailing spaces. You can also handle this in the export specifications. When you export to a fixed width file you can specify how many characters each field will take up, Sop you can set the field for 40 characters. I suggest you do a manual export and use the Advanced button to see what I mean.

 

[denileigh]

Thanks, I will play with that. Any ideas on the currency issue?

 

[Len Boorman]

Space(40-Len([string])) & [String]

Wish I had known that a while ago

Would

0(40-Len([string])) & [String] pad to 40 chars with zero's as the pad character

Re Currency, Multiply by 100 first ?. or have I missed something

L

 

[denileigh]

I doubt it...I am probably the one who missed it...still green behind the ears here. So...how would you format the currency to make it multiply and output like 000000?

 

[denileigh]

P.S. It keeps kicking the string above back at me with errors.

 

[Len Boorman]

I doubt it...I am probably the one who missed it...still green behind the ears here. So...how would you format the currency to make it multiply and output like 000000?

something like

Space(40-Len([String])) & [String]*100

Didn't multiply first String cos assumed you had a currency format i.e £860.00. If you have pure integer like 860 then I would try

Space(40-Len([String]*100)) & [String]*100

It's a thought anyway

Knowledge is bliss....only Learning hurts

Len

 

[ScottGem]

If you need to zero fill a number you could use a format:

=Format(INT(value*100)."000000")

If value were 860.00 then the result would be 086000