CSV导入到SQL MAMP受支持的数据库的正确语法 [英] Correct Syntax for CSV import to SQL MAMP Powered Database

问题描述

我在我的本地机器上运行MAMP，并且使用此命令进行导入成功，但它在我的语法上抛出一个错误。

  / applications / MAMP / library / bin / mysql -u testtest -p test< /Users/myName/info.csv 
  

我已经将很多CSV文件导入/导出到不同的数据库并且注意到语法对于不同的DB稍有不同，并给出错误有时带或不带引号等。我想知道是否有人可以解释这个给我，在这种特殊情况下可能是什么问题，因为这是格式

  ID，第一个，最后一个，电子邮件，积分， k，gID，fID，tID，rID 
45，mark，brown，[e-mail]，234， ，
353，sam，harris，[e-mail]，343，3432， $ b  

我收到此错误，这是为什么我认为

 错误1064（42000）在第1行：您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册，以获得在第1行的ID，第一，最后，电子邮件，点，kID，gID，f附近使用的正确语法
  
 
 
解决方案
对于导入csv文件，请改用加载数据语法。 
从生成的CSV文件中删除CSV标头以及Excel可能在CSV文件末尾放置的空数据。
 
 
 
 IGNORE数字LINES选项可以用来忽略文件开头的行。例如，您可以使用IGNORE 1 LINES跳过包含列名称的初始标题行：
  LOAD DATA INFILE'/用户/myName/info.csv'INTO TABLE test IGNORE 1 LINES; 
  
因此，您可以使用以下语句：
  LOAD DATA LOCAL INFILE'/Users/myName/info.csv'
 INTO TABLE test 
终止于'，'
 ENCLOSED BY' '
 LINES TERMINATED BY'\\\
'
 IGNORE 1 LINES 
  
 
I am running MAMP on my local machine, and using this command for an import works successfully but it is throwing an error on my syntax. 
/applications/MAMP/library/bin/mysql -u testtest -p test < /Users/myName/info.csv 
I have been importing/exporting a lot of CSV's into different databases and have noticed that the syntax is slightly different for different DBs, and giving errors sometimes with or without quotes etc. I'm wondering if someone could explain this to me, and what may be the problem in this particular case as this is the format my CSV export came in when I exported it using a Drupal UI.
ID,First,Last,e-mail,Points,kID,gID,fID,tID,rID
"45","mark","brown","[e-mail]","234","","34","","532",""
"353","sam","harris","[e-mail]","343","3432","","43","","87"
I am getting this error which is why I think this
ERROR 1064 (42000) at line 1: 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 'ID,First,Last,e-mail,Points,kID,gID,f' at line 1

解决方案
For import csv files use 'load data syntax' instead.
Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:
LOAD DATA INFILE '/Users/myName/info.csv' INTO TABLE test IGNORE 1 LINES;
Therefore, you can use the following statement:
LOAD DATA LOCAL INFILE '/Users/myName/info.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES


                        
这篇关于CSV导入到SQL MAMP受支持的数据库的正确语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！