I had to do 2 things to make your sample output work with MS Excel 2010
(yeah, it's old but I'm pretty cheap and it still works).
#1 Remove the spaces after the commas.
#2 Double up the quotes in the foobar cell. I have no idea why.
"=""12DE""","=""12DF""","=""12E0""","=""12E1"""
"=""12DE""","=""12DF""","=""12E0""","=""12E1""","=""foo""""bar"""
On 3/24/2021 12:08 PM, Paul Gilmartin wrote:
On Wed, 24 Mar 2021 16:17:10 +0000, Chris Hoelscher wrote:
Wouldn't just transforming the mainframe data to CSV format and downloading
work?
Indeed. Then open the .CSV with Excel/LibreOffice and Save as .XLS.
But the hard part is "just" transforming the mainframe data to CSV,
as other plies in this thread explain.
On Wed, 24 Mar 2021 10:53:05 -0700, Tom Brennan wrote:
"12DE","12DF","12E0","12E1"
The first two are fine, but the next 2 are interpreted as exponential
notation regardless of the quotes. One way around this is to make the
value ="12E0" once it arrives in Excel, but even that is difficult in
CSV - something like this might be required:
"=""12DE""","=""12DF""","=""12E0""","=""12E1"""
I tried the followibg:
524 $ cat awkq
# Doc: Convert each field to .CSV-friendly format.
{ for ( I = 1; I <= NF; I++ ) {
gsub( /\"/, "\"\"", $I ) # Double internal quotes.
sub( /^/, "\"=\"\"", $I ) # Add leading "="".
sub( /$/, "\"\"\",", $I ) } # Add trailing """,.
sub( /,$/, "", $0 ) # remove trailing ,.
print( $0 ) }
525 $
522 $ awk -f awkq | tee foo.csv
Which converted:
12DE 12DF 12E0 12E1
12DE 12DF 12E0 12E1 foo"bar
To:
"=""12DE""", "=""12DF""", "=""12E0""", "=""12E1"""
"=""12DE""", "=""12DF""", "=""12E0""", "=""12E1""", "=""foo""bar"""
Which almost worked, but on the last item, LibreOffice said, "Err:509."
Is it LibreOffice, or is it me?
-- gil
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
