How to import data from a txt to Mysql with php?

Asked

Viewed 7,712 times

2

How to import data from a txt for Mysql with PHP?

Explanation: I need to read a txt take all the data and insert it into mysql in a string that at zero position has a four-digit code and at eleven position starts the description with up to 80 characters.

Example:

0014 ARARA DO ARIPUANA. (ARARA DO BElRADAO/ARIPUANA)
  • 1

    @Brasofilo suggest you transcribe the comment there, because it took me a long time to realize that it was link, and that you were not simply agreeing with the previous comment (that was deleted). I liked the comment there, but I only saw it because it was the only one left. (<Joke>comments that contains only links can become obsolete if the original answer is deleted. To learn more read....</Joke> :P ) . then delete this one.

2 answers

4

Example:

1 - Separated by point and comma.

File layout:

FULANO 1;1999-01-01
FULANO 2;1999-02-02
FULANO 3;1999-03-03
FULANO 4;1999-04-04

Code for entering such data

<?php
    function Inserir($itens, Pdo $pdo){
        $sts = $pdo->prepare("INSERT INTO dados(nome, data) VALUES(?,?);");
        $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
        $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
        $sts->execute();
        $sts->closeCursor();
        $sts = NULL;
    }
    if (!empty($_FILES['arquivo']))
    {
        $Pdo     = new PDO("mysql:host=localhost;dbname=testdb", "root", "senha");
        $file    = fopen($_FILES['arquivo']['tmp_name'], 'r');
        while (!feof($file)){
            $linha = fgets($file);          
            $itens = explode(';', $linha);          
            Inserir($itens, $Pdo);
        }
    }

?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Arquivo</title>
</head>
<body>
    <form action="<?php echo $_SERVER['PHP_SELF'];?>" enctype="multipart/form-data" method="post">
        <input type="file" name="arquivo" id="arquivo">
        <input type="submit" name="enviar" value="Enviar">
    </form>
</body>
</html>

2 - Separated by spaces

File Layout: (where the name comprises 60 spaces (0.60) and the date plus 10 (60.70)

FULANO 1                                                    1999-01-01
FULANO 2                                                    1999-02-02
FULANO 3                                                    1999-03-03
FULANO 4                                                    1999-04-04

Code for entering such data

<?php
    function Inserir($itens, Pdo $pdo){
        $sts = $pdo->prepare("INSERT INTO dados(nome, data) VALUES(?,?);");
        $sts->bindValue(1, $itens[0], PDO::PARAM_STR);
        $sts->bindValue(2, $itens[1], PDO::PARAM_STR);
        $sts->execute();
        $sts->closeCursor();
        $sts = NULL;
    }
    if (!empty($_FILES['arquivo']))
    {
        $Pdo     = new PDO("mysql:host=localhost;dbname=testdb", "root", "senha");
        $file    = fopen($_FILES['arquivo']['tmp_name'], 'r');
        while (!feof($file)){
            $linha = fgets($file);      
            $itens = array(trim(substr($linha, 0, 60)), trim(substr($linha, 60, 70)));
            Inserir($itens, $Pdo);
        }
    }   
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Arquivo</title>
</head>
<body>
    <form action="<?php echo $_SERVER['PHP_SELF'];?>" enctype="multipart/form-data" method="post">
        <input type="file" name="arquivo" id="arquivo">
        <input type="submit" name="enviar" value="Enviar">
    </form>
</body>
</html>

2


<?php
set_time_limit(0);//zera o limite de tempo
$con=mysql_connect('localhost','root','root');
mysql_select_db('raas',$con);

if($_POST){
$count=0;

$tmp_name = $_FILES["arquivo"]["tmp_name"];//recebe o arquivo para ser manipulado
$arquivo = $tmp_name;
$ler=file($arquivo);//abre o arquivo
$total=count($ler); //contas total de linhas

foreach ($ler as $linha){//pega todas as linhas do arquivo e joga em uma variavel criando um array

    $etnia = trim(substr($linha,0,100));

    $sql =mysql_query("INSERT INTO etnia (id, descricao) VALUES ('000000','$etnia')");
    $count++;

    $largura=(($count*100)/$total);//calcula andamento da operação em percentual
   $perc=(int)($largura);

   echo "<div style='width:300px; height:20px; background:#fff; border:1px solid #f00; position:absolute; top:55px; left:10px'>
      <div style='width:$largura%; height:20px; background:#f00; position:absolute; top:0; left:0'></div>
   </div>

   <div style='width:100px; height:20px; background:#fff; position:absolute; top:95px; left:155px'>$perc</div>
   ";

}
if($sql){
    echo $count."registros importados";
}

}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Importa txt</title>
</head>

<form action="<?php echo $PHP_SELF?>" method="POST" enctype="multipart/form-data">
    <input type="hidden" name="MAX_FILE_SIZE" value="100000000000" />
    <input name="arquivo" type="file" />
    <input type="submit" value="Atualizar" />
</form>

<body>
</body>
</html>
  • Where is the part that separates the code from the description?

Browser other questions tagged

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