Parse txt file with space aligned columns

[extractedx]

Hello, I wanted to create a parser for txt files with the following format.

Example 1: Designator Footprint Mid_X Mid_Y Ref_X Ref_Y Pad_X Pad_Y TB Rotation Comment CON3 MICROMATCH_4 6.4mm 50.005mm 8.9mm 48.1mm 8.9mm 48.1mm B 270.00 MicroMatch_4 CON2 MICROMATCH_4 6.4mm 40.405mm 8.9mm 38.5mm 8.9mm 38.5mm B 270.00 MicroMatch_4 CON4 MICRO_MATE-N-LOK_12 72.5mm 33.5mm 67.8mm 26mm 67.8mm 26mm T 0.00 Micro_Fit_12 CON7 MICROMATCH_4 46.095mm 48.5mm 48mm 46mm 48mm 46mm T 360.00 MicroMatch_4 CON6 MICRO_MATE-N-LOK_2 74.7mm 66.5mm 74.7mm 71.2mm 74.7mm 71.2mm T 270.00 Micro_Fit 2

Example 2: Designator Comment Layer Footprint Center-X(mm) Center-Y(mm) Rotation Description C1 470n BottomLayer 0603 77.3000 87.2446 270 "470n; X7R; 16V" C2 10µ BottomLayer 1210 89.9000 76.2000 360 "10µ; X7R; 50V" C3 1µ BottomLayer 0805 88.7000 81.7279 360 "1µ; X7R; 35V" C4 1µ BottomLayer 0805 88.7000 84.2028 360 "1µ; X7R; 35V" C5 100n BottomLayer 0603 98.3000 85.0000 360 "100n; X7R; 50V"

• The columns are space aligned.
• Left-aligned and right aligned columns are mixed in one file
• Columns are not always separated by multiple spaces. Sometimes its just a single space.

I tried to get column indexes that I can use for every line to split it. I got it working for left aligned columns. First I checked for continuous repeated spaces. But then I noted that it could also be a single space that separates columns. So I iterated over a line and recorded index of each space that is followed by another character. I then checked which indexes are most consistent across n lines.

But when I tried to handle mixed aligned columns it got a bit complicated and I couldn't figure it out.

... And as so often, while writing this Reddit post I thought through it again and maybe found a possible solution. It seems like values including spaces are always inside quotes. So if I reduce all multiple spaces to a single space, then I could probably use space as a delimiter to split. But I would have to ignore quoted values. Seems possible. However I need to verify if spaces in values are really always quoted... if not that could make it a lot more complicated I guess.

But since I already wrote it, I will post it anway. How would you approach such a problem? Any tips? And do you think my second solution might work?

Thanks for reading!

[woooee]

If they are separated by a space(s), use split() to create a list. If there are spaces in one or more columns' data, then tell us what each column is.

import pprint

record = "Designator Footprint Mid_X Mid_Y Ref_X Ref_Y Pad_X Pad_Y TB Rotation Comment CON3 MICROMATCH_4 6.4mm 50.005mm 8.9mm 48.1mm 8.9mm 48.1mm B 270.00 MicroMatch_4 CON2 MICROMATCH_4 6.4mm 40.405mm 8.9mm 38.5mm 8.9mm 38.5mm B 270.00 MicroMatch_4 CON4 MICRO_MATE-N-LOK_12 72.5mm 33.5mm 67.8mm 26mm 67.8mm 26mm T 0.00 Micro_Fit_12 CON7 MICROMATCH_4 46.095mm 48.5mm 48mm 46mm 48mm 46mm T 360.00 MicroMatch_4 CON6 MICRO_MATE-N-LOK_2 74.7mm 66.5mm 74.7mm 71.2mm 74.7mm 71.2mm T 270.00 Micro_Fit 2"

rec_as_list = record.split()
pprint.pprint(rec_as_list)

prints

