>#DEFINE MAX_LINE_SIZE 8192 >* It is assumed you've already output the CSV file from the table with something like >* COPY TO "Data.txt" DELIMITED >* it is also assumed that variable cHeader contains the text of the header line >* to be output in the final file. >* in the following, the variable hIn and hOut are "handle" used to refer to >* the input and output files respectively. >hIn = FOPEN("Data.txt") && open file that contains the data >hOut = FCREATE("Final.txt") && open output file >= FPUTS(hOut, cHeader) && write header line >DO WHILE NOT FEOF(hIn) > cBuf = FGETS(hIn, MAX_LINE_SIZE) && read one line of data > = FPUTS(hOut, cBuf) && write one line of data to result >ENDDO >= FCLOSE(hIn) >= FCLOSE(hOut) >>
>>* Generate .csv file, send to principle >>* The result is like this: >>* INVOICE,TRANSACTION_TYPE,REPLACEMENT,... >>* 100433,sales,no,... >>* 100434,sales,no,... >> >>* My steps: >>* 1) create a cursor (tax_inv) >>* 2) insert the header >>* 3) insert the detail, from table inv >>* 4) export to another cursor (tax_inv1) as one string >>* 5) copy to .csv >> >>* The error is at step 4, error message: String is too long to fit. >>* The .csv must have header, but the header is too long >>* Is it possible to generate .csv the other way? >> >>* 1) >>CREATE CURSOR tax_inv (col1 c(30), col2 c(30), col3 c(30), col4 c(30), col5 c(30),col6 c(30), col7 c(30), col8 c(30), col9 c(30), col10 c(30),col11 c(30), col12 c(30), col13 c(30), col14 c(30), col15 c(30),col16 c(30), col17 c(30), col18 c(30), col19 c(30),col20 c(30)) >> >>* 2) >>INSERT INTO tax_inv (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20) VALUES; ('INVOICE','TRANSACTION_TYPE','REPLACEMENT_INVOICE','INVOICE_NUMBER','TAX_PERIOD','TAX_YEAR','DATE','TAX_ID','CUSTOMER_NAME','ADDRESS','INVOICE_AMOUNT','TAX_AMOUNT','AMOUNT','REMARK','DOWN_PAYMENT','DOWN_PAYMENT_TAX','DOWN_PAYMENT_TAX','DOWN_PAYMENT_LUXURY_TAX','CUSTOMER_REFERENCE','ADDITIONAL_REMARK') >> >>* 3) >>SELECT inv >>SCAN >> INSERT INTO tax_inv (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20); >> VALUES (inv.col1,inv.col2,inv.col3,inv.col4,inv.col5,inv.col6,inv.col7,inv.col8,inv.col9,inv.col10,inv.col11,inv.col12,inv.col13,inv.col14,inv.col15,inv.col16,inv.col17,inv.col18,inv.col19,inv.col20) >>ENDSCAN >> >>* 4) >>SELECT allt(col1)+','+allt(col2)+','+allt(col3)+','+allt(col4)+','+allt(col5)+','+allt(col6)+','+allt(col7)+','+allt(col8)+','+allt(col9)+','+alltr(col10)+','+allt(col11)+','+allt(col12)+','+allt(col13)+','+allt(col14)+','+allt(col15)+','+allt(col16)+','+allt(col17)+','+allt(col18)+','+allt(col19)+','+allt(col20) as result1 FROM tax_inv INTO CURSOR tax_inv1 >> >>* 5) >>SELECT tax_inv1 >>COPY TO 'tax_inv.csv' delimited with "" with character ',' >>>>