How to import the data of an Excel file into Oracle table using PHP

[goodamr]

Hi,

I have many Excel files (more than 200 files). I'm using TOAD to import the Excel files into the Oracle table, but this process is boring and time-consuming especially for this huge number of files.

So, I'd like to do a PHP code which takes the path of the Excel file and imports it automatically into the Oracle table without using TOAD or any other programs.

I'm using PHP OCI8 to handle Oracle queries.

Please help me. Thanks in advance.

[Dormilich]

is your PHP host running on windows?

usually, as Excel is a propriatary file format and most PHP hosts can’t run that, you need some kind of file conversion before that (e.g. xls => csv)

[goodamr]

Yes, My PHP HOST is running on Windows Server 2003, IIS 6.0.

OK, after converting it to CSV, how can I read it?

Thanks alot for replying.

[Dormilich]

on your server you can try to directly load Excel files (via COM), though I have no experience with it (lack of windows)

[pa5ha]

You can also do this with PHPExcel. Please find the code below which works fine to me.
The code below does not contain a file browser and PHPExcel.php
This can be automated to your system by making loop with reading number of files in a folder

<?php
 
/* 
Author: Rajan Maharjan
Website: http://rznmzn.com | http://rajanmaharjan.com.np
Date: 2010 December, 25
*/
include "PHPExcel.php";
$ext = pathinfo($_FILES['file_name']['name'], PATHINFO_EXTENSION);
$newFileName = (microtime() * pow(10,8)).".".$ext;
 
if(! isset($_FILES['file_name']['tmp_name'])){
    $_SESSION['error_message'] = 'No file selected for upload';
    header("location:index.php");
    exit();
    }
 
else if(strtolower($ext) != 'csv' && strtolower($ext) != 'xls' && strtolower($ext) != 'xlsx'){
    $_SESSION['error_message'] = 'Invalid file format. Please try to upload CSV (Comma Separated Value) file.';
    header("location:index.php");
    exit();
    }
 
move_uploaded_file($_FILES['file_name']['tmp_name'],"csvfiles/".$newFileName);
 
$csvFileName="csvfiles/".$newFileName; //defined in the page where this is included
 
 
if(strtolower($ext) == 'xls' || strtolower($ext) == 'xlsx'){
 
    $objPHPExcel = PHPExcel_IOFactory::load($csvFileName);
 
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        $worksheetTitle = $worksheet->getTitle();
        $highestRow = $worksheet->getHighestRow(); // e.g. 10
        //$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
        //$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
 
        for ($row = 1; $row <= $highestRow; $row++) {            
            if($row==1)
                continue;
            $arrayData = array();
            $arrayData['first_field']     = $worksheet->getCellByColumnAndRow(1, $row)->getValue();            
            $arrayData['second_field']= $worksheet->getCellByColumnAndRow(2, $row)->getValue();
            $arrayData['third_field']= $worksheet->getCellByColumnAndRow(3, $row)->getValue();
            $arrayData['fourth_field']     = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
            $arrayData['fifth_field'] = $worksheet->getCellByColumnAndRow(5, $row)->getValue();            
 
 
            $object_functions->insert_to_db($arrayData); //insert queries goes here
        }
 
    }
}
else if (strtolower($ext) == 'csv'){
    $row = 1;
    if (($handle = fopen($csvFileName, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {               
            if($row++ <= 1)
                continue;        
 
            $arrayData = array();
 
            $arrayData['first_field']     = $data[1];            
            $arrayData['second_field']= $data[2];
            $arrayData['black_list_date_np']= $data[3];
            $arrayData['third_field']     = $data[4];
            $arrayData['fourth_field'] = $data[5];            
            $arrayData['fifth_field']             = $data[6];
 
            $object_functions->insert_to_db($arrayData);            
        }
 
        fclose($handle);
    }
}
unlink($csvFileName);
echo 'Your data has been imported successfully.';
?>