PHP script to open CSV files directly, with no need to submit formulas

Question

PHP script to open CSV files directly, with no need to submit formulas

Navigation

#1 by (4 votes)
#2 by (0 votes)

1

I'm developing a PHP script for database upgrade. Basically I will schedule the execution of the script on the client machine (via the scheduler), it will open a URL on the server and perform the update of the database.

On the server it has a folder (bases), there will have several CSV files. I need the script to read the folder and get all the CSV files put into an array, open one by one and add the records into the database.

I ask for this help, because I do not even know how to search the internet. I know it has the command dir in PHP that reads the folder. But I can not find anything that reads the folder, search only the * csv files.

If someone can at least give me a north ...

    $files = glob('*.csv');
    $files = var_dump($files);
    $row = 1;
    echo $files;

    if (($handle = fopen("$files", "r")) !== FALSE) {

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $num = count($data);
            echo "<p> $num campos na linha $row: <br /></p>\n";
            $row++;
            for ($c=0; $c < $num; $c++) {
                echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

php upload csv

asked by anonymous 25.01.2018 / 12:14

2 answers

Vue 2: Get input value without using v-model Displaying welcome message on navbar!

score 4 · Answer 1

You can use the glob () function to search for CSV files.

$files = glob('*.csv');
var_dump(files);

Output:

array(3) {
  [0]=>
  string(8) "test.csv"
  [1]=>
  string(9) "test1.csv"
  [2]=>
  string(9) "test2.csv"
}

Then you can use the fopen () function and fgetcsv () to open the file and insert it into its database.

<?php
$row = 1;
if (($handle = fopen("teste.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num campos na linha $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?>

Soon after this be sure to give fclose () to close the file.

score 0 · Answer 2

Friends, thanks for the help, I managed to solve it, I'll leave my code below to help ...

<?php

$tempo = 0;

set_time_limit($tempo);
date_default_timezone_set("America/Sao_Paulo");

$data_inclusao_bd2 = date("Y/m/d H:i:s",time());
$data_inclusao_bd = date("Y/m/d");
$hora_inclusao_bd = date("H:i:s",time()); 

include "../bd.php";

$files = glob('*.csv');

var_dump($files);

$row = 1;

$t = 0;

foreach($files as $key => $value)
    {       
        $headerLine = true;

        if (($handle = fopen($files[$t], "r")) !== FALSE) 
        {

            $nome = $files[$t];

            //tratamento para o nome do arquivo e localizar cliente
            $loja_dw_    = explode("_", $nome);
            $loja_dw     =  $loja_dw_[0];

            $sql_loja = "Select * from tb_lojas WHERE id = $loja_dw";
            $acao_loja = mysql_query($sql_loja, $conecta);

            while($linha_loja = mysql_fetch_assoc($acao_loja))
            {

                $id_loja                =   $linha_loja["id"];
                $loja_cliente           =   $linha_loja["loja"];
                $id_clientes            =   $linha_loja["id_tb_clientes"];

            }

            $sql_cli = "Select id, cliente FROM tb_clientes WHERE id = $id_clientes";
            $acao_cli = mysql_query($sql_cli, $conecta);

            while ($linha_cli = mysql_fetch_assoc($acao_cli))
            {

                $cliente_dw         =   $linha_cli["cliente"];


            }

            //Abre o arquivo CSV

            while ($linha = fgetcsv ($handle, 2048, ","))
            {       

                if($headerLine)
                {
                    $headerLine = false;
                }

                else
                {   
                    //Função para analisar timestamp do CSV

                    $timestamp_calc = $linha[1]; // Salva o timestamp atual numa variável
                    //Funções de timestamp
                    $timestamp_reg = date('d/m/Y H:i:s', $timestamp_calc);
                    $data_reg = date('Y/m/d', $timestamp_calc);
                    $hora_reg = date('H:i:s', $timestamp_calc);
                    $turno_reg = date('H', $timestamp_calc);


                    switch($turno_reg)
                {
                    case 1:
                        $turno = "Noite";
                        break;

                    case 2:
                        $turno = "Noite";
                        break;

                    case 3:
                        $turno = "Noite";
                        break;

                    case 4:
                        $turno = "Noite";
                        break;

                    case 5:
                        $turno = "Noite";
                        break;

                    case 6:
                        $turno = "Manhã";

                    case 7:
                        $turno = "Manhã";
                        break;

                    case 8:
                        $turno = "Manhã";

                    case 9:
                        $turno = "Manhã";
                        break;

                    case 10:
                        $turno = "Manhã";

                    case 11:
                        $turno = "Manhã";
                        break;

                    case 12:
                        $turno = "Manhã";
                        break;

                    case 13:
                        $turno = "Tarde";
                        break;

                    case 14:
                        $turno = "Tarde";
                        break;

                    case 15:
                        $turno = "Tarde";
                        break;

                    case 16:
                        $turno = "Tarde";
                        break;

                    case 17:
                        $turno = "Tarde";

                    case 18:
                        $turno = "Tarde";
                        break;

                    case 19:
                        $turno = "Noite";

                    case 20:
                        $turno = "Noite";
                        break;

                    case 21:
                        $turno = "Noite";
                        break;

                    case 22:
                        $turno = "Noite";
                        break;

                    case 23:
                        $turno = "Noite";
                        break;

                    case 24:
                        $turno = "Noite";
                        break;

                    case 0:
                        $turno = "Noite";
                        break;

                }

                //validações de classe através da coluna age
                if ($linha[3] < 15)
                {
                    $classe = "Criança";
                }


                if ($linha[3] >=15 && $linha[3] <20) 
                {
                    $classe = "Adolencente";

                }

                if ($linha[3] >=20 && $linha[3] <30)
                {
                    $classe = "Jovem";

                }

                if ($linha[3] >=30 && $linha[3] <45)
                {
                    $classe = "Adulto";

                }

                if ($linha[3] >=45 && $linha[3] <60)
                {
                    $classe = "Senior";
                }

                if ($linha{4} <= (-1))
                {
                    $sexo = "Homens";

                }

                if($linha[4] >= 1)
                {
                    $sexo = "Mulheres";

                }

                //calculo de attention

                $att_cal = $linha[15] / 1000;

                       $sql = "INSERT INTO tb_dw (frame,_timestamp,_id,age,gender,african,asian,caucasian,hispanic,pitch,yaw,roll,viewing,headGaze_x,headGaze_y,attention,interest,mood,happiness,surprise,anger,disgust,fear,sadness,face_x,face_y,face_width,face_height,left_eye_x,left_eye_y,right_eye_x,right_eye_y,cliente,id_loja,loja_cliente,classe,sexo,calc_attent,timestamp_reg,data,hora,periodo,data_input_dw,hora_input_dw,data_hora_dw)


                                                                   VALUES ('$linha[0]',
                                                                           '$linha[1]',
                                                                           '$linha[2]',
                                                                           '$linha[3]', 
                                                                           '$linha[4]', 
                                                                           '$linha[5]',
                                                                           '$linha[6]', 
                                                                           '$linha[7]', 
                                                                           '$linha[8]',  
                                                                           '$linha[9]', 
                                                                           '$linha[10]', 
                                                                           '$linha[11]', 
                                                                           '$linha[12]', 
                                                                           '$linha[13]', 
                                                                           '$linha[14]', 
                                                                           '$linha[15]', 
                                                                           '$linha[16]', 
                                                                           '$linha[17]', 
                                                                           '$linha[18]', 
                                                                           '$linha[19]', 
                                                                           '$linha[20]', 
                                                                           '$linha[21]', 
                                                                           '$linha[22]', 
                                                                           '$linha[23]', 
                                                                           '$linha[24]', 
                                                                           '$linha[25]', 
                                                                           '$linha[26]', 
                                                                           '$linha[27]',
                                                                           '$linha[28]', 
                                                                           '$linha[29]', 
                                                                           '$linha[30]', 
                                                                           '$linha[31]',
                                                                           '$cliente_dw',
                                                                           '$id_loja',
                                                                           '$loja_cliente',
                                                                           '$classe',
                                                                           '$sexo',
                                                                           '$att_cal',
                                                                           '$timestamp_reg',
                                                                           '$data_reg',
                                                                           '$hora_reg',
                                                                           '$turno',
                                                                           '$data_inclusao_bd',
                                                                           '$hora_inclusao_bd',
                                                                           '$data_inclusao_bd')";

                       $result = mysql_query($sql) or die(mysql_error());

                    }
            }

        }

        $t = $t + 1;    

        fclose($handle);
    }

echo "<br><font color='#999999' face='arial' size='2'>Arquivo Carregado com sucesso. Aguarde a leitura dos dados...</font>";

?>