delimiter issue during migration

Question

delimiter issue during migration

Rakesh Kumar 20

Data is transferred to another column due to our selection of a comma "," as the delimiter when migrating data from the database to the data lake. For instance, in the SQL database, "Da-PJ 'Relax' 19,99" is present in one column.

Consequently, in the CSV file, "Da-PJ 'Relax 19'" occupies one column, and "99" occupies a separate column, leading to this problem.

How do we address this, considering that similar occurrences may happen with other delimiters present in the columns?

1 answer

Your answer

Answer 1

Subashri Vasudevan 11,221

Hi Rakesh Kumar

Thanks for your query and using MS Q&A Portal.

Regarding your query, if your source file is delimited by comma, and you have value like "Da-PJ 'Relax' 19,99", if the string is enclosed in double quotes, you can make use of quote character. This will be fixing the issue. (check the screenshot below) But if your text is not enclosed within double quotes or escaped by any escape character, then we cannot fix it with the copy activity setting. We will have to use our own logic using c#\Python to process the file.

Screenshot 2023-12-22 at 11.05.21 AM

Rakesh Kumar 20 Reputation points

2023-12-22T06:00:28.63+00:00

@Subashri Vasudevan

I have selected the double quote (") still getting the same issue.

Sink:

Source: in database
Subashri Vasudevan 11,221 Reputation points

2023-12-22T07:48:18.3266667+00:00

But as you show here, the whole text is not enclosed in double quotes.

Only relax is enclosed in double quote. So only the problem occurs. If you can manipulate the source column, try to bring the source data like the below.

"Da-PJ Relax 19,99"

The idea is, if the whole text is enclosed in a double quote, all of them will be considered as single column.
Rakesh Kumar 20 Reputation points

2023-12-22T07:55:05.44+00:00

@Subashri Vasudevan We cannot change this in source as we are migrating 100+ GB of data. We cannot change every cell.

is there any other way to handle this?
Subashri Vasudevan 11,221 Reputation points

2023-12-22T08:40:44.08+00:00

I understand the problem, but the way the source data gets interpreted cannot be changed. As an alternative, you can consider writing to a Pipe delimited text file instead of comma separated to fix this problem.

Share via

delimiter issue during migration

1 answer

Your answer