CODEWITHSUNDEEP
phpmysqlcsv
Shallow_Criminal
Shallow_Criminal

Reputation: 33

Importing csv file into mysql database using php

I have a MS-excel file with a column: Address

And a MySQL database table having the columns: Add1, Add2, Add3

Is it possible to break the data in the Address column of Excel file and divide that data into Add1, Add2, Add3 (With Limit of 40 character each column) After uploading the MSExcel file through PHP!

My Code For uploading the file in PHP: 

if(isset($_POST['submit'])) {
 if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
    $allowedExtensions = array("xls","xlsx","csv");
    $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);
    if(in_array($ext, $allowedExtensions)) {
       $file_size = $_FILES['uploadFile']['size'] / 1024;
       if($file_size < 50) {
           $file = "../excelfile/".$_FILES['uploadFile']['name'];
           $isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
           if($isUploaded) {

                include("../Classes/PHPExcel/IOFactory.php");
                try {
                    //Load the excel(.xls/.xlsx/.csv) file
                    $objPHPExcel = PHPExcel_IOFactory::load($file);
                } catch (Exception $e) {
                     die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                }

                //An excel file may contains many sheets, so you have to specify which one you need to read or work with.
                $sheet = $objPHPExcel->getSheet(0);
                //It returns the highest number of rows
                $total_rows = $sheet->getHighestRow();
                //It returns the highest number of columns
                $total_columns = $sheet->getHighestColumn();
                $sheet_data = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

And the Query for inserting the address into the table is:

$query = "insert into address_table (Add1, Add2, Add3)
    values(
    '".$_POST['address1']."',
    '".$_POST['address2']."',
    '".$_POST['address3']."')

Upvotes: 2

Views: 915

Answers (1)

Evil_skunk
Evil_skunk

Reputation: 3392

I need to make some estimations because I don't have your Excel File.

Imagine your $sheet_data now looks like that

Array
(
    [0] => Array
        (
            [0] => Street1
        )

    [1] => Array
        (
            [0] => Street2
        )

    [2] => Array
        (
            [0] => Street3 with a long name, more than 40 characters
        )

)

Each Array-Element in the 1st level representing one line - each element in the 2nd level contains the corresponding columns.

foreach ($sheet_data as $line){
    //maybe you need to skip the first line because it could contain just some headers
    //in my estimation $line[0] contains the address
    $address = $line[0];

    //split address after 40 characters
    $split = str_split($address, 40);

    //$conn - mysqli connection
    $stmt = $conn->prepare("INSERT INTO address_table (Add1, Add2, Add3)
    VALUES (?, ?, ?)");

    $addr1=""; $addr2=""; $addr3="";
    if(isset($split[0]) $addr1 = $split[0];
    if(isset($split[1]) $addr2 = $split[1];
    if(isset($split[2]) $addr3 = $split[2];

    //fill query params params with values
    $stmt->bind_param("sss", $addr1, $addr2, $addr3);

    $stmt->execute();
}

For the 3rd line - table entry would be filled with

Add1 => Street3 with a long name, more than 40 c
Add2 => haracters

One thing you still need to check - if address has more than 120 characters the additional characters are lost atm.

And please use prepared statements like in my example - when you fill your query directly with variables you are vulnerable to SQL-Injection

Upvotes: 2

Related Questions