Hi @ADF_Coder
Thanks for the question and using MS Q&A platform.To remove extra spaces from a file during copy to a database in ADF, leverage a Stored Procedure Activity. This procedure trims spaces from designated columns after the copy operation. Below are the steps:
Step 1: Create a Staging Table, this table will temporarily hold the data copied from your file. Example SQL for Creating a Staging Table:
CREATE TABLE StagingTable (
Column1 VARCHAR(255),
Column2 VARCHAR(255),
Column3 VARCHAR(255)
);
Step 2: Create the Final Table, this table will hold the cleaned data after trimming spaces. Example SQL for Creating the Final Table:
CREATE TABLE FinalTable (
Column1 VARCHAR(255),
Column2 VARCHAR(255),
Column3 VARCHAR(255)
);
Step 3: Create a Stored Procedure, this stored procedure will trim spaces from the columns in the staging table and insert the cleaned data into the final table.
Example SQL for Creating the Stored Procedure:
CREATE PROCEDURE TrimColumns
AS
BEGIN
-- Using LTRIM and RTRIM for leading and trailing spaces
INSERT INTO FinalTable (`Column1`, `Column2`, `Column3`)
SELECT TRIM(Column1) AS trimmed_column1, TRIM(Column2) AS trimmed_column2, TRIM(Column3) AS trimmed_column3
FROM StagingTable;
-- Optionally, clean up the staging table if needed
DELETE FROM StagingTable;
END;
For additional info, please refer: https://video2.skills-academy.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver16
Step 4: Configure ADF Pipeline using copy data activity.
- Source - Configure your source dataset to point to the file you want to copy data from.
- Sink - Configure your sink dataset to point to the StagingTable in your database.
Add a Stored Procedure Activity Drag a "Stored Procedure" activity onto the pipeline canvas and connect it to the "Copy Data" activity.
- Linked Service: Choose the linked service that connects to your SQL Database.
- Stored Procedure Name: Select the stored procedure TrimColumns that you created earlier.
Connect the Activities Ensure the Copy Data activity is connected to the Stored Procedure activity. This ensures that once the data is copied to the staging table, the stored procedure is executed to clean the data.
Example Pipeline Flow:
- Copy Data Activity: Copies data from the file to the StagingTable.
- Stored Procedure Activity: Executes the TrimColumns stored procedure to trim spaces and move data to the FinalTable.
Step 5: Validate and Run the Pipeline.
I hope this information helps, please do let us know if you have any further queries.