Import a csv file into mysql through phpmyadmin

0
votes

i am trying to import a csv file into mysql through phpmyadmin I am doing as follow:

1- import

2- choose file (test.csv)

3- format csv

4- Columns separated with: ,

5- Columns enclosed with: "

6- Columns escaped with: \

7- Lines terminated with: auto

8- Column names: (list of my column, comma separated)

But I am having always the same error: invalid column count in CSV input on line 1.

I am trying too to use the csv using load data format, but the values inserted in the database are not correct.

Edit: My csv rows:

3861    23  Zougheib    NULL    http://www.mobigate.mobi:8081/ABC/upload/23/ts1Copy2.jpg    http://www.mobigate.mobi:8081/ABC/upload/23/s1Copy2.jpg IMAGE_AND_ARTICLE   http://www.mobigate.mobi:8081/ABC/upload/23/ts1Copy2.jpg    Meet us at ABC Achrafiyeh and ABC Dbayeh every day from 10 am to 10 pm and learn about our new collection.  0   NULL    0   0   0   NULL    NULL    NULL    NULL    0   0   1   0   0   APPROVED    interface   0   ("36")  <html><style type='text/css'>a {text-decoration:none; color:#fff}.shops p{color:#fff; line-height: .9 em; margin-top: 2px}.shops {color: #fff}.alignleft {float: left;width:50%;text-align:left;}.aligncenter {float: left;width:10%;text-align:center;}.alignright {float: left;width:40%;text-align:right;}.floatright {float: right}</style><div class='shops'><div id='openinghours'><p class='alignleft'>OPENING HOURS</p><p class='floatright'>10:00 AM - 11:00 PM</p></div><div id='place1'><p class='alignleft'><a href='achrafieh'>>ABC ACHRAFIEH</a></p><p class='aligncenter'>L1</p><p class='alignright'><a href='tel:01212888'>01212888</a></p></div></div> </html>    NULL    10:00 - 22:00   ABC ACHRAFIEH   none    L1  L0  1212888 NULL

my columns name:

CONTENT_ID,CHANNEL_ID,TITLE,DESCRIPTION,LINK,THUMBNAIL_LINK,MEDIA_TYPE,ARTICLE_MEDIA_LINK,ARTICLE_TEXT,POINTS,LOCATION_NAME,LOCATION_LONGITUDE,LOCATION_LATITUDE, LOCATION_RADIUS,AD_POSITION, APP_PACKAGE_NAME,APP_IMAGE_SCALE,APP_IMAGE_POSITION,SHOW_DATE, SHOW_START_TIME,SHOW_END_TIME,AD_FREQUENCY_OF_SHOWING,AD_IS_INTERRUPTABLE,STATUS,SOURCE,NOTIFY_USERS,TAGS,ARTICLE_HTML,contentcol,HTML_HOURS,HTML_LOCATION1,HTML_LOCATION2,HTML_LEVEL1,HTML_LEVEL2, HTML_PHONE1,HTML_PHONE2

any help please?

3
mysqlcsvimportphpmyadmin
Can't we see the first lines of the CSV file ? - kmas
Count the columns on line 1 of your CSV file, and then count the number of columns that you input at step 8. The error message says they are not the same. - Miklos Aubert
@Miklos Aubert: The number of columns is the same - Berna
Check in database, run sql query show create table your_table, maybe you have AUTO_INCREMENT ID and trying to import new fixed ids - mirkobrankovic
@ mirkobrankovic: my first column is AUTO_INCREMENT. I have removed it from the csv file and in the "column names" I have listed all my column except the auto increment one. Still not working - Berna

3 Answers

2
votes

Can I suggest to use the "OpenDocument Spreadsheet (ODS)" import option (phpmyadmin 3.3.0 and above) ? It's really fast, reliable and it will create a new table with your data to be safely managed.

0
votes

It seems that :

  • Columns separated with: \t (for tab character)
  • Columns enclosed with: [NOTHING]

Try and tell us.

-1
votes

I had this problem, too. Then I scanned through the csv spreadsheet (it had been prepared by someone else), and found that there were several rows that had extra fields. After editing these rows (and scolding the person who prepared the CSVs), everthing worked properly.