2

How can I get rid of these null columns? I'm importing my csv file to create a table on phpMyAdmin. The sql query looks appropriate but at the end, I get...

`Category 1` VARCHAR( 97 ) ,
`Category 2` VARCHAR( 113 ) ,
`NULL` VARCHAR( 71 ) ,
`NULL` VARCHAR( 56 ) ,
`NULL` VARCHAR( 73 ) ,
`NULL` VARCHAR( 97 )
) ENGINE = MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL said: 

#1060 - Duplicate column name 'NULL'

Category 2 should be the last column. I can't figure out where the null columns are coming from, or how to get rid of them. The original file is given to me in .xls format. I make some changes and save as csv. I used to upload an xls file (never had problems) but I think our cPanel was upgraded so it's all changed.

So far I've tried deleting 4+ columns at the end while in OpenOffice. I've tried opening the csv in notepad and making sure there was no extra commas or spaces, adding in / to escape. I've tried fiddling with the import options in phpMyAdmin but I'm far from an expert so no luck there.

Marilee
  • 33
  • 1
  • 5

4 Answers4

3

For me, I had to select:

columns separated with ,

columns enclosed with "

columns escaped with "

lines terminated with auto

and check The first line of the file contains the table column names

RPL
  • 3,500
  • 2
  • 21
  • 28
1

Came across that and what fixed it for me wasn't very intuitive.

In the import settings I checked the columns enclosed with [ " ].

That seemed to fix it and all imported nicely (as far as I know).

Ryan Ore
  • 1,315
  • 17
  • 23
0

I had a similar thing with two cvs uploads. PHPMyAdmin complained that one had 2 NULL columns at the end, and the other had 6. Thought I'd 'fixed' it by adding extra fake columns, but no.

Turned out that some of the data had double quotes round it, fooling mysql into thinking there was the wrong number of columns.

Just had to check through all the data to make sure it was clean... I know its more of a work around than a fix. But the best I could do in a short time.

Steve Peck
  • 16
  • 1
  • I found a nice free tool that checks for errors. I couldn't fix it yet, but... https://csvlint.io/ – Darkgaze Apr 11 '17 at 20:23
  • I fixed it. Gives you a nice check. For me, it was the existence of " inside the text, using commas and ; instead... doing regular replacement by hand on notepad worked until I passet the test. – Darkgaze Apr 11 '17 at 20:30
0

In my case: I change the column separator from " to ' Same in phpMyAdmin in the Format-specific options: change from " to '

Bernardo Paz
  • 1
  • 1