Unable to skip a line from Excel while doing copy activity in azure data factory

Question

I have a excel Column as a source in Copy Activity. I need to map the columns from source to sink (MS SQL Server).

I have one constraint, whenever I have a blank row in the excel source I should Stop the copy Activity.

I want to copy all the column data before the blank row, and ignore what ever after the blank row.

For example if I have this dataset:

A	B
ABC	DEF
HIJ	KLM

NOP	QRS

Then the first two rows should be imported and the last two rows should not be imported.

What should I do?

Ed Elliott · Accepted Answer · 2022-09-08 16:40:52Z

1

In an ADF pipeline there are limited options for working with the contents of datasets (you can add cols, remove cols, etc) but modifying the rows themselves is hard. You can do it with dataflows but as your sink is mssqlserver then you can use row_number() to find any empty rows and keep the rows up to the blank row.

The process is:

Create a staging table which is the same as the target table but includes a row column that is defined row INT IDENTITY(1,1) NOT NULL
Truncate the staging table
Write the data to a staging table
From the staging table write to the main table using the insert query:

SELECT * FROM staging_table 
  WHERE rowid < 
    (SELECT 
       COALESCE(MIN(rowid), (SELECT MAX(rowid)+1 FROM staging_table)) FROM staging_table WHERE a IS NULL)

edited Sep 8, 2022 at 16:40

answered Sep 7, 2022 at 16:24

Ed Elliott

6,88420 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Being Real Over a year ago

rownumber is not fixed ,so how cani get the rownumber(means everytime i am getting diffrent data as input excel,also I cannot use dataflows since i our organisation not allows use azure ir

Being Real Over a year ago

Can u suggest someother ways

Being Real Over a year ago

I didnt used azure functions till now

Ed Elliott Over a year ago

what is your sink (destination)? is it a database? what type of database? @Being-Real

Ed Elliott Over a year ago

oh cool - then create a staging table, truncate it before you insert into it - include a row column row IDENTITY(1,1) NOT NULL INT - then insert into that, then insert into real_table select columns from staging_table where row < (select row from staging_table where col_ is null)

|

Collectives™ on Stack Overflow

Unable to skip a line from Excel while doing copy activity in azure data factory

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related