How to pass excel data to Mysql?

Asked

Viewed 23,089 times

6

First, I have a newsletter system that I modified for email marketing, it’s all in PHP (obvious) and first, I add the emails manually through a part of the site add.php but there are over 3000 emails to be added and it will take a long time to do this manually.

Anyway the question is:

Would you have some way to pass an excel email list to the database in MYSQL ?

use the phpmyadmin I don’t know if it helps to specify...

In case you don’t understand you can comment that I try to explain better.

php mysql sql excel
  • 2

    Yes it is possible, believe it! what is the excel structure? it is possible to convert to a csv?

  • 1

    great to know this friend, already this in csv, it gets easier?

  • 4

    Behold that to extract csv values, then just format SQL and insert.

  • sorry I am layman on the subject as well as "format sql" ?

  • 2

    Mount the INSERT INTO tabela(campo) VALUES('[email protected]')

  • 4

    Or you can use phpmyadmin see those answers

  • 1

    Thank you very much guy, solved my problem completely, saved me hours and hours hahaha

Show 2 more comments

1 answer

9

Saves this list as csv and imports via phpmyadmin or Heidisql.

phpmyadmin:

1 - In excel, save the spreadsheet as csv

2 - Open phpmyadmin and select the table where you wanted to enter the data

3 - select import tab

4 - Load the csv file, change the encoding to ISO-8859-1 the format should be CSV and "Columns separated with:" should be filled as ";"

PHP

Heidosql:

1 - In excel, save the spreadsheet as csv

2 - Open phpmyadmin and select the table where you wanted to enter the data

3 - Go to Tools->Import CSV file

4 - In the window that opened select the file and encoding and click import

inserir a descrição da imagem aqui

  • 3

    Explain the procedure better

  • 1

    Sorry for my displeasure Marlon

  • 1

    Very good Rafael, I liked, I did not know that you could do this directly by phpmyadmin, even if already solved my problem, I appreciate the explanation, will be useful for new people who are also with the same problem.

  • Thanks Victor, Yes, this method has already helped me a lot, I hope it helps the guys.

  • In the case of phpMyAdmin, I have to have a table in excel equal to the table in the database or I can import only one column from the table, for example?

  • You solved my problem, thank you.

  • 1

    @Marianaferreira Mariana, in this case the columns of your csv have to be in the same order and quantity as the target table. Through Heidi it is possible to ignore some columns. These two methods are a user-friendly interface to the LOAD DATA INFILE command, if you need to correct some data or ignore some columns look for more about this function that you can run directly. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Show 2 more comments

Browser other questions tagged php mysql sql excel

You are not signed in. Login or sign up in order to post.