Trying to remove bad data from csv file to import into database

Question

Good day,

I am having trouble removing some bad data from my input file before writing the data to the database (Microsoft Sql Server).

The bad data has a lots of different values but one example for a field shows:

I am unsure how to remove these special characters or characters of some sort from my dataset.

I have tried importing the csv with different Code Pages and tried removing the values for the various fields using the following formula tools:

Formula tool 1: REPLACE(REGEX_REPLACE([LAST_NAME],"(.*)(?:[^x20-x7E])(.*)","$1"+"$2")," "," ")

Formula tool 2: Regex_Replace([LAST_NAME],"[[:unicode:]]",'FAIL') = [LAST_NAME]

I have also tried flagging the records by creating a new field:

IF REGEX_CountMatches([FIRST_NAME],"[^'a-zA-Z\s\d-]+") > 0
then 'INVALID' else 'Fine'
endif

Any help would be much appreciated.

Cheers,

Clayton.

ClaytonA · Answer

Good morning,

I did try a few use cases of REGEX_MATCH, however the T/F flag was not populating correctly.

Will your expression also work for address related data, eg. 123 Test Lane?

I will try your example and see if it flags the records accordingly.

Also is there a glossary or best practices document for Regular Expressions and their meanings? In this case I am unsure what x and E are referring to.

Thank you for your reply.

Cheers,

Clayton Ayers.

BS_THE_ANALYST · Answer

@ClaytonA can't you just use REGEX_MATCH, this will flag the data, then filter accordingly. You just need to define what are the "suitable" characters and match those that solely contain those, or do the opposite and define the "bad" set. Whatever way suits you, however, defining the set to retain is dynamic and therefore less work.

Just lifted a character set out of one of the formulas you provided earlier. Then you get the True/False flag.

All the best,

BS

ClaytonA · Answer

Good morning,

Sorry for the delayed response.

@Preferably, if a record contains special characters I would like to send it to a separate sql table for bad data review.

Is there a way to filter on if a cell contains special characters or remove special characters from a value with regular text?

The main issue with the old process I am using is that the special characters exceed the field limits I have set. Which fails on import into my sql database using Sql Server Management Studio Tasks.

Please let me know if you have any other questions.

Some examples below:

Example 1.) Ø®ÛŒØ§Ø¨Ø§Ù† Ù¾ÛŒØ±ÙˆØ²ÛŒ_Ù†Ø±Ø³ÛŒØ¯Ù‡ Ø¨Ù‡ Ø¯Ø±Ù…Ø§Ù†Ú¯Ø§Ù‡ Ø±Ø§Ø²ÛŒ_Ù…Ø¬ØªÙ…Ø¹ ØªØ¬Ø§Ø±ÛŒ Ù…Ø³Ú©ÙˆÙ†ÛŒ Ø§Ø±Ø²Ø§Ù† ØªØ¹Ø§ÙˆÙ† Ø´Ù‡Ø±_ÙˆØ§Ø­Ø¯ Ø§ÙˆÙTEST1Example 2. containing multiple fields with bad data)testÐ¡Ð°Ð½ÐºÑ‚- ÐŸÐµÑ‚ÐµÑ€Ð±ÑƒÑ€Ð³,Ð¡Ð°Ð½ÐºÑtest‚- ÐŸÐµÑ‚ÐµÑ€Ð±ÑƒÑ€Ð³Cheers,

Clayton Ayers.