Transactional Database 1

[EBOUGHEY]

Hi.

I am receiving a file from a client on an as400. Each master record can have infinite number of transactions in it. Fields 1-42 are the master and then 5 fields for each transaction until you reach a break code that says you've reached the next master record.

Sample client data:

YNNNNN
1
2
3
4
5
6
7
8
9
10
11
12
001
000344
57-000344

Crystallina

Marie
Crystallina Marie
1101 La Mirade Boulevard
Monterrey LA 71124-1101

Monterrey
LA
71124-1101

Crystallina

Marie
Crystallina Marie
1101 La Mirade Boulevard
Monterrey LA 71124-1101

Monterrey
LA
71124-1101
4531
1
4531
7
7
2003
Auto Payment
-4531
1
-4531
8
1
2003
Additional Outlet 8/1..8/31
0
1
0
8
1
2003
Basic 8/1..8/31
2000
1
2000
8
1
2003
Converter and Remote 8/1..8/31
200
1
200
8
1
2003
FCC User Fee 8/1..8/31
5
1
5
8
1
2003
HBO 8/1..8/31
900
1
900
8
1
2003
HBO/Show/Starz discount 8/1..8/31
-500
1
-500
8
1
2003
Showtime 8/1..8/31
900
1
900
8
1
2003
Starz 8/1..8/31
495
1
495
8
1
2003
Franchise Fee
120
1
120



BD Beginning of Second Record
4120
1
4120
**
NNNNNN


What is the best way to import a file of this magnitude? Could you even do it in foxpro?

Thanks

Elena

 

[mkrausnick]

There are at least two ways to do this in VFP, assuming that your input file is a readable ASCII text file.

1) use the FILETOSTR() function to load the file and then parse the resulting string variable.
2) Use low-level file-io (FOPEN, FREAD, etc) to read a fixed number of bytes at a time in a loop.

I'd probably do it the second way because the first way would have limitations on the size of the file you could process, depending on the amount of memory in the computer you use. Also, the file structure lends itself neatly to the second method - read the master section, then in a loop, read the transaction sections, until you read a string with the control character in it that indicates end of record, then back up 1 block to get back to the beginning of the next record and go again.




Mike Krausnick
Dublin, California

 

[baltman]

Elena,

The most efficient way would be to use low level file functions to create two output files, one with customer level information and an account number and one with an account number and transaction level information.

Somthing like this, which creates 2 '#' delimited files:

&&&&&&&&&&&&&&&&&&&&&&&
CLOSE ALL

