How to remove special characters from text file

user_13342391 0
Viewed 909

I've been trying to import a text file into mysql, but with no luck. I get the following error when running this command

LOAD DATA LOCAL INFILE '/file' INTO TABLE test COLUMNS TERMINATED BY ':';
ERROR 1300 (HY000): Invalid utf8mb4 character string: ''

I found a command that was shared on here that shows me the lines with the bad characters.

grep -axv '.*' filename

11241321:▒oy▒_moley@zmal.com
11241771:▒om▒_shead@zmal.com
11241811:▒eva▒_gould@zmal.com

Now that I can tell which lines the bad characters are on then I could go delete the bad characters manually, but that's a pain because these files are potentially huge. I wonder if there is a way to find those bad characters and then delete them using the linux command line. I would like to keep the line intact, but just remove the bad character.

sed awk linux mysql
1 Answers

Remove characters that are not within the ascii table (11,12,40-176)

enter image description here

\11 = tab

\12 = new line

\40-176 = ( to ~ this range includes all letters and symbols present in the keyboard

cat test.txt | tr -cd '\11\12\40-\176' > temp && mv temp test.txt

NOTE: If your data has special characters that are not in the ascii table, they might be removed as well

user_147336690
Related