MySQL - 将.CSV / Excel文件导入MySQL [英] MySQL - Import .CSV/Excel file into MySQL

问题描述

如果我想导入csv / excel文件但我希望内容位于特定列中，我该怎么办？

How do I do it when I want to Import a csv/excel file but I want the contents to be in a specific column?

我有6列我的数据库。我们只需将其称为a，b，c，d，e，f。

I have 6 columns in my database. Let's just call it a, b, c, d, e, f.

我想从excel文件中将内容插入c，d，e。
将自动生成列a，b和f的内容。

I want to insert contents to c, d, e from the excel file. Contents of the column a, b and f would be automatically generated.

我正在使用LOAD DATA INFILE脚本。

I'm using the LOAD DATA INFILE script.

推荐答案

一个选项是将用户定义的变量指定为fiel的目标，代替列末尾列中的列名称LOAD DATA语句。

One option is to specify user-defined variables as a target for a fiel, in the place of a column name in the column list at the end of the LOAD DATA statement.

例如（省略大部分语法）：

For example (omitting most of the syntax):

LOAD DATA ... 
...     
(@dummy1, @dummy2, c, d, e, @dummy3)

一行中的第一个字段将分配给用户定义的变量@ dummy1，第二个字段将分配给另一个用户定义的变量，第三个，第四个和第五个字段将分配给分配给列c，d和e（分别），第六个字段将分配给另一个用户定义的变量。

The first field from a line will be assigned to user-defined variable @dummy1, the second field to another user-defined variable, the third, fourth and fifth fields will be assigned to columns c, d, and e (respectively), the sixth field will be assigned to another user-defined variable.

如果要为a提供值作为表达式结果的列，您可以使用SET子句......

If you want to provide a value for a column that's the result of an expression, you can use the SET clause...

LOAD DATA ... 
...     
(@a, @b, c, d, e, @dummy3) 
SET a = UPPER(@a) 
  , b = STR_TO_DATE(@b,'%m/%d/%Y')
  , f = CONCAT(@a,@b)

---

更新

如果您在csv中只有三个字段，并且您想要将第一个字段分配给列 c ，将第二个字段值分配给列 d ，将第三个字段值分配给列 e ...

If you only have three fields in the csv, and you want to assign the first field to column c, the second field value to column d, and the third field value to column e...

LOAD DATA ...
...
(c, d, e)

这是位置 （col_or_user_var，...） 列中的列名称，用于确定将哪个字段值分配给哪个列。要跳过某个字段，请将其分配给用户定义的变量...必须将文件中的字段分配给某个字段，如果您不想将其分配给列，则将其分配给用户定义的字段变量。

It's the position of the column name in the (col_or_user_var,...) list that determines which field value will be assigned to which column. To "skip" a field, assign it to user-defined variable... the field in the file has to be assigned to something, and if you don't want to assign it to a column, the assign it to user-defined variable.

您的问题中不清楚文件中有多少字段，哪个字段应分配给哪一列。

It wasn't clear in your question how many fields were in the file, and which field should be assigned to which column.

这篇关于MySQL - 将.CSV / Excel文件导入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！