CODEWITHSUNDEEP
excelphpmyadminspreadsheet
Giffary
Giffary

Reputation: 3128

Can I import excel file to phpmyadmin?

I have an speadsheet file contains some data such as ID, name, lastname. Can I import excel file to phpmyadmin (SQL)? How to do it?

Upvotes: 1

Views: 16072

Answers (3)

Dave
Dave

Reputation: 11

Excel tables must not have blank cells - these will be skipped when CSV file is created giving the wrong number of fields in the row(s) concerned. Also Excel cells must not contain any symbols used as delimiters (typically doublequote" and comma,. The delimiters may vary according to excel version/language.

Excel 2016 CSV looks like:

22,Fred,Bloggs,22,Acacia Avenue

...but phpMyAdmin requires:

"22","Fred","Bloggs","22","Acacia Avenue"

My tip - add extra columns before and after your excel table and fill each column with something unique like ZZ. Then create CSV. Use a text editor with Find/Replace to change each comma to ",". This gives you ZZ","22","Fred","Bloggs","22","Acacia Avenue","ZZ. Then Find/Replace ZZ"," with " and then ","ZZ with ".

Now your CSV matches what phpMyAdmin needs.

Upvotes: 1

local idiot
local idiot

Reputation: 49

is there a white space in what you think could be an empty excel field? Also you want to check that your delimiter isnt represented in the text field. Meaning that if you have stored a persons name or id with a comma, like "Nixon, Richard", it will think that is for two seperate colums, giving you a count mismatch on using the first delemited lines as more than 2 colums.

The error message is telling you that it found a different number of columns when compared to the rows of data. More than likely your delimiter is inside of your datacells.

Upvotes: -1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798666

Export the spreadsheet as CSV first, then import as CSV.

Upvotes: 4

Related Questions