String Type does not handle special characters "(", ")", "&", "="

Question

I am loading a csv file with fields Speciality and Webpage URL. Datatype for both of these is set to string

Few Examples:

Speciality

Nephrology (NEP)

Internal Medicine (IM)

Nephrology (NEP)

Dermatology (D)

Webpage URL:

play.google.com/store/apps/details?id=jp.gocro.smartnews.android

thespruce.com

allears.net

si.com

newsweek.com

natashaskitchen.com.

However, Azure data flow replaces these with nulls.

Sample Dataset:

Speciality

website_url

Azure data flow with nulls for the selected columns

Data is being loaded to mysql: database table columns are marked varchar if that is any relevance.

This is baffling me. Any help is appreciated.

Thanks

Answer 1

Some special characters might be interpreted differently by ADF or MySQL. For example, & is often used in SQL for concatenation, and = is a comparison operator.

The CSV file might have encoding issues that cause certain characters to be misinterpreted.

The MySQL table might be using a character set or collation that does not support certain special characters.

If you suspect that (, ), &, and = are causing issues, you could use a derived column transformation to replace these with a safe character sequence:

replace(replace(replace(replace(Speciality, '(', '[LEFT_PAR]'), ')', '[RIGHT_PAR]'), '&', '[AND]'), '=', '[EQUAL]')