question

RohitKulkarni-6062 avatar image
0 Votes"
RohitKulkarni-6062 asked MartinJaffer-MSFT commented

Tab per table

Hello Team,

I have one csv file. Under that there are 5 tabs. So I need to transfer the data from each tab to each different tables in the Target Table

ex :

Tab1: TableA
Tab2: TableB
Tab3: TableC
Tab4: TableD
Tab5: TableE

How this is possible in ADF.Please advise me and if you have any supporting link. Please provide me.


Regards
RK

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @RohitKulkarni-6062 and welcome back to Microsoft Q&A.

Are you sure you meant CSV, and not excel? Excel has different tabs/pages.
There is also the whitespace character 'tab', which is used for indentation. That can be used in delimited text, but is horizontal.

0 Votes 0 ·

It's in xlxs.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @RohitKulkarni-6062 .

The Copy activity only does 1 excel tab/page at a time. To re-use the same dataset for multiple pages, we need to parameterize the dataset. This is good news because on the sink side, the copy activity only does one table at a time anyway.
So, the solution, is to make a (ForEach) loop over the associations of tabNumber : tableName. We will pass the tabNumber to the source Dataset, and the tableName to the sink Dataset.

Below is how I parameterize the source excel Dataset.

141482-image.png



image.png (92.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Team,

I am not able to see this option in my screen. Please refer to the screenshot :

142858-image.png


0 Votes 0 ·
image.png (57.5 KiB)

What you are showing is for delimited text, not for excel.

While the Excel desktop application can read csv and xlsx, these are not the same. csv is considered delimited text, while xlsx is a much more complicated data form.



0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

@RohitKulkarni-6062

The correlation of tab/page to table can be done either in a parameter, or a lookup activity pointed at a file like your example. In both cases we want to iterate over the correlation.

In below example, I encode the correlation in a parameter called "correlation" of type array

 [{"page":1,"table":"foo"},{"page":2,"table":"bar"}]

In the ForEach Items I use

 @pipeline().parameters.correlation

In the Copy activity source, I reference the tab/page

 @item().page

141844-image.png

In the Copy activity sink I reference the table name


141779-image.png

 @item().table




image.png (85.2 KiB)
image.png (72.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.