DEV Community: Abbrevia

Data in fixed fields text files

Claudio Fior — Fri, 19 Nov 2021 15:35:48 +0000

I have to read and write data from a fixed fields text file.
What is a it? It'a mess like this.

The data organized based on the character position:
1 - 11 Packet ID
12 - 11 Anagraph ID
23 - 10 Submission date
and so on.

How can I read the data in PHP?

First, ugly solution: use substr to read

 $identificativo_pacchetto = substr($line,0,11);
 $identificativo_mandante = substr($line,11,11);
 $data_affido = substr($line,22,10);
...

and str_pad to write

 $line = str_pad($identificativo_pacchetto,11);
 $line .= str_pad($identificativo_mandante,11);
 $line .= str_pad($data_affido,10);
...

It'a nightmare!!!

Lets'try with unpack and pack command.

One line of code to read.

$data =unpack('A11identificativo_pacchetto/A11identificativo_mandante/A10data_affido',$line));

and data is an array like this

{
   "identificativo_pacchetto":"1",
   "identificativo_mandante":"52301",
   "data_affido":"19-11-2021"
}

One line of code to write

$data = ['1','52301','19-11-2021'];
$line =pack('A11A11A10',...$data));

The is no need to pad the values.
Note that the are no filed names (eg . identificativo_pacchetto) and no separators (/).

I put all together defining the structure in an array.

$def = [
'A11identificativo_pacchetto',
'A11identificativo_mandante',
'A10data_affido
];
$data = ['1','52301','19-11-2021'];
$line = pack(implode('',preg_replace('/^(A\d+).*/','$1',$def)),...$data);
$data = unpack(implode('/',$def),$line);

More info:
PHP manual
PHP Cookbook
Stackoverflow

Extract data from a PDF

Claudio Fior — Tue, 14 Apr 2020 07:25:16 +0000

One of our providers give us some data ad PDF and I have to produce a JSON object for further elaborations.

For the textual information non problem: I used pdftotext to extract the text.

$content = shell_exec('pdftotext -enc UTF-8 -layout input.pdf -');

Then I used regular expressions to extract the data

 $anagrafica=array();
 if(preg_match('/^Denominazione\W*(.*)/m', $content, $aDenominazione)) {
     $anagrafica['denominazione']=$aDenominazione[1];
 }

How to extract the data of the semaphores that are images without labels?

I used the linux command pdftohtml

$rawImages = shell_exec('pdftohtml -enc UTF-8 -noframes -stdout -xml "'.$this->filePath.'" - | grep image');
$tok = strtok($rawImages,"\r\n");
while ($tok !== false) {
    $oImage = simplexml_load_string($tok);
    $images[]=$oImage;
    $tok = strtok("\r\n");
}

The output of pdftohtml in a xml document for each text box or image.

$rawImages is an array of the xml elements of the images ans I put them as SimpleXmlObjects in $images array.

Than I searched trough the array the images with 77 pixel of width and sort the by the vertical position.

The images are saved in the current directory of the script.

I queried the color of a pixel in a specific position of the image with convert command of ImageMagick library and saved the data in the JSON object.

$color = shell_exec('convert "'.$imagePath.'" -format \'%[pixel:p{100,50}]\' info:- ');
switch ($color) {
    case 'srgb(253,78,83)':
        $anagrafica[$this::chekcs[$pos]]='red';
    break;
    case 'srgb(123,196,78)':
        $anagrafica[$this::chekcs[$pos]]='green';
    break;
    case 'srgb(254,211,80)':
        $anagrafica[$this::chekcs[$pos]]='yellow';
    break;
};

At this point: is there an easy way to do the trick?

The beautiful marriage of Python and MongoDB

Claudio Fior — Tue, 10 Apr 2018 09:05:42 +0000

I'm a PHP developer and managing big data stored with this language is not so easy.

Add data to the db is not a problem, but retrieving a summary and save it as csv is not to easy.

So this time I tried to use Python and it marvelous because:

In a moment you can create the object containing the aggregate conditions;
Saving the data as csv is easy.

#!/usr/bin/python
import pymongo
import csv
import collections
from pymongo import MongoClient
db = MongoClient().report_impresa
pipeline = [
    {
        "$group": {
            "_id": "$CompanySummary.CreditRating.CreditLimit",
            "count": {"$sum": 1},
            "min_rating": {"$min": "$CompanySummary.CreditRating.CommonValue"},
            "max_rating": {"$max": "$CompanySummary.CreditRating.CommonValue"},
            }
    },
    {"$sort": {"_id": 1}}
]
descr = db.ggs.aggregate(pipeline)
values = {}
for att in descr:
    chiave = att["_id"]
    if chiave is None:
        chiave = ""
    if chiave == "":
        values[""] = att
    else:
        values[int(chiave)] = att

