Question Using SQLEXP to generate CSV - Excel reads data as padded with spaces

[Ryszard Musielak]

Hello,

I am extracting some data into a CSV files using SQLEXP. Goes into a text file (I just rename is as .CSV) and I use the comma as a delimiter. Some columns are fine, Excel understands them, but some are opened in Excel with data padded, so the Excel output looks more padded to the right, even though I try to use the LTRIM function to strip out any blanks. Here is the bare output in the SQLEXP session:

SELECT ltrim(s."supp-name")||',',
    ltrim(s."supp-add",s."supp-code")
FROM pub.supplier s
WHERE s."curr-code" = 'GBP'
and s."supp-code" = 'A10000';

1> 2> 3> 4> concat(ltrim(supp-name),,)                                    ltrim(supp-add,supp-code)                                                                                                                    
------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RXXXXX  Ltd ,                                                  TURNPIKE WAY;HIGH TOWN;RRRRR;PO22 4CL

When I use DBeaver, this is not the case.

Here are the column types:
CREATE TABLE PUB."supplier" (
"supp-code" varchar(20) NOT NULL,
"supp-name" varchar(60),
"supp-add" varchar(208),
"curr-code" varchar(8),


Any suggesitons?

Attached is a sample record I see in Excel .

Thanks,
Richard

 

[Stefan]

sqlexp formats output into columns with headers, which according to Progress KB - Can SQL Explorer (sqlexp) output query results without column names when using the -outfile command line option? , cannot be turned off.

You should however be able to concatenate the entire record as a single column.

 

[Rob Fitzpatrick]

Alternatively, you could write your CSV output routine in ABL.

 

[Ryszard Musielak]

sqlexp formats output into columns with headers, which according to Progress KB - Can SQL Explorer (sqlexp) output query results without column names when using the -outfile command line option? , cannot be turned off.

You should however be able to concatenate the entire record as a single column.

That's cheeky, I'll try that.

Thanks
R.

 

[Ryszard Musielak]

Alternatively, you could write your CSV output routine in ABL.

Yes, I would have to learn the 4GL first, I tried some basics, but I was pressed for time in this case to see what is possible.

R.

 

[TomBascom]

/*
 * SELECT ltrim(s."supp-name")||',',
 *    ltrim(s."supp-add",s."supp-code")
 * FROM pub.supplier s
 * WHERE s."curr-code" = 'GBP'
 * and s."supp-code" = 'A10000';
 */

output to "supplier.csv".

for each supplier no-lock where supplier.curr-code = 'GBP' and supplier.supp-code = 'A10000':
  export supplier.supp-name supplier.supp-add supplier.supp-code.
end.

output close.

 

[Stefan]

To be a csv, you'll want to add a delimiter to the export statement and optionally add a header row (and reinstate your query phrase which I forgot):

def var cfile  as char no-undo.

cfile = session:temp-directory + 'suppliers.csv'.

output to value( cfile ).

put unformatted 'id,name,discount' skip.

for each supplier:

    export delimiter ','
        supplier.SupplierIDNum
        supplier.Name
        supplier.Discount
        .

end.

output close.

// for demo purposes
def var lcfile as longchar no-undo.
copy-lob from file cfile to lcfile.
message string( substring( lcfile, 1, 30000 ) ).

ProgressAblDojo

abldojo.services.progress.com

 

[Ryszard Musielak]

This does look very simple indeed. I did manage to run a simple query in ABL, so I think I can experiment with it.

I did manage to get the SQL working with one column still frustrated by the header, but this biggest challenge was to group by, which demands a TO_CHAR conversion on the SUMMed columns:

@HasFetchLimit False
@TransactionIsolation 0

SELECT to_char(sh."p-code")||','||
       to_char(s."supp-code")||','||
--       '"'||p."p-desc"||p."p-desc2"||'"'||',',
       to_char('"'||p."p-desc"||p."p-desc2"||'"')||',',
       to_char(sum(sh."o-sentqty"))||',' Qty,
       to_char(sum(sh."o-weight"))||',' Weight,
/* this column gets padded  */
       to_char(s."supp-name")||','||
       to_char(s."supp-add")||','||
       to_char(s."supp-tel")||','||
       to_char(s."curr-code")
FROM pub.stathist sh
  INNER JOIN pub.product p ON p."p-code" = sh."p-code"
  INNER JOIN pub.supplier s ON s."supp-code" = sh."supp-code"
WHERE sh."o-date" BETWEEN LAST_DAY(ADD_MONTHS (curdate(),-2))+1
                     AND LAST_DAY(ADD_MONTHS (curdate(),-1))
AND s."curr-code" != 'GBP'
GROUP BY to_char(sh."p-code")||','||
       to_char(s."supp-code")||','||
       to_char('"'||p."p-desc"||p."p-desc2"||'"')||',',
       to_char(s."supp-name")||','||
       to_char(s."supp-add")||','||
       to_char(s."supp-tel")||','||
       to_char(s."curr-code");

This columns just after the second SUM column still gets padded:
to_char(s."supp-name")||','||

And the column where two are concatenated had to be enclosed in double-quotes, so Excel would understand it.

Thanks a million - again.
Richard