export fixed

[Steve Wood]

I don't see how I can export a table in fixed format, as in something like:

..
export.field1(1,20) = "first_name"
export.field2(21,20) = "last_name"
export.field3(41,1) = "gender"
..

Where the values in parens would be (offset, number of characters), and the output would be (sans the dots):

Steve...............Wood................M
Nikoo................Arasteh.............F

[Stan Mathews]

I don't see how I can export a table in fixed format, as in something like:

..
export.field1(1,20) = "first_name"
export.field2(21,20) = "last_name"
export.field3(41,1) = "gender"
..

Where the values in parens would be (offset, number of characters), and the output would be (sans the dots):

Steve...............Wood................M
Nikoo................Arasteh.............F

An export of character fields to text/ascii will give you that, given the defined width of the fields are as specified (20, 20, 1). I don't think you can specify that the export not be delimited in some manner but careful specification of that delimiter followed by a search and replace should do the trick.

Numeric fields will follow the same general rule but appear right justified rather than left as you indicate you want.

[Steve Wood]

Thanks, but that's not going to satisfy the requirements. I have to export to SAP! For example, the export can be 2000 chars wide, with literal and field values, etc. I also have to convert decimal values to fixed, such as 100.99 to 00010099, do case conversion (Steve to STEVE), pad numeric strings, strip non-numerics from strings (100ABC200 to 100200), etc. I may also have a field that is 20 chars wide, but has to be exported as 100 chars wide, flush left.

[Lawrence Fox]

Steve was asking the question as a followup to a conversation we had earlier today (thanks again for the input, Steve!).

Unfortunately, many large mainframe systems don't know about delimited files and want fixed record length with the data in fixed positions. For example, my client needs to send employee data to an insurance company, so I have a specification in my hands that looks like this:

EmployeeID #, Field Position 1-10
Filler, Position 11
Control Number, Pos 12-17
etc, etc.

and while they've alloted 10 characters for the EmployeeID, we're only using nine. With other products that I've used, you just "paint" the layout (e.g., to position all the fields) and tell the program to create ASCII files and badda bing. Steve and I were kicking around "What's the best way for Alpha to do this?"

Often the data needs some transformations before it's outputted as well. Some want dates as DDMMYYYY, some want YYYYDDMM (with no "/") and so on.

[Steve Wood]

Both are true, I also need to know for current projects. Here is a real example from DE that I have to duplicate in Alpha with fixed format output, field manipulation and a variable output filename. Its pretty self explanatory. I know I can't use the <tbl>.export() function for this. Its complicated by the last line where, after kicking out one line for each record, it posts a summary as the last line in the ascii file.

define "tAmountTot" number . 
define "tAmountT"    text 12 .
define "tAmountTotT" text 12 . 
define global "gTransDate" text 6 . 
define global "gMonthText" text 9 .
define "tDesc" text 29 . 
tDesc := "TELEPHONE CHARGES" .
tDesc := jointext(jointext(tDesc,"  "),gMonthText) . -- must be 29 total

for xcSCexportToGLsum ;

-- AMNT DEFINITION
tAmountT    := blank .
tAmountT    := NumToText(abs(Amount),"000000000.00") .
tAmountT    := StrNumbersOnly(tAmountT) .
--tAmountT    := jointext(tAmountT , if (Amount < 0 ,"-", "+" )) . 

tAmountTot  := tAmountTot + Amount .
tAmountTotT := NumToText(abs(tAmountTot),"000000000.00") .
tAmountTotT := StrNumbersOnly(tAmountTotT) .
--tAmountTotT := jointext(tAmountTotT,"-") .

list records

GLNumber in order ; 
tAmountT ; 
gTransDate ;
tAmountTotT ;
tDesc . 
end  .

export to jointext(any CustomerSetup FMSdir , "\DATA\XFMSBILL.CSV") .
.items
JE@f[1,1,15]@f[1,2,11]00000000000@f[1,5,29]R000033@f[1,3,6]
.end
JE10349055500000000000000000@f[1,4,11]@f[1,5,29]R000033@f[1,3,6]

[Tom Cone Jr]

