Custom headers preparation for Excel file to load into SAP- Azure Synapse / ADF

Dileep K 1 Reputation point
2022-09-28T13:23:42.003+00:00

Hi All,

I am working on one requirement and explaining below.

  1. I get raw file from our third party system and we need to perform some ETL operations and need to prepare exact target file format which accepts by our custom SAP program. 

Source file format:

object no. account No. Vendor description account No. Internal account No.  Cost Centre booking No. description value Date value  value incl. tax value without tax

tax tax code voucher desc.  invoice no. invoice date creditor no. creditor name contra account no. contra account name time of performance from time of performance to

Target Format ( I am looking like below ‌‌

HD Company Doc Type Posting Date Doc Date Currency Ex Rate Reference HeaderText
Ldger

HT 0 YY 21.03.2022 21.03.2022 XYZ
UPLOADFILE MAR UPLOAD FILE

LD \ Posting Key Account Special G/L Indicator Transaction Type Amount (Doc curr) Amount (Loc Curr) Amount (Group Curr) Contract Contract type Tax code Payment terms Baseline/Due Date Pmt Method
LT 1 40/50 ABCD

2200.00 2200.00 2200.00

Note: I will have many line items (LT ) in this diagram shown only one line but HT line always be one.

my questions:

How can we have 2 separate custom headers to populate data into target format ? 

I am able to populate my line item data & line item header but for every file i need to provide custom header & its one line.

please suggest.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,379 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MartinJaffer-MSFT 26,026 Reputation points
    2022-09-29T18:37:38.427+00:00

    Hello and welcome to Microsoft Q&A @Dileep K

    If I understand correctly, you want to add an extra header to your excel, like in below picture.

    246194-image.png

    Out of the box, the normal data like in row 4 and below is done easy. However inserting the rows 1 and 2 is the ask.

    I'm thinking it would be easier to split this into 2 tasks:

    1. First write the excel with just the data header and data as in row 4+
    2. Use a Synapse notbeook to load the excel, and then insert rows at the beginning and write the special headers. Then save the result back to excel.

    While pre-pending the header rows may be possible in a dataframe, I worry about type conflicts. This form of editing is not in native excel.
    Instead I would consider using an excel library made specific for excel.

    Example inserting rows in dataframe
    Example inserting rows with openpyxl

    Please confirm if this is the sort of thing you are looking for, or if I totally misunderstood the ask.

    0 comments