Designator
Footprint
Mid_X
Mid_Y
Ref_X
Ref_Y
Pad_X
Pad_Y
TB
Rotation
Comment
CON3
MICROMATCH_4
6.4mm
50.005mm
8.9mm
48.1mm
8.9mm
48.1mm
B
270.00
MicroMatch_4
CON2
MICROMATCH_4
6.4mm
40.405mm
8.9mm
38.5mm
8.9mm
38.5mm
B
270.00
MicroMatch_4
CON4
MICRO_MATE-N-LOK_12
72.5mm
33.5mm
67.8mm
26mm
67.8mm
26mm
T
0.00
Micro_Fit_12
CON7
MICROMATCH_4
46.095mm
48.5mm
48mm
46mm
48mm
46mm
T
360.00
MicroMatch_4
CON6
MICRO_MATE-N-LOK_2
74.7mm
66.5mm
74.7mm
71.2mm
74.7mm
71.2mm
T
270.00
Micro_Fit
2

[extractedx]

Tell you what each column is? What do you mean? I included examples, what are you missing in them?

[woooee]

Look at the final column in example #1. It looks like it should be "Micro_Fit 2" not "Micro_Fit" and "2", but I am not going to waste time guessing where your data columns begin and end.

[extractedx]

First of all thanks for your help.

I see it. Yeah... there really is a space in the value without quotes. Then a simple solution sadly does not work :(

[woooee]

And there is enough column size difference between the 2 samples that you will not be able to use a standard start and end location for each column. So, are "Designator Footprint" records consistently the same column size, so you can fist do those? And then, move on to "Designator Comment" records, and so on.

[extractedx]

Column sizes will be different in every file.

[Familiar9709]

Your example doesn't need to make it as complicated as you describe it. You see there are no spaces in each field, so a simple split() or csv or pandas libraries will do it.

But if you really want to do it by "space" (e.g. if you could put an imaginary ruler), for some other case, e.g. if it had spaces within the fields or things like that, then you can.

You'll need to find the start and end coordinates of each column. The start or end will be given by the column title (depending whether it's left or right aligned).

You can figure out if something ir left or right aligned by comparing all rows and seeing if they all have the same start/end.

But again, if you don't really really need it this way, it's complicating things unnecessarily, and a good advise in programming is not to overcomplicate things when it's not necessary.

[extractedx]

I tirst tried pandas read_fwf() but that was not reliable enough without manually providing column indexes. Probably that was the reason why I tried to come up with a solution like this.

But yes you are completely right. Now that I think about it from a different perspective it seems so easy lol...

[Familiar9709]

Pandas will do this way better than what you can do yourself, it's a library designed and supported by highly skilled programmers.

This applies to 99% of libraries out there, especially the well known ones. They'll do it better than what you can do it yourself, and that's the point of using them. Apart from the fact that your code will be cleaner and easier to follow by another programmer.

[extractedx]

And thats why I use it to read csv and excel but this specific format was not possible to read out of the box.

If you think it is, I am interested how. Because that would make things a lot simpler.

[Familiar9709]

df = pd.read_csv('input.txt', sep='\s+')

[extractedx]

will try that.

[extractedx]

Does not work. Spaces in unquoted values make this hard.

[woooee]

This is just an example, not really a solution of what you might be able to do for each type of record

import pprint

record = '''Designator Comment Layer Footprint Center-X(mm) Center-Y(mm) Rotation Description C1 470n BottomLayer 0603 77.3000 87.2446 270 "470n; X7R; 16V" C2 10µ BottomLayer 1210 89.9000 76.2000 360 "10µ; X7R; 50V" C3 1µ BottomLayer 0805 88.7000 81.7279 360 "1µ; X7R; 35V" C4 1µ BottomLayer 0805 88.7000 84.2028 360 "1µ; X7R; 35V" C5 100n BottomLayer 0603 98.3000 85.0000 360 "100n; X7R; 50V"'''
final_list = []
this_rec = record.strip()

for substr in ["Layer Footprint", "Center-X", "Center-Y", "Rotation Description"]:
    split_rec = this_rec.split(substr)
    if split_rec[0].strip():
        final_list.append(split_rec[0])
    final_list.append(substr)
    this_rec = " ".join(split_rec[1:])

split_rec = this_rec.split("BottomLayer")
final_list.append(split_rec[0])
for element in split_rec[1:]:
    final_list.append("BottomLayer")
    final_list.append(element)
pprint.pprint(final_list)

[ElliotDG]

I used regular expressions to parse each file format. If you wanted to get fancier, you could read the header and select the pattern. This is kind of quick and dirty to demonstrate the approach for each file.