Steve, for this kind of situation I'd look at using <file>.write_line() to create the "export" file one line at a time. You'd preprocess the field values from your source table, storing the results in character type variables. Each would be padded using the space() function to the desired "field" width. Then they'd be concatenated and written to the target file. Loop through all your records and badda bing you're done.

-- tom

[Stan Mathews]

Thanks, but that's not going to satisfy the requirements. I have to export to SAP! For example, the export can be 2000 chars wide, with literal and field values, etc. I also have to convert decimal values to fixed, such as 100.99 to 00010099, do case conversion (Steve to STEVE), pad numeric strings, strip non-numerics from strings (100ABC200 to 100200), etc. I may also have a field that is 20 chars wide, but has to be exported as 100 chars wide, flush left.

If Tom's solution involves more coding than you want, you could create a temporary table with the number of character fields you want to hold the total required length. IOW for 2000 chaacters you could use seven 255 wide character fields and one 215 wide. Write an append operation that adds records from your "real" table to the temp performing the necessary transformations. Export from the temp table and strip out the commas.

Not as clean perhaps but involves no xbasic if you manually remove commas, a little if you code that..

[Lawrence Fox]

One other alternative that I've seen suggested elsewhere (not for Alpha)--
has anyone tried writing to a text driver and saving to disk? Would this not be the simplest approach?

[Stan Mathews]

Another thought

If Tom's solution involves more coding than you want, you could create a temporary table with the number of character fields you want to hold the total required length. IOW for 2000 chaacters you could use seven 255 wide character fields and one 215 wide. Write an append operation that adds records from your "real" table to the temp performing the necessary transformations. Export from the temp table and strip out the commas.

Not as clean perhaps but involves no xbasic if you manually remove commas, a little if you code that..

If you create a temporary table with the same number of fields as your original table but make all of the fields character of the width required by the target recipient and append the records to the temp table making the pad and other conversions needed during the append then this

exp_file = ui_get_file("Table to be exported","DBF (*.dbf)","","X")
IF exp_file = ""
	end
END IF
wrt_file = ui_get_file("File to be created","TXT (*.txt)","","N")
IF wrt_file = ""
	end
END IF
dim tbl as P
tbl = table.open(exp_file)
fil = file.create(wrt_file,FILE_RW_EXCLUSIVE)
number_of_fields = tbl.fields_get()
dim fld as P
tbl.fetch_first()
ln = ""
WHILE .NOT. tbl.fetch_eof()
	FOR i = 1 TO number_of_fields
		fld = tbl.field_get(i)
		ln = ln + fld.value_get()
	NEXT i
	fil.write_line(ln)
	ln = ""
	tbl.fetch_next()
	
END WHILE

fil.flush()
fil.close()
tbl.close()

will export all of the fields from each record in the chosen temp table without interrupting delimiters.

[Classick]

Re: export fixed

Steve,

have you successfully exported to SAP? i was under the impression that alpha couldnt do this.

[Steve Wood]

Re: export fixed

My previous example was not from Alpha, its from DataEase. Ihave not tried to do a fixed length export from Alpha yet. But that (in the time I had to do so) is what SAP expected. I never followed up to try. My request was actually for a newbie friend that was needing an export in that format.

So the question is, how to do a fixed length export from Alpha.

[KeithW]

Re: export fixed

Greetings,

I have not done this in A5, but in other RADs I have done things like creating a string (or character blob) with something in the order of ...

dim mystring as C

mystring = replicate(" ",250)
mystring = mystring+mystring+mystring[+mystring to desired length]

mystring = stuff(mystring,1,20,"first_name")
mystring = stuff(mystring,21,20,"last_name")
...

write out mystring...
be sure to blank out before next record or...
always account for the total length of the field with blank padding...

hope this helps...

Regards,
Keith

[Bill Parker]

Re: export fixed

I must be overlooking something simple and fundamental in the requirements.

Alpha's table ascii export operation does fixed length export. If you copy the data to a temporary table with field widths defined as needed in the export, and do an update for data conversions, then the export operation should give what you want. There may be extra fields in the table that are used as the source for the update operation but not actually exported.

Bill.