Home  »  Importar XLS Para MySQL Via PHP E phpMyadmin

Importar XLS Para MySQL Via PHP E phpMyadmin

Você já teve a necessidade de importar informações de um ficheiro elaborado em “.XLS” para um banco de dados MySQL?

Se você já fez isso, certamente sabe que pode ser realizado através do phpMyadmin facilmente, correto?

Existem também alguns softwares que fazem isso de forma automática, mas o ideal é criarmos uma solução que possa nos atender!

Como de costume, estaremos elaborando a matéria e disponibilizando ao final o conteúdo para verificação.

Importar Através Do phpMyadmin

Como falamos acima, esta é a maneira mais prática de se converter um ficheiro feito no Excel (“.xls”) para o MySQL.

Para efetuar este procedimento, é só seguir os passos a seguir:

  • Abra o Microsoft Excel e gravar o arquivo (que está em “.XLS”) como “.CSV”.
  • Crie a tabela da base de dados usando o phpMyADMIN ou outro programa de sua escolha.
  • Depois de ter criado a tabela, clique no link da tabela e aparecerá um novo link que diz “Inserir arquivo texto na tabela”.
  • Vá ao link, escolha o ficheiro “.CSV” que você criou, configure as opções e importe os dados para a tabela.
  • Para finalizar, basta fazer um “dump” da tabela, com estrutura e dados, para um ficheiro.

Prático, não?

Bem… se você achou complicado e quer facilitar seus trabalhos, preparamos uma solução mais simples!

Só lembrando antes de darmos início: A solução a seguir funcionará perfeitamente em ambiente Windows.

No Linux ele dá erro… Eu vasculhei a Internet a procuraa de uma solução que importe o XLS para CSV (em Linux), mas não vi nada que funcionasse.

Se você tiver idéia e quiser contribuir, é só me informar.

Criar As Tabelas Na Base de Dados

Antes de partirmos para a programação, vamos criar uma tabelinha simples, só para rodarmos a solução que estamos a criar.

Para isso, pegue o conteúdo do ficheiro script-sql.txt (disponível em anexo) e cole seu conteúdo no Programa utilizado para gerenciamento da base de dados.

Em nossas matérias utilizamos como padrão o MySQLFront ou o PHPMyAdmin. Caso tu também esteja utilizando outra solução é só colar o conteúdo no SQL Editor (conforme código abaixo).





----- CRIA A TABELA CARROS -----



CREATE TABLE `tbl_carros` (

  `id_carro` int(11) NOT NULL AUTO_INCREMENT,

  `modelo` text,

  PRIMARY KEY (`id_carro`)

);

Após criar a tabela acima, você poderá configurar o script de conexão à base de dados.

Configurar O Script De Conexão

Para efetuar a configuração devida, acesse o script “ConectaBanco.class.php” e siga o exemplo a seguir:



<?php



class ConectaBanco{



var $host = "SERVIDOR"; // Nome do Servidor

var $user = "USUARIO";  // Usuário do MySQL

var $pass = "SENHA";    // Senha do Usuário MySQL

var $base = "BASEDEDADOS"; // Base de Dados MySQL

var $link = "";



function ConectaBanco(){

$this -> Conectar();

}



function Conectar(){

$this -> link = mysql_connect($this -> host, $this ->user, $this ->pass);



if (!$this ->link){

die ("Erro ao conectar.");



}elseif (!mysql_select_db($this ->base, $this ->link)){

die ("Erro ao selecionar banco.");



} // Fecha }elseif (!mysql_select_db($this ->base, $this ->link)){

} // Fecha function Conectar(){



// Fecha class ConectaBanco{

?>

Feita as configurações devidas, vamos analisar o ficheiro “index.php”.

Ficheiro index.php – Para Ambiente Windows

Este é o ficheiro principal que terá por finalidade importar suas informações ao MySQL.

Até para facilitar sua utiização, dividimos o ficheiro em 3 partes:

1 – Esta parte do código seleciona o ficheiro em “XLS” no computador e efetua seu envio ao diretório onde estão os ficheiros (conforme imagem e código a seguir).



 <html>

 <head>

 <title>WebMaster.PT :: Importando Conte&uacute;do do XLS ao MySQL Atrav&eacute;s do PHP</title>

 <style type="text/css">

<!--

.style2 {

  font-size: 24px;

  font-weight: bold;

}

.style3 {font-size: 12px}

-->

 </style>

 </head>

