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.
Advertisement
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!