topic Re: Export to TXT/CSV with customized header in App Development

Export to TXT/CSV with customized header

qhe — Sat, 16 Nov 2024 07:09:49 GMT

Hi scripting experts, I have below the table, need to export to csv/txt with a specific header. Any thoughts?

Input:

Customer, Sales

A,100

B,200

C,300

D,400

E,500

Required output in txt/csv:

Total rows: 5
A,100
B,200
C,300
D,400
E,500

Not able to upload a qvf, so I attached the codes:

Table1:

LOAD * INLINE [
Customer, Sales
A,100
B,200
C,300
D,400
E,500
];

Store Table1 into [lib://Data/Table1.txt](txt, delimiter is '|');

Re: Export to TXT/CSV with customized header

albert_guito — Fri, 30 Nov 2018 12:17:36 GMT

Hi,

You can try with this code:

Table1:
LOAD * INLINE [Customer, Sales
A,100
B,200
C,300
D,400
E,500
];

Header: Load 'Total rows: ' & Count(Customer) as numReg Resident Table1; 

let vLabelColumn1 = peek('numReg',0,'Header');
let vLabelColumn2 = ' ';

TableToExport:
LOAD 
	Customer as '$(vLabelColumn1)',
	Sales as '$(vLabelColumn2)'
RESIDENT Table1;

DROP TABLE Table1, Header;

Store TableToExport into [Table1.txt](txt, delimiter is '|');

But the solution isn't perfect because it includes the delimiter in the header 😞

Total rows: 5| 
A|100
B|200
C|300
D|400
E|500

Re: Export to TXT/CSV with customized header

albert_guito — Fri, 30 Nov 2018 12:41:45 GMT

If you just use a little number of columns this would be a perfect solution:

Table1:
LOAD Customer & '|' & Sales as Reg INLINE [Customer, Sales
A,100
B,200
C,300
D,400
E,500
];

Header: Load 'Total rows: ' & Count(Reg) as numReg Resident Table1; 

LET vLabelColumn1 = peek('numReg',0,'Header');

TableToExport: LOAD PurgeChar(Reg,'"')	as '$(vLabelColumn1)' RESIDENT Table1;

DROP TABLE Table1, Header;

Store TableToExport into [Table1.txt](txt);

The result will be:

Total rows: 5
A|100
B|200
C|300
D|400
E|500

Re: Export to TXT/CSV with customized header

qhe — Sat, 01 Dec 2018 07:45:10 GMT

Thanks, Albert! excellent solution!