question

Jzd-5648 avatar image
0 Votes"
Jzd-5648 asked HimanshuSinha-MSFT answered

Excel file with multiple row of column headers and blank columns

Hi,

I have an Excel file as source in Data Factory which has multiple rows for column headers and blank columns as separators. May I know what would be the best approach to transform this dataset into a single dataset?

114889-image.png

My idea is to make it like:

Header 1 Subhead 1
Header 1 Subhead 2
Header 2 Subhead 3
Header 2 Subhead 4

But I don't know how to realize it :(

Also I have blank columns which I delete manually in Select function. Is there any way I can select columns in the Dataset options?

azure-data-factory
image.png (25.5 KiB)
· 1
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.

If you found your own solution, please share here with the community.

0 Votes 0 ·

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered

Hello @,
Thanks for the ask and using the Microsoft Q&A platform .

I did tried to work with the data which you shared . The blank column which basically acts like a boundry between do different dataset , shows up as a blank column with no records but a header . See the snapshot below . I was thinking if we can use some functions to split this , but I dont think we have any . The only option which i see is to use a SELECT transformation and select the first 4 columns .

115474-image.png

In excel the columns like header1 is merged but when In dataflow it appears to be like

115522-image.png

This is aklso not helping us much here .

As is I dont think we can achieve the goal here , unless you make some modification on the Excel side .

LThanks
HImanshu






Please do let me know how it goes .
Thanks
Himanshu



image.png (33.4 KiB)
image.png (19.5 KiB)
· 1
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.

@Jzd-5648, I agree with Himanshu.

What you have shared are effectively two disjunct sets of data.

0 Votes 0 ·