Excel file with numbers for footprint causes TypeError on insertion

[scandey]

Another edge case issue that is easily worked around on the user end, just confused me for a bit.

When an Excel spreadsheet is extracted from schematic and then edited to have a cell with any format except Text containing just a number, it causes a TypeError when that spreadsheet is then inserted back into the schematic. Behavior not seen with .csv export.

Seems like Excel forces any edited cell with format of General containing just a number to remain a number, notable in that a cell with just 0805 for footprints is immediately replaced by 805. If the user is careful about resetting every cell with just a number to Text format, there are no issues. Since the Excel spreadsheet exported by KiField seems to have a string in a General formatted cell, inserting an unmodified spreadsheet causes no issues.

If possible, it would be lovely to have KiField do a sanity check or cast to string before importing from Excel cells, though I realize this is perhaps more easily solved by the user before inserting.

Thank you again for your work on this project!

Simplest possible schematic included, with example .xlsx and .csv files for comparison. footprint.zip

footprint.sch has one resistor with a footprint of 0805. Extracted to footprint.xlsx and footprint.csv with kifield -x /Users/scottcandey/Desktop/footprint.sch -i ~/Desktop/footprint.csv

Change from 0805 to 0605 footprint in the Excel document causes the cell to container a number, 603. Also changed CSV for consistency, 0805 to 0603.

Attempted Excel insertion with kifield -x ~/Desktop/footprint.xlsx -i /Users/scottcandey/Desktop/footprint.sch -d 10 results in TypeError: expected string or bytes-like object.

Extracting fields +[], -[] from files ['/Users/scottcandey/Desktop/footprint.xlsx'].
Extracting fields from /Users/scottcandey/Desktop/footprint.xlsx.
Extracting fields [], -[] from XLSX file /Users/scottcandey/Desktop/footprint.xlsx.
Header on row 1: ['Refs', 'datasheet', 'footprint', 'value'].
Found references on header column 1.
Exploding R1 => ['R1'].
Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': 603, 'value': 'R'}}
Total Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': 603, 'value': 'R'}}
Traceback (most recent call last):
  File "/Users/scottcandey/.local/share/virtualenvs/KiField-lIPAINC2/bin/kifield", line 8, in <module>
    sys.exit(main())
  File "/Users/scottcandey/.local/share/virtualenvs/KiField-lIPAINC2/lib/python3.9/site-packages/kifield/__main__.py", line 159, in main
    kifield(
  File "/Users/scottcandey/.local/share/virtualenvs/KiField-lIPAINC2/lib/python3.9/site-packages/kifield/kifield.py", line 1334, in kifield
    clean_part_fields(part_fields_dict)
  File "/Users/scottcandey/.local/share/virtualenvs/KiField-lIPAINC2/lib/python3.9/site-packages/kifield/kifield.py", line 1314, in clean_part_fields
    v = re.sub("[\n\r]+$", "", v) # Remove newlines at end of field.
  File "/usr/local/Cellar/python@3.9/3.9.2_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/re.py", line 210, in sub
    return _compile(pattern, flags).sub(repl, string, count)
TypeError: expected string or bytes-like object

Attempted CSV insertion with kifield -x ~/Desktop/footprint.csv -i /Users/scottcandey/Desktop/footprint.sch -d 10 results in a successful insertion.

Extracting fields +[], -[] from files ['/Users/scottcandey/Desktop/footprint.csv'].
Extracting fields from /Users/scottcandey/Desktop/footprint.csv.
Extracting fields [], -[] from CSV file /Users/scottcandey/Desktop/footprint.csv.
Converting CSV file /Users/scottcandey/Desktop/footprint.csv into an XLSX workbook.
Header on row 1: ['Refs', 'datasheet', 'footprint', 'value'].
Found references on header column 1.
Exploding R1 => ['R1'].
Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': '0603', 'value': 'R'}}
Total Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': '0603', 'value': 'R'}}
Inserting extracted fields into files ['/Users/scottcandey/Desktop/footprint.sch'].
Inserting fields into /Users/scottcandey/Desktop/footprint.sch.
Inserting extracted fields into schematic file /Users/scottcandey/Desktop/footprint.sch.
Updating R1 field 3 from "~" to "~"
Updating R1 field 2 from "0805" to "0603"
Updating R1 field 1 from "R" to "R"

To isolate the issue, I also tried with the footprint cell of the Excel spreadsheet set to Text format, which also had no problem, kifield -x ~/Desktop/footprint_text.xlsx -i /Users/scottcandey/Desktop/footprint.sch -d 10

Extracting fields +[], -[] from files ['/Users/scottcandey/Desktop/footprint_text.xlsx'].
Extracting fields from /Users/scottcandey/Desktop/footprint_text.xlsx.
Extracting fields [], -[] from XLSX file /Users/scottcandey/Desktop/footprint_text.xlsx.
Header on row 1: ['Refs', 'datasheet', 'footprint', 'value'].
Found references on header column 1.
Exploding R1 => ['R1'].
Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': '0603', 'value': 'R'}}
Total Extracted Part Fields:
{'R1': {'datasheet': '~', 'footprint': '0603', 'value': 'R'}}
Inserting extracted fields into files ['/Users/scottcandey/Desktop/footprint.sch'].
Inserting fields into /Users/scottcandey/Desktop/footprint.sch.
Inserting extracted fields into schematic file /Users/scottcandey/Desktop/footprint.sch.
Updating R1 field 3 from "~" to "~"
Updating R1 field 2 from "0803" to "0603"
Updating R1 field 1 from "R" to "R"

[xesscorp]

Thanks for the detailed problem report! I'll take a look at it.

[xesscorp]

Now when spreadsheets are created, any cell containing a string has its number format set as TEXT. That means anything you type into the cell should be interpreted as text so entering 0603 will result in the string 0603 instead of the number 603. In order to enter an actual number, you'll have to explicitly format the cell with a number format.

It's in the development branch. Let me know if it fixes the problem.

[scandey]

That works for me, thank you!