-1

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.

Nikola
  • 61
  • 8
  • Is it a tab delimited file? You can probably use fputcsv or some other similar existing library to achieve it. Done any research? – ADyson Jul 21 '21 at 08:14
  • The problem is that I only work with sql, so I have no knowledge of PHP. I adapted the code above to one template that with uploading excel file to the database, but don't have such template for txt. Yes, it is tab delimited txt file. – Nikola Jul 21 '21 at 08:18
  • 1
    Ok we're not a replacement for your basic knowledge of the language, and we're not going to sit and teach you. You haven't actually asked a question or described a problem you specifically need help. you've just stated a fairly broad requirement. To be clear, we're not a free write-my-code or do-my-thinking service. Well _help_ you with a particular programming problem you're having with _your_ attempt to achieve what you need. Please read [ask] for more guidance. If you don't have enough experience to even make a basic attempt, then hire a freelancer. – ADyson Jul 21 '21 at 08:20
  • Well, I thought that I was clear, sorry for that. My problem is, hot to adapt the code above, so that instead of uploading excel file, I can upload txt file. – Nikola Jul 21 '21 at 08:20
  • 1
    That's not a problem, it's a requirement - and a fairly large one at that. A problem occurs when you've actually tried to implement the requirement and encountered an error or bug or some other specific issue in the code. You may find people sometimes help you with small or simple things on stackoverflow without any input from you, but that isn't going to happen for something larger like this. Excel format and your text format are completely different. And we're also not going research existing solutions on your behalf. – ADyson Jul 21 '21 at 08:21
  • I'm really sorry for your frustration. You might be right, it may looked like I'm trying to take some advantage of the community, but that couldn't be further from my intention. I did some research, and edited the original question. Using the same code, I was able to upload both .xlsx and .txt files, but now I have some problem with the content. – Nikola Jul 22 '21 at 07:34

1 Answers1

1

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!

Nikola
  • 61
  • 8