dtmbc1606 2012-05-20 09:11
浏览 896
已采纳

LOAD DATA LOCAL INFILE不能跳过列?

I've searched this for a few hours already, but it doesn't work.

I have a csv files of presidency. the first row is the header.

Presidency ,President ,Wikipedia Entry,Took office ,Left office ,Party ,Portrait,Thumbnail,Home State

I want to create a relation that has only Presidency, President, and Party.

$sqlquerycreate1 = "CREATE TABLE IF NOT EXISTS `sample1` (
  `Presidency`  VARCHAR(30),
  `President` VARCHAR(30),
  `Party` VARCHAR(30)
)";
mysql_query($sqlquerycreate1);

# download the file off the internet
$file = file_get_contents("http://localhost/sample.csv");
$filepath = "sample.csv";
file_put_contents($filepath, $file);

# load the data into nycgrid table
$pt11 = "LOAD DATA LOCAL INFILE ";
$pt21 = "'C:/xampp/htdocs/test/file/sample.csv' INTO TABLE sample1(Presidency,President,@Wikipedia Entry,@Took office ,@Left office ,Party,@Portrait,@Thumbnail,@Home State) ";
$pt31 = "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ";
$pt41 = "LINES TERMINATED BY '
' ";
$pt51 = "IGNORE 1 LINES";
$sqlquerynew1 = $pt11.$pt21.$pt31.$pt41.$pt51;
mysql_query($sqlquerynew1);

It doesn't work. The table is created, but no data loaded. Any idea why? Thanks.


mysql> LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/sample.csv' INTO TABLE sample1(Pr
esidency, President, @x, @x, @x, Party, @x, @x, @x) FIELDS TERMINATED BY ',' ENC
LOSED BY '\"' LINES TERMINATED BY '
' IGNORE 1 LINES;


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FIELD
S TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '
' IGNORE 1 LINE' at
line 1
  • 写回答

1条回答 默认 最新

  • dsh125986083 2012-05-20 09:29
    关注

    You have syntax errors in your LOAD DATA statement. In particular:

    1. As stated in the manual:

      User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`)

      Therefore your variables named @Wikipedia Entry, @Took office, @Left office and @Home state are all syntactically incorrect. You must either remove the whitespace or quote the variable names.

      However, you don't need to use differing variable names unless you intend to refer to them elsewhere. As stated in the manual:

      You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

      LOAD DATA INFILE 'file.txt'
        INTO TABLE t1
        (column1, @dummy, column2, @dummy, column3);
      
    2. The FIELDS, LINES and IGNORE clauses should all preceed the column list. As stated in the manual, the syntax is:

      LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
          [REPLACE | IGNORE]
          INTO TABLE tbl_name
          [CHARACTER SET charset_name]
          [{FIELDS | COLUMNS}
              [TERMINATED BY 'string']
              [[OPTIONALLY] ENCLOSED BY 'char']
              [ESCAPED BY 'char']
          ]
          [LINES
              [STARTING BY 'string']
              [TERMINATED BY 'string']
          ]
          [IGNORE number LINES]
          [(col_name_or_user_var,...)]
          [SET col_name = expr,...]

    Therefore, you can simply do:

    LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/test/file/sample.csv'
        INTO TABLE sample1
        FIELDS
            TERMINATED BY ','
            ENCLOSED BY '\"'
        LINES
            TERMINATED BY '
    '
        IGNORE 1 LINES
        (Presidency, President, @x, @x, @x, Party, @x, @x, @x)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择
  • ¥15 这款软件是什么?需要能满足我的需求
  • ¥15 SpringSecurityOauth2登陆前后request不一致