Upload txt file using PHP into database and then return Excel file

What I need to do is upload txt file into databe using PHP, do some calculations based on that table, and then return result in Excel file.

This code works well, but I’m not getting all data from the .txt file, since it looks like it stops when it finds a comma.

Here is the code:

  require_once('connection.php.inc');
  require_once 'Classes/PHPExcel/IOFactory.php';
  require_once 'Classes/PHPExcel.php';

  function HeaderingExcel($filename) {
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=$filename" );
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
      header("Pragma: public");
      }

//-------upload---------------------------------------
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');

if (!file_exists($_FILES['excel_fajl']['tmp_name'])) {
    exit("Nije dobar fajl!");
}

$file =  $_FILES['excel_fajl']['tmp_name'];

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '128MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);

$inputFileType = PHPExcel_IOFactory::identify($file);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($file);
$objPHPExcel-> setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

//kreiranje privremene tabele koju punimo:
$query1="create temp table visa_tc
(sequence char(170))
with no log";
odbc_exec($conn,$query1);
    
$i=1;
   foreach ($sheet->getRowIterator() as $row) {
    $sequence  = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
    $query1 = "insert into visa_tc values ('".$sequence."')";
    odbc_exec($conn,$query1);
$i++;
      unset($sequence); 
    
  }
//--------kraj upload-a-------------------------------------
$query = "
SELECT sequence[17,20] Reason_code, sequence[24,27] Event_date, sequence[47,58]/100 dest_amount, sequence[59,61] destination_currency, 
       sequence[62,73]/100 source_amount, sequence[74,76] source_currency, sequence[77,146] message_text, 
       round(sequence[62,73]/sequence[47,58], 5) eur_usd, round(sequence[47,58]/sequence[62,73], 5) usd_eur
FROM visa_tc
WHERE sequence[17,20] <> ''";
$odbc_result = odbc_exec($conn,$query);


$excel = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $excel->load('./TC_Template.xlsx'); // Empty Sheet

$excel->setActiveSheetIndex(0);

$i=3;
      while (odbc_fetch_row($odbc_result))
      {
        $excel->getActiveSheet(0)->getCellByColumnAndRow(1,  $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,1)), PHPExcel_Cell_DataType::TYPE_STRING);
        $excel->getActiveSheet(0)->getCellByColumnAndRow(2,  $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,2)), PHPExcel_Cell_DataType::TYPE_STRING);
        $excel->getActiveSheet(0)->getCellByColumnAndRow(3,  $i)->setValue(odbc_result($odbc_result,3));;
        $excel->getActiveSheet(0)->getCellByColumnAndRow(4,  $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,4)), PHPExcel_Cell_DataType::TYPE_STRING);
        $excel->getActiveSheet(0)->getCellByColumnAndRow(5, $i)->setValue(odbc_result($odbc_result,5));
        $excel->getActiveSheet(0)->getCellByColumnAndRow(6, $i)->setValueExplicit(iconv('ISO-8859-2', 'utf-8//TRANSLIT',odbc_result($odbc_result,6)), PHPExcel_Cell_DataType::TYPE_STRING);
        $excel->getActiveSheet(0)->getCellByColumnAndRow(7, $i)->setValue(odbc_result($odbc_result,7));
        $excel->getActiveSheet(0)->getCellByColumnAndRow(8, $i)->setValue(odbc_result($odbc_result,8));
        $excel->getActiveSheet(0)->getCellByColumnAndRow(9, $i)->setValue(odbc_result($odbc_result,9));
        
                
                $i++;
        }

$k =$i+1;
  
$excel->getActiveSheet(0)->getCellByColumnAndRow(2, $k)->setValue('TOTAL:');
$excel->getActiveSheet(0)->SetCellValue('D'.$k,"=SUM(D3:D".($k-1).")");
$excel->getActiveSheet(0)->SetCellValue('F'.$k,"=SUM(F3:F".($k-1).")");

$filename = 'TC_VISA.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
    
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
exit;


       odbc_close($conn);
?>        

And here is the content of the .txt file that has to be uploaded to the table visa_tc

10004083524334965010   06150000000000000000000000000000343978000000000415840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0004, 408357            0611166479225087 11660

10004083524334965010   06150000000000000000000000000001845978000000002231840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0005, 408358            0611166479225088 11660

10004083524334965010   06150000000000000000000000000003581978000000004330840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0012, 1000659089421357  0611166479225095 11660

10004083524334965010   06150000000000000000000000000010337978000000012500840VGBP BILLING FOR  JUN 2021, INV 210601-07684- 0001, 10064776499       0611166479232817 11660

10004083524334965010   06150000000000000000000000000014208978000000017180840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0006, 421357            0611166479225089 11660

10004083524334965010   06150000000000000000000000000022773978000000027537840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0008, 1000324233408353  0611166479225091 11660

10004083524334965010   06150000000000000000000000000041522978000000050208840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0002, 408352            0611166479225085 11660

10004083524334965010   06150000000000000000000000000138358978000000167303840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0009, 1000328311408358  0611166479225092 11660

10004083524334965010   06150000000000000000000000000153530978000000185649840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0011, 1000506582408357  0611166479225094 11660

10004083524334965010   06150000000000000000000000000434600978000000525518840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0007, 1000324232408352  0611166479225090 11660

10004083524334965010   06150000000000000000000000000814400978000000984773840VGBP BILLING FOR  MAY 2021, INV 210500-13239- 0013, 9000366491421357  0611166479225096 11660

The output that I get for this column ‘sequence[77,146]’ from the sql query is based on example of this entry:

10004083524334965010 06150000000000000000000000000014208978000000017180840VGBP BILLING FOR MAY 2021, INV 210500-13239- 0006, 421357 0611166479225089 11660

What I should get is:

VGBP BILLING FOR MAY 2021, INV 210500-13239- 0006, 421357

But what I ged is:

VGBP BILLING FOR MAY 2021

It looks like the sequence stops when it finds comma.

I tried this remedies in the code (with preg_replace)

   foreach ($sheet->getRowIterator() as $row) {
    $sequence  = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
    $query1 = "insert into visa_tc values ('".preg_replace('/[ ,]+/',' ',$sequence)."')";
    odbc_exec($conn,$query1);
$i++;
      unset($sequence); 
    
  }

and 

$i=1;
   foreach ($sheet->getRowIterator() as $row) {
    $sequence  = trim($sheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue());
    $sequence1 = preg_replace('/[,]+/', ' ', $sequence);
    $query1 = "insert into visa_tc values ('".$sequence1."')";
    odbc_exec($conn,$query1);
$i++;
      unset($sequence); 
    
  }

Now I hope my question is more straightforward.

Thank you.

Answer

I was able to workout a solution:

    exit("Nije dobar fajl!");
}

$file =  $_FILES['excel_fajl']['tmp_name'];
$handle = fopen($file, "r");

//kreiranje privremene tabele koju punimo:
$query1="create temp table visa_tc
(sequence char(170))
with no log";
odbc_exec($conn,$query1);


while (($buffer = fgets($handle, 4096)) !== false) {

$query1 = "insert into visa_tc values ('".$buffer."')";
odbc_exec($conn,$query1);
}

So basically, only difference compared to the original code was that I added this:

$handle = fopen($file, "r");

while (($buffer = fgets($handle, 4096)) !== false) {
$query1 = "insert into visa_tc values ('".$buffer."')";
odbc_exec($conn,$query1);
}

Doesn’t seem like a fairly large reqirement to me.

Thank you!