php 第 1 行 CSV 输入中的列数无效错误

Invalid column count in CSV input on line 1 Error

提问by Carpetfizz

I'm trying to get a ".csv" file onto an SQL database with phpMyAdmin. However, whenever I import it, I get the error: Invalid column count in CSV input on line 1. I've spent all day playing around with different options to try and get it to work but with no avail. There are exactly 47 columns in my .csv file. I have created 47 columns in my SQL table. The names however aren't exactly the same as the ones in the file. Whenever I import, it keeps giving me that error. Any help would be greatly appreciated! ~Carpetfizz One thing I thought might be causing the problem was that the first column isn't named anything in my excel document. Could this be causing an issue?

我正在尝试使用 phpMyAdmin 将“.csv”文件放到 SQL 数据库中。但是，每当我导入它时，我都会收到错误消息：第 1 行的 CSV 输入中的列数无效。我花了一整天的时间尝试不同的选项来尝试让它工作，但无济于事。我的 .csv 文件中正好有 47 列。我在我的 SQL 表中创建了 47 列。但是，名称与文件中的名称并不完全相同。每当我导入时，它都会给我那个错误。任何帮助将不胜感激！~Carpetfizz 我认为可能导致问题的一件事是第一列在我的 excel 文档中没有命名。这会导致问题吗？

EDIT 12:30AM: phpMyAdmin is already the latest version available, via (apt-get install phpmyadmin) (phpmyadmin is already latest version)

编辑 12:30AM：phpMyAdmin 已经是可用的最新版本，通过（apt-get install phpmyadmin）（phpmyadmin 已经是最新版本）

Hereis the .csv file, if that helps.

这是 .csv 文件，如果有帮助的话。

采纳答案by Carpetfizz

Fixed! I basically just selected "Import" without even making a table myself. phpMyAdmin created the table for me, with all the right column names, from the original document.

固定的！我基本上只是选择了“导入”，甚至没有自己制作表格。phpMyAdmin 从原始文档中为我创建了包含所有正确列名的表。

回答by Daniel Flippance

If your DB table already exists and you do NOT want to include all the table's columns in your CSV file, then when you run PHP Admin Import, you'll need fill in the Column Names field in the Format-Specific Options for CSV - Shown here at the bottom of the following screenshot.

如果您的数据库表已经存在并且您不想在 CSV 文件中包含所有表的列，那么当您运行 PHP 管理导入时，您需要填写 CSV 格式特定选项中的列名字段 - 显示在以下屏幕截图的底部。

In summary:

总之：

• Choose a CSV file
• Set the Format to CSV
• Fill in the Column Names field with the names of the columns in your CSV
• If your CSV file has the column names listed in row 1, set "Skip this number of queries (for SQL) or lines (for other formats), starting from the first one" to 1

• 选择一个 CSV 文件
• 将格式设置为 CSV
• 使用 CSV 中的列名称填写列名称字段
• 如果您的 CSV 文件具有第 1 行中列出的列名称，请将“跳过此查询数（对于 SQL）或行数（对于其他格式），从第一个开始”设置为 1

回答by Nicholas

I got the same error when importing a .csv file using phpMyAdmin.

使用 phpMyAdmin 导入 .csv 文件时，我遇到了同样的错误。

Solution to my problem was that my computer saved the .csv file with ; (semi-colon) as delimiter instead of , (commas).

我的问题的解决方案是我的电脑保存了 .csv 文件；（分号）作为分隔符而不是 , （逗号）。

In the Format-Specific Options you can however chose "columns separated:" and select ; instead of , (comma).

但是，在特定格式选项中，您可以选择“列分隔：”并选择 ; 而不是，（逗号）。

In order to see what your computer stores the file in, open the .csv file in an text editor.

要查看您的计算机将文件存储在什么位置，请在文本编辑器中打开 .csv 文件。

回答by Brane

You will need to skip first row (where column names are defined) and you will need to check which "character" is separating cells (usually is ,, but in my case was ;)

您将需要跳过第一行（其中定义了列名）并且您需要检查哪个“字符”正在分隔单元格（通常是,，但在我的情况下是;）

Bellow is the picture of my import:

波纹管是我进口的图片：

回答by AO_

This is actually pretty simple to fix, I originally wrote about the fix ~10 years ago over here https://ao.gl/phpmyadmin-invalid-field-count-in-csv-input-on-line-1/

这实际上很容易修复，我最初在这里写了大约 10 年前的修复https://ao.gl/phpmyadmin-invalid-field-count-in-csv-input-on-line-1/

What you want to do is change "Fields terminated by" from ";" to "," and then make sure that the "Use LOCAL keyword" is selected.

您想要做的是将“字段终止于”从“；”更改为 到“,”，然后确保选择了“使用本地关键字”。

回答by Rule

Had the same problem and did two changes: (a) did not over-write existing data (not ideal if that is your intention but you can run a delete query beforehand), and (b) counted the columns and found that the csv had an empty column so it always pays to go back to your original work even though all 'seems' to look correct.

遇到了同样的问题并做了两个更改：(a) 没有覆盖现有数据（如果这是您的意图并不理想，但您可以事先运行删除查询），以及 (b) 计算列数并发现 csv一个空列，因此即使所有“似乎”看起来都正确，返回到您的原始作品总是值得的。

回答by Cody O'Dell

I just had this issue and realized that there were empty columns being treated as columns with values, I saw this by opening my CSV in my text editor. To fix this, I opened my spreadsheet and deleted the columns after my last column, they looked completely empty but they were not. After I did this, the import worked perfectly.

我刚刚遇到这个问题并意识到有空列被视为带有值的列，我通过在文本编辑器中打开我的 CSV 看到了这一点。为了解决这个问题，我打开了我的电子表格并删除了最后一列之后的列，它们看起来完全是空的，但实际上并非如此。在我这样做之后，导入工作完美。

回答by P-S

When facing errors with input files of any type, encoding issuesare common.

当遇到任何类型的输入文件错误时，编码问题很常见。

A simple solution might be to open a new file, copy pasting your CSV text in it, then saving it as a new file.

一个简单的解决方案可能是打开一个新文件，将 CSV 文本复制粘贴到其中，然后将其另存为新文件。

回答by Netwise Spain

The final column of my database (it's column F in the spreadsheet) is not used and therefore empty. When I imported the excel CSV file I got the "column count" error.

我的数据库的最后一列（它是电子表格中的 F 列）未使用，因此为空。当我导入 excel CSV 文件时，出现“列数”错误。

This is because excel was only saving the columns I use. A-E

这是因为 excel 只保存我使用的列。AE

Adding a 0 to the first row in F solved the problem, then I deleted it after upload was successful.

F中第一行加0解决了，上传成功后我删除了。

Hope this helps and saves someone else time and loss of hair :)

希望这有助于并节省其他人的时间和脱发:)

回答by jtubre

The dumbest thing ever that will fix this error in Microsoft Excel (assuming you actually have everything else right):

有史以来最愚蠢的事情将在 Microsoft Excel 中修复此错误（假设您实际上拥有其他一切正确）：

Select your data and click "Borders All" in Excel (puts visual borders around your data) before saving the CSV. Sound pointless? I completely agree! However, it will fix this error. I use this trick at least three times a week.

在保存 CSV 之前，选择您的数据并单击 Excel 中的“全部边框”（在您的数据周围放置可视边框）。听起来毫无意义？我完全同意！但是，它将修复此错误。我每周至少使用这个技巧三次。