How to import data from a txt into MySQL with PHP?
I need to read a txt get all data and insert into mysql in a string of characters that in position zero has a code four-digit number and in position eleven the description with up to 80 characters begins.Example:
0014 ARARA DO ARIPUANA. (ARARA DO BElRADAO/ARIPUANA)
<?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>
Example:
1 - Separated by semicolon.
Layout File:
FULANO 1;1999-01-01
FULANO 2;1999-02-02
FULANO 3;1999-03-03
FULANO 4;1999-04-04
Code for insertion of this 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
Layout File: (the name being 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 insertion of this 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>