如何使用MySQL导入的CSV中查找和替换 [英] How do I find and replace in a CSV I'm importing using mysql

问题描述

我正在将CSV文件导入Heidi SQL.我使用以下代码创建了表:

I'm importing a CSV file into Heidi SQL. I've created the 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
        )
;

，但是在"Avg_Daily_visitors"列中，其值为"\ N"，其中没有任何值.我一直在使用以下代码导入数据:

but in the Avg_Daily_visitors column it has "\N" where there is no value. I've been importing the data with 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)
;

但是它没有用0代替值，这是我想要实现的.如何在导入时让Heidi SQL将"\ N"替换为"0"?

but it's not replacing the values with 0, which is what I want to achieve. How do I make Heidi SQL replace "\N" with "0" on import?

谢谢.

推荐答案

首先将您读取的值分配给一个变量，然后对该变量进行处理.为此，您可以指定目标表的列，但要指定变量而不是要替换的列.

First assign the value you read to a variable, then work on that variable. For this you specify the columns of your destination table, but a variable instead of the column where you want to replace.

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)
;

这篇关于如何使用MySQL导入的CSV中查找和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！