CODEWITHSUNDEEP
azure-data-factory
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Handling delimited files in Azure Data factory

I have got a very large table with around 28 columns and 900k records. I converted it to CSV file (Pipe separated) and then tried to use that file for feeding another table using ADF itself.

When I tried to use that file, it keeps triggering an error saying some column datatype mismatch.

So excavating more into the data I have found few rows having Pipe (|) symbol in their text itself. So at the time coverting it back, the text after the pipe been considered for the next column and thus the error.

So how to handle the conversion into CSV efficiently when there are texts with delimiters in their columns.

Upvotes: 0

Views: 1393

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

Option1: If there is a possibility, I would suggest changing the delimiter to other than pipe(|), as the column value also contains pipe in its text.

Option2: In the CSV dataset, select a Quote character to identify the columns.

Step1: Copying data from table1 to CSV.

Source:

enter image description here

Sink CSV dataset:

enter image description here

Output:

enter image description here

Step2: Loading same CSV data to table2 with a copy activity.

  • CSV output file of Step1.

Source CSV dataset:

enter image description here

Sink dataset:

enter image description here

Output:

enter image description here

Upvotes: 1

Related Questions