Importing data from a TXT file to calc. A kind of "mask" or selective import

[ilkyest]

Pals.

I have a TXT file with some names and things I need. Example

Name: Peter Pan                              Internal Code: 99999999
NIN: 999.999.888-77
Type: Tractor                 Inventory number: 66778899
Brand.....: MASSEY FERGUSON                                   Qtd: 1          
Model....: MF290                                                             
Chassis/Serial number: 145541145            
Value $.....:            90.000,00  

Name: Capitain Hook                              Internal Code: 777777777
NIN: 799.899.888-77
Type: Tractor                 Inventory number: 66778899
Brand.....: New Holland                                   Qtd: 1          
Model....: NH1590                                                             
Chassis/Serial number: 145541145            
Value $.....:            170.000,00  

Name: Thinker Bel                              Internal Code: 4444444444444444
NIN: 799.899.999-77
Type: Tractor                 Inventory number: 66778899
Brand.....: Fendt                                   Qtd: 1          
Model....: FF1222                                                             
Chassis/Serial number: 91119991991            
Value $.....:            600.000

And I need import this to a Calc file, with:

Name - NIN - Internal Code - Type - Brand - Model - Chassis/SN - Inventory Number - Value

Every search I did like “mask importing” or “importing from TXT” don’t work.

The TXT file will be quite long. About 20, 30 names. And I need import these data, put them in a spreadshet, and after create some forms by a direct mail form to our internal sectors.

As of it, selective importing from this TXT would help me a lot. I lose about 3 hours to create these forms, and I should generate them twice a month.

If it’s possible without VBA, better. I didn’t work with VBA yet

Thanks in advance

[Villeroy]

Ask the creator of that text file for a normalized version. Database normalization - Wikipedia

[ilkyest]

It’s not possible. The system layout is cobol based, using a 3270 emulator.

My example, although fictitious names, fits exactly the system layout (unfortunately).
I copy selected areas from system screen and paste on libreoffice.

But I should select Name (not Name:) NIN, Tractor, etc, selecting any frame manually

In another department, I’ve used a VBA/Excel with personal comunications, but in another cenario. The VBA “copy” selected areas of the file (in that case, on the screen) and paste on excel.

But at this time, on my actual department I’m using linux, libreoffice 7.1.2.2, and…my corporation doesn’t offer PC Comunications license anymore, even windows.

[erAck]

Run your data through the following awk script, e.g.
gawk -f free-form-text-to-records.awk <data.txt >data.csv
and import the resulting data.csv file to Calc with ; semicolon field separator and an import locale that uses , comma decimal separator and . dot group separator. Make sure you import non-Value columns as type Text, especially Internal Code and Inventory Number and Chassis/SN if they come with digits only but in fact are IDs.

#!/usr/bin/gawk -f

BEGIN {
    # Two field separators, : followed by at least 1 space, or at least 3 spaces.
    FS = ": +| {3,}"
    # Semicolon separated "CSV" (SSV) preferred because data contains commas.
    ofs = ";"
    OFS = ofs
}

/^$/ {
    precord()
    next
}

# Name; Internal Code
# NIN
# Type; Inventory number
# Brand; Qtd
# Model
# Chassis/Serial number
# Value $
/^Name:/ {
    name = enquote($2)
    internal = enquote($4)
    next
}
/^NIN:/ {
    nin = enquote($2)
    next
}
/^Type:/ {
    type = enquote($2)
    inventory = enquote($4)
    next
}
/^Brand\./ {
    brand = enquote($2)
    next
}
/^Model\./ {
    model = enquote($2)
    next
}
/^Chassis\// {
    chassis = enquote($2)
    next
}
/^Value / {
    value = enquote($2)
    next
}

ENDFILE {
    precord()
}

# Name;NIN;Internal Code;Type;Brand;Model;Chassis/SN;Inventory Number;Value
function precord() {
    print name, nin, internal, type, brand, model, chassis, inventory, value
    name = nin = internal = type = brand = model = chassis = inventory = value = ""
}

function enquote(var) {
    quote = 0
    if (index(var, "\"")) {
        gsub(/"/, "\"\"", var)
        quote = 1
    }
    if (quote || index(var, ofs))
        var = "\"" var "\""
    return var
}

[ilkyest]

Wow.

Let’s try. I post here the results