 <body bgcolor="#ffffff" text="#000000" id=all>

 <form enctype="multipart/form-data" action="<? echo "$PHP_SELF"; ?>" method=POST>

 <div align="center">

 <p><span class="style2">WEBMASTER.PT </span><br>

   <span class="style3">IMPORTANDO CONTE&Uacute;DO DO XLS PARA O MYSQL ATRAV&Eacute;S DO PHP <br>

   <br>

   <br>

   <br>

   </span></p>

 <table border="0" cellpadding="5" cellspacing="5" width="100%" align="center">

 <tr>

 <td width="354" align="left" valign="top"><div align="right">ARQUIVO </div></td>

 <td width="600" valign="top"><input name="arquivo" type="file">

   <input type="submit" name="submit" value="Upload">   <br></td>

 </tr>

 <tr>

 <td colspan="2" align="center">&nbsp;</td>

 </tr>

 </table>

 </div>

 </form>

 </body>

 </html>

2 – Recebe o ficheiro em XLS e faz tratamento (conforme código a seguir).



$uploaded = $_FILES['arquivo'];



function trocar_acentos ($uploaded){

    $uploaded = str_replace(' ','_',$uploaded);

        $uploaded = str_replace('á','a',$uploaded);

    $uploaded = str_replace('Á','a',$uploaded);

        $uploaded = str_replace('à','a',$uploaded);

    $uploaded = str_replace('À','a',$uploaded);

        $uploaded = str_replace('é','e',$uploaded);

    $uploaded = str_replace('É','e',$uploaded);

        $uploaded = str_replace('í','i',$uploaded);

    $uploaded = str_replace('Í','i',$uploaded);

        $uploaded = str_replace('ó','o',$uploaded);

    $uploaded = str_replace('Ó','o',$uploaded);

        $uploaded = str_replace('ú','u',$uploaded);

    $uploaded = str_replace('Ú','u',$uploaded);

        $uploaded = str_replace('(','',$uploaded);

    $uploaded = str_replace(')','',$uploaded);

        $uploaded = str_replace('ç','c',$uploaded);

    $uploaded = str_replace('Ç','c',$uploaded);

        $uploaded = strtolower($uploaded);

    return $uploaded;

}



    $uploaded = trocar_acentos ($_FILES['arquivo']['name']);

        $uploaded_extensao = substr($uploaded,strpos($uploaded,'.')+1,strlen($uploaded)-strpos($uploaded,'.'));

        $imagem_destaque_tamanho = $_FILES['uploaded']['size'];

        $imagem_destaque_descricao = $_POST['uploaded'];

        $imagem_destaque_data = date('d/m/Y');

        $uploaddir = "../xls_mysql/";



    $data = mktime();



        if ($uploaded != ""){

    if (file_exists($uploaddir.$uploaded)){ $uploaded = mktime()."_".$uploaded; }



        move_uploaded_file($HTTP_POST_FILES['arquivo']['tmp_name'], $uploaddir . $uploaded);

    }else{

        $uploaded = "";

        }



    require_once 'Excel/reader.php';

    $excel = new Spreadsheet_Excel_Reader();

    $excel->setOutputEncoding('CP1251');

    $excel->read($uploaded);

    $x=1;

    $sep = ",";

    ob_start();

