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

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:

Code:
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';

Code:
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 . screen.png

Thanks,
Richard
 
Last edited:

TomBascom

Curmudgeon
Code:
/*
 * 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

Well-Known Member
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):

Code:
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 ) ).

 
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:

Code:
@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
 
Top