How do I find and replace in a CSV I import using mysql
I am importing a CSV file into Heidi SQL. I created a table using this code:
create table all_data
(
Keyword varchar(1000),
Position int,
Previous_Position int,
Search_Volume int,
KW_Difficulty float,
URL varchar(1000),
Post_title varchar(1000),
Post_URL varchar(1000),
Genre varchar(1000),
Location varchar(1000),
Avg_Daily_Visitors float,
pageviews int
)
;
but in Avg_Daily_visitors column it has "\ n" where there is no value. I imported data using this code:
load data local infile 'C:/filepath.../All_Data.csv'
replace into table all_data
fields terminated by ','
enclosed by '"'
escaped by '"'
lines terminated by "\r\n"
ignore 1 rows
set
Avg_Daily_Visitors = replace(Avg_Daily_Visitors,"\N",0),
pageviews = replace(pageviews,"\N", 0)
;
but that doesn't replace the values 0, which is what I want to achieve. How do I force Heidi SQL to replace "\ n" with "0" on import?
Thank.
+3
source to share
1 answer
First, assign the value you are reading to a variable and then apply that variable. To do this, you specify the columns of the target table, and instead of the column where you want to replace, instead of a variable.
load data local infile 'C:/filepath.../All_Data.csv'
replace into table all_data
fields terminated by ','
enclosed by '"'
escaped by '"'
lines terminated by "\r\n"
ignore 1 rows
(column_1, column_2, @variable1, @variable2, column_5)
set
Avg_Daily_Visitors = replace(@variable1,"\N",0),
pageviews = replace(@variable2,"\N", 0)
;
0
source to share