CODEWITHSUNDEEP
phpmysqlcsv
AWA
AWA

Reputation: 303

Read first row from csv file and create table according to it(csv file fields)

Read first row from csv file and create table automatically according to it(csv file fields) in mysql. Looking for PHP script? I have tried this 

 <?php

$arr = array(array(),array());
$num = 0;
$row = 0;
$handle = fopen("./contacts.csv", "r");


while($data = fgetcsv($handle,1000,",")){   
    $num = count($data);
    for ($c=0; $c < $num; $c++) {
            $arr[$row][$c] = $data[$c];
    }
    $row++;
}


$con = mysql_connect('localhost','root','');
mysql_select_db("excel_database",$con);

for($i=1; $i<$row; $i++){
$sql = "INSERT INTO contacts VALUES ('".$arr[$i][0]."','".$arr[$i][1]."','".$arr[$i][2]."','".$arr[$i][3]."','".$arr[$i][4]."','".$arr[$i][5]."')";
mysql_query($sql,$con);
}

?>

Upvotes: 4

Views: 16532

Answers (5)

Miguel Q
Miguel Q

Reputation: 3628

If you want to visualize the table data, in html, you can create a table using PHP and data from csv reading the first line as headers. I use this function that you can try it!

function build_table($source){
$data = array();
$file = fopen($source, 'r');
while (($result = fgetcsv($file,0,";")) !== false){$data[] = $result;}
fclose($file);


//Now process the data and create table
$data_rows=@count($data[0]); //from the first row get the header name
$colum_trail="";
//Create the header part
for($i=0; $i<$data_rows;$i++){
$cell=$data[0][$i]; //each header
$colum_trail.="<th>$cell</th>";    
}

////Create the table body contents     
$table_cells="";

for($i=1; $i<count($data);$i++){
$table_cells.="<tr>";       
$cell=$data[$i];

foreach($cell as $cell_value){
$cell_v=$cell_value;
$table_cells.="<td>$cell_v</td>";
}
$table_cells.="</tr>";
}

$table="<table><tr>$colum_trail</tr>$table_cells</table>";
echo "<style>td {border-bottom: 2px solid black;}</style>";
echo $table;
}

Then all you need is to after that code, call that function like this:

build_table("source.csv");  //then call the file you want to create table

Upvotes: 2

Kermes
Kermes

Reputation: 41

Modifiying your script a bit, this should do the trick and create the table according to the csv header line:

    $sql[] = array();
    $handle = fopen($file, "r");

    $header = fgetcsv($handle,1000,",");
    if($header){
        $header_sql = array();
        foreach($header as $h){
            $header_sql[] = '`'.$h.'` VARCHAR(255)';
        }
        $sql[] = 'CREATE TABLE `imported_csv` ('.implode(',',$header_sql).')';

        while($data = fgetcsv($handle,1000,",")){   
            $sql[] = "INSERT INTO imported_csv VALUES ('".implode("','",$data)."')";
        }
    }        

    $con = mysql_connect('localhost','root','');
    mysql_select_db("excel_database",$con);
    foreach($sql as $s){
        mysql_query($s,$con);
    }

Upvotes: 4

bzbz
bzbz

Reputation: 182

This is half way to your question.

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

            }
        }

        fclose($handle);
    }
    ?>

Upvotes: 4

Michael Greisman
Michael Greisman

Reputation: 410

Here's a single function to give you an array: fgetcsv See Example #1 on that page for a basic script.

The table will just be a matter of looping over the contents ...

Aha, I finally parsed "table automatically according to it in mysql" and understood. Sorry. fgetcsv will return an array; just use that array to create your sql command. e.g. '...values (' . implode(',', $results_of_fgetcsv) . ')...'

Upvotes: 3

Friedrich
Friedrich

Reputation: 2290

whith array_shift(file('filename.csv')) will give you the first line of the document filename.csv

Upvotes: 4

Related Questions