Error importing data from an Excel spreadsheet to mysql directly on the server

Question

I am trying to import data from an Excel csv spreadsheet, however the following error appears:

Invalid column count in CSV input on line 1.

I imported directly to the root of the database (created new table) and when finishing the data were grouped in the same column and another is empty.

There are only 3 fields (ID, CNPJ and social reason), but with more than 30 thousand records.

What could be done to import correctly?

Table image:

Answer 1

Assuming you have a CSV file with the following structure:

ID;MES1;MES2;MES3
106186;0;0;0
123609;0;0;0

And a table called "my table" with the following structure:

CREATE TABLE `minha_tabela` (
  `ID` int(11) NOT NULL,
  `MES1` int(11) NOT NULL,
  `MES2` int(11) NOT NULL,
  `MES3` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

On the terminal, after connecting with your Mysql database server and opening the database, you could import the file this way:

load data local infile 'public_html/meu_arquivo.csv' into table minha_tabela
fields terminated by ';'
lines terminated by '\r\n'
(ID,MES1, MES2, MES3);