with open('fido.csv', 'w') as csvfile:
    writer = csv.DictWriter(
                            csvfile,
                            fieldnames=[
                                "_id",
                                "count",
                                "min_rating",
                                "max_rating"
                                ]
                            )
    writer.writeheader()
    for k in sorted(values):
        writer.writerow(values[k])

Data exchange between Excel and a LAMP server

Claudio Fior — Mon, 19 Mar 2018 10:01:22 +0000

Premise

Since 2011 the data collected in my company is stored and managed in an Excel spreadsheet. The company has grown up and now the data in exchanged in three offices in different parts of the Nation.

Since 2013 was adopted a solution based on a VPN.

Since 2017 the Excel macros have been transformed in a VB NET application.

Not they asked to realize a web application to manage the data.

How to feed the beast (the web application)

Optimal solution

The final solution will be a web service that exchanges the data from the web application that collects the client's data and the new application that store and manage the data.

Call to a web service for every insert/update in the Excel file

What to do in the meantime while the integration is not ready?

The VB NET application calls a web service to insert and update the data.

It's an easy trick creating a REST web service.

        Dim md5 As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create()
        Dim request As System.Net.HttpWebRequest = System.Net.WebRequest.Create(Me.gatUrl & "?token=" & Me.GetMd5Hash(md5, token))
        request.AutomaticDecompression = System.Net.DecompressionMethods.Deflate Or Net.DecompressionMethods.GZip
        request.Method = "POST"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Timeout = 5000


        Dim dataStream As IO.Stream = request.GetRequestStream()
        Dim postData As String = Newtonsoft.Json.JsonConvert.SerializeObject(rigaRichiesta)
        Dim enc As System.Text.UTF8Encoding = New System.Text.UTF8Encoding()
        Dim byteArray As Byte() = enc.GetBytes(postData)
        Using stream = request.GetRequestStream()
            stream.Write(byteArray, 0, byteArray.Length)
        End Using
        Dim responseString As String
        Try
            Dim result As System.Net.HttpWebResponse = request.GetResponse()
            Dim reader As New System.IO.StreamReader(result.GetResponseStream())
            responseString = reader.ReadToEnd()
            result.Close()
        Catch ex As Exception
            Me.Log.Text = "Errore nella chimata a " + Me.gatUrl + " " + ex.Message
            Me.Log.BackColor = Drawing.Color.Red

            Exit Sub
        End Try

The solution is slow: 10 minutes for 4k rows.

Call to a web service at Excel close passing all the data

Another solution is:

Save the Excel data as CSV;
Pass the CSV file as content of a REST call.

        Dim currentWorkbook As String = Me.workbook.FullName
        Dim currentFormat As Long = Me.workbook.FileFormat

        Dim nomeFile = currentWorkbook + ".csv"
        Me.foglioRichieste.SaveAs(nomeFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
        Me.foglioRichieste.SaveAs(currentWorkbook, currentFormat)

        Dim contenuto As String = System.IO.File.ReadAllText(nomeFile)

        Dim dt As DateTime = DateTime.Now
        Dim token As String = Me.token & dt.Year & dt.Month.ToString.PadLeft(2, "0") & dt.Day.ToString.PadLeft(2, "0")

        Dim md5 As System.Security.Cryptography.MD5 = System.Security.Cryptography.MD5.Create()
        Dim request As System.Net.HttpWebRequest = System.Net.WebRequest.Create(Me.gatUrl & "?azione=file&token=" & Me.GetMd5Hash(md5, token))
        request.AutomaticDecompression = System.Net.DecompressionMethods.Deflate Or Net.DecompressionMethods.GZip
        request.Method = "POST"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Timeout = 50000

        Dim dataStream As IO.Stream = request.GetRequestStream()

        Dim enc As System.Text.UTF8Encoding = New System.Text.UTF8Encoding()
        Dim byteArray As Byte() = enc.GetBytes(contenuto)
        Using stream = request.GetRequestStream()
            stream.Write(byteArray, 0, byteArray.Length)
        End Using
        Dim responseString As String
        Try
            Dim result As System.Net.HttpWebResponse = request.GetResponse()
            Dim reader As New System.IO.StreamReader(result.GetResponseStream())
            responseString = reader.ReadToEnd()
            result.Close()
        Catch ex As Exception
            Me.Log.Text = "Errore nella chimata a " + Me.gatUrl + " " + ex.Message
            Me.Log.BackColor = Drawing.Color.Red

            Exit Sub
End Try

In 2 seconds the data has been updated in the server.

Bonus trick: saving the Excel file as CSV using Visual Basic produce a file of good quality, UTF8 encoded and that use coma as separator.