    while($x<=$excel->sheets[0]['numRows']) {

     $y=1;

     $row="";

     while($y<=$excel->sheets[0]['numCols']) {

         $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';

         $row.=($row=="")?"\"".$cell."\"":"".$sep."\"".$cell."\"";

         $y++;

     }

     echo $row."\n";

     $x++;

    }

OBServações:

No “require_once” estaremos chamando o diretório “Excel”.

Este diretório contém ficheiros que tratam o XLS antes de iniciar a exportação para “CSV”.

Na internet ele pode ser encontrado facilmente. Basta você digitar no Google “phpExcelReader.zip” (existem vários sites oferecendo este ficheiro).

Na variável “$uploaddir” apontei para o diretório que estamos trabalhando. Faça a alteração para o caminho correto em seu Servidor.

3 – Altera conteúdo de XLS para CSV (conforme código a seguir).





$fp = fopen($uploaded.".csv",'w');

fwrite($fp,ob_get_contents());

fclose($fp);

ob_end_clean();



$conectar = new ConectaBanco();



$tabela = "tbl_carros";



$handle = file($arquivocsv);



for ($i=1;$i<count($handle);$i++){

$valori = explode(";", $handle[$i]);

$values = "";

foreach ($valori as $val){

$val=trim($val);

if (eregi("NULL",$val) == 0)

$values .= "'".addslashes($val)."',";



else



$values .= "NULL,";

} // fecha foreach ($valori as $val){



$values = trim(substr($values,0,-1));



$query = "INSERT INTO $tabela VALUES (null, ".trim($values).");";

$resultado = mysql_query($query);



} // fecha for ($i=1;$i<count($handle);$i++){

Veja que nomeamos o ficheiro “.csv” com o nome da variável “$uploaded.csv”.

Caso você queira verificar, foi criado um ficheiro com o mesmo nome neste diretório.

4 – Cadastramos o conteúdo exportado anteriormente de XLS para CSV no Banco de Dados MySQL (conforme código abaixo).





for ($i=1;$i<count($handle);$i++){

$valori = explode(";", $handle[$i]);

$values = "";

foreach ($valori as $val){

$val=trim($val);

if (eregi("NULL",$val) == 0)

$values .= "'".addslashes($val)."',";



else



$values .= "NULL,";

} // fecha foreach ($valori as $val){



$values = trim(substr($values,0,-1));



$query = "INSERT INTO $tabela VALUES (null, ".trim($values).");";

$resultado = mysql_query($query);



} // fecha for ($i=1;$i<count($handle);$i++){



?>



<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript"> alert ("importação efetuada com sucesso")</SCRIPT>

<SCRIPT language="JavaScript">window.location.href="index.php";</SCRIPT>



<?



}else{

Nesta parte do código fazemos a inserção na base de dados.

OBS.: Como trata-se apenas de um exemplo, pegamos apenas 1 tabela e preenchemos 1 linha do ficheiro XLS.

E aí, gostou da matéria?

Ah, deu o erro a seguir?

- Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client

Resposta: Faça a atualização da sua base de dados MySQL para a versão 5.x.

Ah, não posso deixar de lhe dizer que para este conteúdo funcione corretamente, você precisa fazer uso do PHP 5.0 e MySQL 5.0, fazendo uso no Windows, ok?

Espero que esta solução seja útil pra você!

Código usado neste tutorial: Importar XLS Para MySQL VIA Script

Be Sociable, Share!

16 Comentários

  1. Matheus
    Posted on 26/10/2010 at 18:51

    Apareceu a mensagem The filename carro.xls is not readable

    Reply
  3. Joao Laudir
    Posted on 25/08/2011 at 18:56

    $fp = fopen($uploaded.".csv",'rw'); e se usar rw? talvez funcione

    Reply
  4. Ana
    Posted on 20/12/2011 at 17:45

    Bem, não funciona! Houve alguma alteração?

    Reply
  5. Helio
    Posted on 28/03/2012 at 14:04

    Eu consigi importar mas ele manda caracteres estranhos

    Reply
  6. nrebe
    Posted on 05/04/2012 at 18:39

    Depois de muito trabalho e correção no codido eu consegui importar o arquivo para dentro das pastas porém não envia para o banco de dados

    Reply
  7. newber
    Posted on 06/04/2012 at 21:17

    Senhores depois de algumas correções no código ele esta copiando e alterando os arquivos perfeitamente, porém não esta gravando no banco de dados, alguem tem alguma sugestão?

    Reply
  8. arquimedes
    Posted on 21/12/2012 at 21:06

    deu o mesmo problema para mim, nao esta gravando no banco

    Reply
  9. Hebert.viana
    Posted on 20/03/2013 at 23:41

    Olá alguem conseguil? estou tentando mas não esta funcionando :( alguem me ajdua?

    Reply
  10. Quaresma
    Posted on 04/06/2013 at 19:13

    E ai galera esse código não funciona muita coisa sem sentido

    Reply
  11. Isaac
    Posted on 03/09/2013 at 15:29

    mudei essa parte e funcionou:

    $fp = fopen($uploaded.".csv",'w');
    fwrite($fp,ob_get_contents());
    fclose($fp);
    ob_end_clean();

    $conectar = new ConectaBanco();

    $tabela = "tbl_carros";

    $handle = file($uploaded.'.csv');

    Reply
  12. Marcos
    Posted on 16/06/2017 at 22:53

    "Depois de ter criado a tabela, clique no link da tabela e aparecerá um novo link que diz “Inserir arquivo texto na tabela”.

    Isso não aparece
    phpMyAdmin

    Informação da versão: 4.6.5.2

    Reply

Participa! Comenta...