MySQL Load Data Infile (CSV) : replace/remove vertical tabs

165
March 06, 2019, at 09:10 AM

When using LOAD DATA INFILE to load a csv file with CHARACTER SET latin1 (table also encoded as latin1) all vertical tabs are displayed as '□'. Neither replacing 'u000b' nor '\v' worked.

LOAD DATA INFILE 'database.csv'
INTO table taxa
CHARACTER SET latin1
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(columns1...@column6...column19)
SET @column6 = REPLACE(@column6, '\u000b', '\t')

When I convert the table to UTF8 and import that with 'UTF8' specified, I get an error stating "Invalid utf8 character string:..." in the first field where a \v is coming up.

Notepad++ shows me a 'VT', so I know it's a vertical tab with escape char '\v'. Replacing it with Excel(where it shows up as '□') or NotePad did not work so far.

Printing it to the frontend of my WebApp shows:

'u000b' for \v` 
'u001d' for a Group Seperator`

Question: How to remove these little f*** - Could be even done before importing them, but I couldn'T manage that either.

Answer 1

Way easier than I would've ever thought:

NotePad++ let's you search for unicode characters as well. Instead of \v I used \u000b to search and replace the character. Same for the GS symbol \u001d

Make sure to set the search mode to 'extended' if you wan't to use unicode symbols.

Rent Charter Buses Company
READ ALSO
Update table where column starts with value 'st'%

Update table where column starts with value 'st'%

Im trying to update column brand where productcode starts with ST

94
Select query inside the foreach loop in wordpress

Select query inside the foreach loop in wordpress

How to implement the select query inside the foreach loop I am selecting the ids from array and passing inside the select query

113
enable and enter the value of a textbox basedon the selecting value of dropdown list

enable and enter the value of a textbox basedon the selecting value of dropdown list

I want to enable a text box while selecting a value from a drop down list and enter the text box value into a databaseHow can I do this with only using PHP and MYSQL? The text box should be enabled only when a particular value of the drop down list is selected

109
SUM different queries

SUM different queries

Good afternoon everyone,

122