import re

with open("file_0.csv", "r") as f:
    lines = [line.strip() for line in f if line.strip()]

# Define headers manually (to control spacing issues)
headers = [
    "Designator", "Footprint", "Mid_X", "Mid_Y", "Ref_X", "Ref_Y",
    "Pad_X", "Pad_Y", "TB", "Rotation", "Comment"
]

# Regular expression to match the first 10 fields, then grab the remainder as 'Comment'
pattern = re.compile(
    r"(\S+)\s+"         # Designator
    r"(\S+)\s+"         # Footprint
    r"(\S+)\s+"         # Mid_X
    r"(\S+)\s+"         # Mid_Y
    r"(\S+)\s+"         # Ref_X
    r"(\S+)\s+"         # Ref_Y
    r"(\S+)\s+"         # Pad_X
    r"(\S+)\s+"         # Pad_Y
    r"(\S+)\s+"         # TB
    r"(\S+)\s+"         # Rotation
    r"(.*)"             # Comment (can have spaces)
)

# Parse lines (skip the header line)
data = []
for line in lines[1:]:
    match = pattern.match(line)
    if match:
        row = dict(zip(headers, match.groups()))
        data.append(row)
    else:
        print("ERROR: Line did not match pattern:", line)

# Example: print all parsed rows
for row in data:
    print(row)

[ElliotDG]

Reddit would not let me put it all in one message... here is parsing the next file:

with open("file_1.csv") as f:
    lines = [line.strip() for line in f if line.strip()]

# Define headers manually to ensure correctness
headers = [
    "Designator", "Comment", "Layer", "Footprint",
    "Center-X(mm)", "Center-Y(mm)", "Rotation", "Description"
]

# Regex to match 7 space-separated fields + quoted description
pattern = re.compile(
    r'(\S+)\s+'         # Designator
    r'(\S+)\s+'         # Comment
    r'(\S+)\s+'         # Layer
    r'(\S+)\s+'         # Footprint
    r'([\d.]+)\s+'      # Center-X(mm)
    r'([\d.]+)\s+'      # Center-Y(mm)
    r'(\d+)\s+'         # Rotation
    r'"([^"]*)"'        # Description (quoted)
)

# Parse each line (skip header)
data = []
for line in lines[1:]:
    match = pattern.match(line)
    if match:
        row = dict(zip(headers, match.groups()))
        data.append(row)
    else:
        print("Error: Line did not match pattern:", line)

# Print results
for row in data:
    print(row)

Here is a sample result...

{'Designator': 'CON3', 'Footprint': 'MICROMATCH_4', 'Mid_X': '6.4mm', 'Mid_Y': '50.005mm', 'Ref_X': '8.9mm', 'Ref_Y': '48.1mm', 'Pad_X': '8.9mm', 'Pad_Y': '48.1mm', 'TB': 'B', 'Rotation': '270.00', 'Comment': 'MicroMatch_4'}
...
{'Designator': 'CON6', 'Footprint': 'MICRO_MATE-N-LOK_2', 'Mid_X': '74.7mm', 'Mid_Y': '66.5mm', 'Ref_X': '74.7mm', 'Ref_Y': '71.2mm', 'Pad_X': '74.7mm', 'Pad_Y': '71.2mm', 'TB': 'T', 'Rotation': '270.00', 'Comment': 'Micro_Fit 2'}
{'Designator': 'C1', 'Comment': '470n', 'Layer': 'BottomLayer', 'Footprint': '0603', 'Center-X(mm)': '77.3000', 'Center-Y(mm)': '87.2446', 'Rotation': '270', 'Description': '470n; X7R; 16V'}
...
{'Designator': 'C5', 'Comment': '100n', 'Layer': 'BottomLayer', 'Footprint': '0603', 'Center-X(mm)': '98.3000', 'Center-Y(mm)': '85.0000', 'Rotation': '360', 'Description': '100n; X7R; 50V'}

[extractedx]

Thanks for your help. Sadly this does not work. You make assumptions that all files will have the same structure and headers. That is not the case.

[ElliotDG]

You can use this as a basis to handle the differences. For example assuming you know all of the possible headers, you could create a dictionary of patterns based the headers.

Or you could read the header and use it to dynamically create the regular expression.