麻烦在phpMyAdmin中导入/替换带有CSV的表:在第1行的CSV输入中无效的列数 [英] Trouble importing/replacing a table w CSV in phpMyAdmin: Invalid column count in CSV input on line 1

问题描述

我正在尝试用新数据交换phpMyAdmin中的表.我在网上搜寻，试图找到问题的答案，但是没有运气.我不确定问题是否出在我的CSV或phpMyAdmin导入设置中.

这是我要导入的CSV文件的前两行的示例...

 'CA1923'，'6320 Canoga Avenue'，''，'6320 Canoga Avenue'，NULL，'Woodland Hills'，'CA'，'91367'，'USA'，NULL，NULL，NULL，'info @ connexusvideo.com'，NULL，NULL，'0'，NULL，NULL，NULL，NULL，'0'，NULL，NULL，'0'，'http://www.google.com/mapfiles/markerN.png"，-118.5961599"，"34.1851569"，"12"，"8"，"42"，"1"，"0"，"6320 Canoga Avenue Woodland Hills CA"，"6320 Canoga Avenue Woodland Hills CA"，'0'，'CA1923'，'0'，'6'，'0'，'CA1927'，'425 Market Street'，'，'425 Market Street'，NULL，'San Francisco'，'CA'，'94105'，'USA'，NULL，NULL，NULL，'[e-mail]'，NULL，NULL，'0'，NULL，NULL，NULL，NULL，'0'，NULL，NULL，'0'，'http://www.google.com/mapfiles/markerN.png'、'-122.3981953"，"37.7912401"，"12"，"8"，"42"，"1"，"0"，"425 Market Street San Francisco CA"，"425 Market Street San Francisco CA"，"0"，"CA1927"，"0"，"7"，"0"， 

这是此表的SQL文件中导出的列标题...

 插入到conx_gmapfp中(id，nom，alias，adresse，adresse2，ville，部门，codepostal，pay，tel`，`tel2`，`传真`，`电子邮件`，`web`，`img`，`专辑`，`介绍`，`消息`，`horaires_prix`，`链接`，`article_id`，`icon`，`icon_label`，`affichage`，`marqueur`，`glng`，`glat`，`gzoom`，`catid`，`userid`，`published`，`checked_out`，`metadesc`，`metakey`，`ordering`，`room_id`，`distance`，`ZipRmID`，`rStatus`)值 

我认为应该起作用的导入设置如下...

FORMAT = CSV

使用文件=选中数据替换数据表

以，

分隔的列

以"

"括起来的列

使用以下符号对列进行转义:/

行终止于:自动

但是，每次收到此错误第1行CSV输入中的列数无效"

我在现有表&中有39列CSV上有39列.每列均以分隔，并以'

括起来

这里的问题是什么?我该如何解决?我花了整整一天的时间来弄清楚简单的解决方案是什么，但无法弄清楚.我知道它必须是基本的，但我已经尝试过任何方法.我放弃.帮助！

解决方案

尽管可以确保您具有正确的列数(如表中所示)，但phpmyadmin仍然可以使用phpmyadmin导入.>

在这种情况下，应使用以下选项成功导入.

• FORMAT:带有LOAD DATA的CSV
• 以下列字符结尾的列:
• 以"
"结尾的列
• 由以下字符转义的列:\
• 行终止于:自动

I am trying to swap out a table in phpMyAdmin with new data. I have scoured the web trying to find an answer to my issue but with no luck. I am not sure if the issue is somewhere in my CSV or in the phpMyAdmin import settings.

Here is an example of the first two lines of my CSV file that I am trying to import...

'CA1923', '6320 Canoga Avenue', '', '6320 Canoga Avenue', NULL, 'Woodland Hills', 'CA', '91367', 'USA', NULL, NULL, NULL, '[e-mail]', NULL, NULL, '0', NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', 'http://www.google.com/mapfiles/markerN.png', '-118.5961599', '34.1851569', '12', '8', '42', '1', '0', '6320 Canoga Avenue Woodland Hills CA', '6320 Canoga Avenue Woodland Hills CA', '0', 'CA1923', '0', '6', '0',
'CA1927', '425 Market Street', '', '425 Market Street', NULL, 'San Francisco', 'CA', '94105', 'USA', NULL, NULL, NULL, '[e-mail]', NULL, NULL, '0', NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', 'http://www.google.com/mapfiles/markerN.png', '-122.3981953', '37.7912401', '12', '8', '42', '1', '0', '425 Market Street San Francisco CA', '425 Market Street San Francisco CA', '0', 'CA1927', '0', '7', '0',

Here is the column headers exported out of the SQL file for this table...

INSERT INTO `conx_gmapfp` (`id`, `nom`, `alias`, `adresse`, `adresse2`, `ville`, `departement`, `codepostal`, `pay`, `tel`, `tel2`, `fax`, `email`, `web`, `img`, `album`, `intro`, `message`, `horaires_prix`, `link`, `article_id`, `icon`, `icon_label`, `affichage`, `marqueur`, `glng`, `glat`, `gzoom`, `catid`, `userid`, `published`, `checked_out`, `metadesc`, `metakey`, `ordering`, `room_id`, `distance`, `ZipRmID`, `rStatus`) VALUES

The import settings that I think should work are the following...

FORMAT = CSV

Replace Data Table With File = CHECKED

Columns separated with: ,

Columns enclosed with: '

Columns escaped with: /

Lines terminated with: auto

However, everytime I get this error "Invalid column count in CSV input on line 1."

I have 39 columns in the existing table & there are 39 columns on the CSV. Every column is seperated with a , and enclosed with a '

What is the issue here & how do I fix this? I have spent all day trying to figure out what the simple solution is but can't figure it out. I know it must be basic but I have tried anything. I give up. HELP!

解决方案

WIth phpmyadmin import, despite making sure you have the right number of columns as in the table this error message occurs if there are commas within the values themselves.

In that case it should import successfully with the following options..

• FORMAT: CSV with LOAD DATA
• Columns terminated by: ,
• Columns enclosed with: "
• Columns escaped by: \
• Lines terminated by: auto

这篇关于麻烦在phpMyAdmin中导入/替换带有CSV的表:在第1行的CSV输入中无效的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！