Share via

Text delimiter file

Vicki 46 Reputation points
2024-05-06T14:48:13.48+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,947 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,474 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
52 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.5K Reputation points
    2024-05-06T20:52:26.25+00:00

    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
    0 comments
  2. CosmogHong-MSFT 24,026 Reputation points Microsoft Vendor
    2024-05-07T02:18:28.6533333+00:00

    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".

    0 comments