Text delimiter file

Question

Hi,

I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlited in yellow.

I need to combine both the headers and lines using a UNION operation to display the result below.

From the line table, I need to add 3 empty fields to match the number of fields in the header table for the union operation.

The results currently show three commas in the output text file1, but I need the result without these extra commas as seen in results file2.

How can I achieve this

select HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount

from header

union all

select LineLEvel,InvNumber,TAmount,Code,InvoiceDate,PNumber,'','',''

from lines

File1

HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount

LineLevel,InvNumber,TAmount,Code,InvoiceDate,PNumber,,,

H,1,5,8901230,19192,10000008927,102-4490-4330-18,163.00,200.00,

L,817053,163.0000,804,2024-03-14,n/a,,,

H,2,5,8901230,19192,10000008927,102-4527-4408-1,1393.95,5438,

L,6054660,4.7000,804,2024-03-05,n/a,,,

L,6054750,14.2500,804,2024-03-05,n/a,,,

File2

HeaderLevel,ReID,AssitId,UserCode,BatchNumber,SoNumber,RequestNo,Amount,PurchaseAmount

LineLevel,InvNumber,TAmount,Code,InvoiceDate,PNumber

H,1,5,8901230,19192,10000008927,102-4490-4330-18,163.00,200.00,

L,817053,163.0000,804,2024-03-14,n/a

H,2,5,8901230,19192,10000008927,102-4527-4408-1,1393.95,5438,

L,6054660,4.7000,804,2024-03-05,n/a

L,6054750,14.2500,804,2024-03-05,n/a

Answer 1

Hi @Vicki

I need the result without these extra commas as seen in results file2.

The csv file is by designed comma delimited. How about save the result as a txt fille.

Since the header table has no line values, you could merge the last four headers into one header with comma delimited, just like Erland's answer.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Maybe this works:

select HeaderLevel, ReID, AssitId, UserCode, BatchNumber, 
      concat_ws(',', SoNumber, RequestNo, Amount, PurchaseAmount)
from header
union all
select LineLEvel, InvNumber, TAmount, Code, InvoiceDate, PNumber
from lines