lnhandle= fopen("test.txt&quot
lnTransFile= fcreate("Output_Transactions.txt&quot
lnCustFile= fcreate("Output_Customer.txt&quot

do while not Feof(lnHandle)
lcString=fgets(lnHandle,2950)


IF LEN(ALLTRIM((CHRTRAN(lcString,"YN","&quot)))#0 or LEN(ALLTRIM(lcString))#6 &&transaction line
lcstring=CustAcct+"#"+lcstring
FOR x= 1 TO 6
lcstring=ALLTRIM(lcstring)+"#"+ALLTRIM(fgets(lnHandle,2950))
ENDFOR
= fput(lnTransFile,lcString)
ENDIF

IF LEN(ALLTRIM((CHRTRAN(lcString,"YN","&quot)))=0 AND LEN(ALLTRIM(lcString))=6 &&new line
FOR x= 1 TO 43
temp_lc_string=ALLTRIM(fgets(lnHandle,2950))
IF SUBSTR(temp_lc_string,3,1)="-"
CustAcct=temp_lc_string
ENDIF
lcstring=ALLTRIM(lcstring)+"#"+temp_lc_string
ENDFOR
= fput(lnCustFile,lcString)
ENDIF

enddo
close all
&&&&&&&&&&&&&&&&&&&&&&&

I don't think that the sample data you provided has enough information for an accurate sample program to be created.

Things to think about:
What (unique and identifiable) line indicates the start/end of cutomer information? Is it a 6 character line of either "N" or "Y"s?

What (unique and identifiable) line indicates the start/end of each transaction set? Is it the description, the $ amount or the transaction codes?

Are empty lines .null. fields?

Is any data 'useless' and ignorable?

Perhaps you'd like to make a larger sample file available for download. I recommend that you dummy the names/address/account numbers.

Brian

 

[ramani]

It appears that you are getting lines 1-42 as (42)master/header record and 43-49 as child records (7).

Since the number of bytes per header is not uniform except that you get a carriage return at the end of each field, i will go FILEOSTR() and ALINES() way of obtaining the data.

cString = FILETOSTR('myTextFile')
i=0
x=ALINES(laArray,cString)

DO WHILE I < x
** Header record
SELECT masterTable
APPEND BLANK
REPLACE field1 WITH laArray(i+1)
REPLACE field2 WITH laArray(i+2)
..
.. till 42 records...
REPLACE field2 WITH laArray(i+42)
i=i+42
IF laArray(i+1) = &quot;New Record indicator&quot;
LOOP
ENDIF
IF i=x
EXIT
ENDIF

** Child records
SELECT childTable
DO WHILE .t.
APPEND BLANK
REPLACE field1 with laArray(i+1), ;
field2 WITH laArray(i+2),
field3 WITH laArray(i+3),
field4 WITH laArray(i+4),
field5 WITH laArray(i+5)
i=i+5
IF laArray(i+1) = &quot;NEW RECORD BREAK MARK&quot;
EXIT
ENDIF
ENDDO
IF i=x
EXIT
ENDIF
ENDDO




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[baltman]

Please note that Mike is incorrect. Even low level file functions will fail on text files >2 GB (but it wouldn't tell you). Files would have to be split into pieces 1st using 'head.exe' or 'tail.exe'.

Brian

 

[EBOUGHEY]

here is a more complete sample for you (3 records). At the end of each master with transactions there are 3 returns and then the characters BD:

I'm working on the code you guys have sent so far...

V3
YNNNNN
1
2
3
4
5
6
7
8
9
10
11
12
001
000344
57-000344

Crystallina

Marie
Crystallina Marie
1101 La Mirade Boulevard
Monterrey LA 71124-1101

Monterrey
LA
71124-1101

Crystallina

Marie
Crystallina Marie
1101 La Mirade Boulevard
Monterrey LA 71124-1101

Monterrey
LA
71124-1101
4531
1
4531
7
7
2003
Auto Payment
-4531
1
-4531
8
1
2003
Additional Outlet 8/1..8/31
0
1
0
8
1
2003
Basic 8/1..8/31
2000
1
2000
8
1
2003
Converter and Remote 8/1..8/31
200
1
200
8
1
2003
FCC User Fee 8/1..8/31
5
1
5
8
1
2003
HBO 8/1..8/31
900
1
900
8
1
2003
HBO/Show/Starz discount 8/1..8/31
-500
1
-500
8
1
2003
Showtime 8/1..8/31
900
1
900
8
1
2003
Starz 8/1..8/31
495
1
495
8
1
2003
Franchise Fee
120
1
120



BD
4120
1
4120
**
NNNNNN
1
2
3
4
5
6
7
8
9
10
11
13
003
000345
57-000345

Hotel

Seville
Hotel Seville
4000 Hotel Circle
Monterrey LA 71124-4000

Monterrey
LA
71124-4000

Hotel

Seville
Hotel Seville
4000 Hotel Circle
Monterrey LA 71124-4000

Monterrey
LA
71124-4000
15000
1
15000
7
2
2003
Payment
-15000
1
-15000
8
1
2003
Commercial Basic $5.00 8/1..8/31
15000
1
15000



BD
15000
1
15000
**
NNNNNN
1
2
3
4
5
6
7
8
14
10
11
13
001
000354
57-000354

Brianna

Stephens
Brianna Stephens
1500 Dusk Way
Monterrey, LA 71124-1500

Monterrey
LA
71124-1500

Brianna

Stephens
Brianna Stephens
1500 Dusk Way
Monterrey LA 71124-1500

Monterrey
LA
71124-1500
5865
1
5865
7
21
2003
Payment
-5865
1
-5865
8
1
2003
Additional Outlet 8/1..8/31
0
1
0
8
1
2003
Basic 8/1..8/31
2000
1
2000
8
1
2003
Converter and Remote 8/1..8/31
400
1
400
8
1
2003
FCC User Fee 8/1..8/31
5
1
5
8
1
2003
Franchise Fee
72
1
72

 

[EBOUGHEY]

Oh, by the way....

The records all need to be in one file. There is a field that I will create with either 'm' or 't' in it to note it as a master or transaction.

The file should have all the master data in the first record and all transaction records right below it. My finished ascii file has to look like this:

M YNNNNN 1 2 3 4 5 6 7 8 9 10 11 12 001 000344 57-000344 Crystallina Marie Crystallina Marie 1101 La Mirade Boulevard Monterrey LA 71124-1101 Monterrey LA 71124-1101 Crystallina Marie Crystallina Marie 1101 La Mirade Boulevard Monterrey LA 71124-1101 Monterrey LA 71124-1101 4531 1 4531 BD 4120 1 4120
T 7 7 2003 Auto Payment -4531 1 -4531
T 8 1 2003 Additional Outlet 8/1..8/31 0 1 0
T 8 1 2003 Basic 8/1..8/31 2000 1 2000
T 8 1 2003 Converter and Remote 8/1..8/31 200 1 200
T 8 1 2003 FCC User Fee 8/1..8/31 5 1 5
T 8 1 2003 HBO 8/1..8/31 900 1 900
T 8 1 2003 HBO/Show/Starz discount 8/1..8/31 -500 1 -500
T 8 1 2003 Showtime 8/1..8/31 900 1 900
T 8 1 2003 Starz 8/1..8/31 495 1 495
T 8 1 2003 Franchise Fee 120 1 120
M NNNNNN 1 2 3 4 5 6 7 8 9 10 11 13 003 000345 57-000345 Hotel Seville Hotel Seville 4000 Hotel Circle Monterrey LA 71124-4000 Monterrey LA 71124-4000 Hotel Seville Hotel Seville 4000 Hotel Circle Monterrey LA 71124-4000 Monterrey LA 71124-4000 15000 1 15000 BD 15000 1 15000
T 7 2 2003 Payment -15000 1 -15000
T 8 1 2003 Commercial Basic $5.00 8/1..8/31 15000 1 15000
M NNNNNN 1 2 3 4 5 6 7 8 9 10 11 13 001 000354 57-000354 Brianna Stephens Brianna Stephens 1500 Dusk Way &quot;Monterrey, LA 71124-1500&quot; Monterrey LA 71124-1500 Brianna Stephens Brianna Stephens 1500 Dusk Way Monterrey LA 71124-1500 Monterrey LA 71124-1500 5865 1 5865 BD 2477 1 2477
T 7 21 2003 Payment -5865 1 -5865
T 8 1 2003 Additional Outlet 8/1..8/31 0 1 0
T 8 1 2003 Basic 8/1..8/31 2000 1 2000
T 8 1 2003 Converter and Remote 8/1..8/31 400 1 400
T 8 1 2003 FCC User Fee 8/1..8/31 5 1 5
T 8 1 2003 Franchise Fee 72 1 72

 

[EBOUGHEY]

Baltman,

Your import is very close....

I'm trying to understand the logic so I can tweak it a bit though.

I changed &quot;YN&quot; to &quot;BD&quot; because that's the beginning of the new record. The transactions are working, but the customer data is coming over as several lines and it is going into the ouput_transaction.txt file (which is really okay because that's where I'm heading with this).

Any suggestions? I also want to add an 'm' or 't' in the first field it exports and take out the #, replace it with tab delimiter.

Thanks so much for your help on this.

 

[baltman]

CLOSE ALL
clear

lnhandle= fopen(&quot;cable.txt&quot &&your sample data
lnMasterFile= fcreate(&quot;Output_Transactions.txt&quot &&my output file

StartofFile_V3=&quot;V3&quot; &&this is unique start of file character?
BDLine=&quot;&quot;
MasterLine=&quot;&quot;
TransLine=&quot;&quot;

do while not Feof(lnHandle)
lcString=alltr(fgets(lnHandle))

if allt(lcstring)==StartofFile_V3
lcString=alltr(fgets(lnHandle))
BDLine=&quot;No BD in Sample File&quot; &&1st record seems to be missing a BD String
endif

do case
case LEN(ALLTRIM(lcString))=0
&&do nothing

CASE (LEN(ALLTRIM((lcString)))=2 and ALLTRIM(lcString)==&quot;BD&quot &&BD String if you want to place out of original order
BDLine=lcString
for x=1 to 3
BDLine=BDLine+&quot; &quot;+ALLTRIM(fgets(lnHandle))
endfor

CASE LEN(ALLTRIM((CHRTRAN(lcString,&quot;YN&quot;,&quot;&quot)))=0 AND LEN(ALLTRIM(lcString))=6 &&Master Lines
FOR x= 1 TO 40
lcstring=lcstring+&quot; &quot;+ALLTRIM(fgets(lnHandle))
ENDFOR
MasterLine=&quot;M &quot;+lcstring

CASE ALLTRIM(lcString)#&quot;**&quot; &&Transaction Lines
TransLine=lcString
for x=1 to 6
TransLine=TransLine+&quot; &quot;+ALLTRIM(fgets(lnHandle))
endfor
TransLine=&quot;T &quot;+TransLine

endcase

if len(alltrim(MasterLine))>0 and len(alltrim(BDLine))>0
= fput(lnMasterFile,MasterLine+&quot; &quot;+BDLine)
MasterLine=&quot;&quot;
BDLine=&quot;&quot;
endif

if len(alltr(TransLine))>0
= fput(lnMasterFile,TransLine)
TransLine=&quot;&quot;
endif

enddo
close all

modi comm Output_Transactions.txt nowait &&show results

 

[EBOUGHEY]

This works really well! You are a genius.

I want to really understand what kind of changes take place in the program to differentiate what is a master and what is a transaction record. I know it has to do with the qualifier in lcstring, but now sure how it works.

At the end of the file, there's statement data that will be a record like the master record with an 'S' at the front of it. I've been working on it this morning but can't figure the logic out. It puts the asterisks in the data file but I don't want it to.

I used the following code from your program for the logic:

CASE (LEN(ALLTRIM((lcString)))=4 and ALLTRIM(lcString)==&quot;****&quot
StateLine=lcString
for x=1 to 25
Stateline=StateLine+&quot; &quot;+ALLTRIM(fgets(lnHandle))
endfor
StateLine=&quot;S &quot;+StateLine

Came up with this:

S **** STATEMENT TOTALS BEGINNING BALANCE 275696 275696 TOTAL PAYMENTS -173751 -173751 TOTAL ADJUSTMENTS -78129 -78129 TOTAL MANUAL INV. 8449 8449 TOTAL TAX 2985 2985 TOTAL BILLED 168180 168180 TOTAL DUE 203430 203430 Total Number of Statements 39

Thanks again so much for your expertise!

Elena

 

[baltman]

If you don't want the &quot;***&quot;, don't make them part of the string:

CASE (LEN(ALLTRIM((lcString)))=4 and ALLTRIM(lcString)==&quot;****&quot
*StateLine=lcString
for x=1 to 25
Stateline=StateLine+&quot; &quot;+ALLTRIM(fgets(lnHandle))
endfor
StateLine=&quot;S &quot;+StateLine

 

[EBOUGHEY]

OOOOH, it finds everything &quot;after&quot; the string is found.

You know, I really appreciate the feedback & programming help. It has saved me hours of research and debugging time.

I don't think I've ever written a post that didn't help me tremendously. This is the best place I've found for foxpro technical help.

Elena