5
\$\begingroup\$

I have a CSV file that is exported from SolidWorks. The input looks like this:

1,A-00034,1,Approved,"Rotating Conveyor Top Level Assembly, 88-1/2" Long 2.5" Rollers, 57-1/2" BF",01,,,,STRUCT,ASSEMB,,
1.1,  P-001991,2,Approved,Hex Bushing to Convert 1/2 NPT to 1/4,01,,,44605K245,Pnuematics,Purchase,,
1.2,  P-012527,2,Submit for Approval,Half Union for 5/32" Tubing 1/4 NPT,,,Bimba,KQ2-H03-35AS,PNEU,PURCH,,
1.3,  A-03428,1,Approved,Rotating Conveyor Base Frame Assembly,,,,,STRUCT,ASSEMB,,
1.3.1,    P-001959,1,Approved,Fixed End Chain Flex Mount,01,11GA HRS  x 1-1/2" x 4",,,STRUCT,CUT,,
1.3.2,    P-002009,1,Approved,Chain Flex Fixed End,01,,IGUS,2020.4PZ,Hardware,Purchase,,
1.3.3,    P-003340,3,Approved,Hex Bushin for 3/8 NPT to 1/4 NPT,02,,McMasterCarr,44605K242,PNEU,PURCH,,
1.3.4,    P-013107,1,Approved,3 Position 5 Port Solenoid Valve 24 V DC Exhaust Center with Subplate ,01,,SMC,VFS3400-5FZ-03T,PNEU,PURCH,,
1.3.5,    A-00011,1,Approved,Base Weldment for 88" Rotating Conveyor,01,Weldment,,,Structural,Weldment,,
1.3.5.1,      P-001916,1,Approved,2-3/16"x10-5/8" Cold Round Shaft  with One end blind tapped 1/2-13 hole,01,2-3/16" CRS Round x 10-7/8",,,STRUCT,MACH,,
1.3.5.2,      P-012844,6,Approved,1/2x2x3 Hot Rolled Flat With One 1/2" Hole 2-3/8" From one end,01,,,,STRUCT,BURN,,
1.3.5.3,      P-001915,1,Approved,Rotating Conveyor Base Plate for 88" Rotator,02,1/2" Burn,,,STRUCT,BURN,,
1.3.5.4,      P-001918,1,Approved,2x4x4-3/4" Cold Flat Machined for Cylinder Mount on Rotate Conveyor,01,2" x 4" CRS x 4-3/4",,,STRUCT,MACH,,

I am breaking on each "Level" to create a new import for each BOM. I've got it hard coded to take up to 3 levels deep at this point, but was wondering if there was a better way to do this to accommodate any level of inner assemblies.

I am basing my current "break" on the segment count (1 = 1, 1.2 = 2, etc.). And using dictionary key:value pairs to keep the parent:components together. And then writing each Key:Value pair out to it's own CSV file for importing into a different system.

Any improvements are welcome and appreciated.

import csv
import os
import pyodbc
#import openpyxl

INPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM'
OUTPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM\\output'

bom_dict = dict()
for file in os.listdir(INPATH):
    file = os.path.join(INPATH, file)
    filename_zero, fileext = os.path.splitext(file)
    filename_zero = filename_zero.translate(",!#;:-/ ")
    if fileext != ".csv" and fileext != '.CSV':
        continue
    with open(file, 'r') as csvfile:
        reader = csv.reader(x.replace('\0', '') for x in csvfile)
        row_count = 0
        for row in reader:
            row_count = row_count + 1
            if len(row) > 0 and row_count > 1 :
                segments = row[0]
                segments = segments.split('.')
                if len(segments) == 1:
                    level = 'Parent'
                    parent_item = row[1]
                if len(segments) == 2 :
                    level = 'Component'
                    component_item = row[1].strip()
                    #print(component_item)
                    qty_per = row[2]
                    component_line = ['Component', component_item, qty_per]
                    if parent_item in bom_dict:  bom_dict[parent_item].append(component_line)
                    if parent_item not in bom_dict: bom_dict[parent_item] = [component_line]
                if len(segments) == 3 :
                    parent_item_2 = component_item
                    new_component = row[1].strip()
                    component_qty = row[2].strip()
                    new_line = ['Component',new_component, component_qty]
                    if parent_item_2 in bom_dict:     bom_dict[parent_item_2].append(new_line)
                    if parent_item_2 not in bom_dict: bom_dict[parent_item_2] = [new_line]
                if len(segments) == 4:
                    parent_item_3 = new_component
                    new_component_3 = row[1].strip()
                    component_qty = row[2].strip()
                    print(parent_item_3)
                    new_line = ['Component', new_component_3, component_qty]
                    if parent_item_3 in bom_dict:     bom_dict[parent_item_3].append(new_line)
                    if parent_item_3 not in bom_dict: bom_dict[parent_item_3] = [new_line]

print(bom_dict)
counts_dict = {k: (len(v)) for k, v in bom_dict.items()}
print(counts_dict)

for k, v in bom_dict.items():

    filename = k + '_bom.csv'
    file = os.path.join(OUTPATH, filename)
    main_count = sum(len(v) for k in bom_dict.values())

    with open(file, 'w+') as outfile:
        header_row = ['Parent Item ', k, main_count]
        #print(header_row)
        component_list = bom_dict[k]
        w = csv.writer(outfile, delimiter=',')
        w.writerow(header_row)
        for v in component_list:
            print(v)

#
\$\endgroup\$
2
  • \$\begingroup\$ Would it be correct to assume that we can group based on the second column name - if it begins with "A" - this is a new group - collect every row after until meet an another name that starts with "A" or the end of the file? \$\endgroup\$ Commented Jun 21, 2017 at 16:38
  • \$\begingroup\$ @alecxe unfortunately, no you can not assume that. I did originally, but while it may be an assembly, it may not be a NEW assembly. Only new assemblies are exploded in the document. \$\endgroup\$ Commented Jun 21, 2017 at 17:02

2 Answers 2

Reset to default
2
\$\begingroup\$

raw backwhacks

INPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM'
OUTPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM\\output'

The \ \ notation is distracting and error prone. Prefer to use an r-string if you feel you need that kind of slash. Better yet, prefer Path objects.

INPATH = Path(r'C:\Users\Alli\PycharmProjects\TestingFiles\BOM')
OUTPATH = INPATH / 'output'

helpers

Break out helper functions. Or turn this into a class for easy sharing of attributes.

for file in os.listdir(INPATH):
    ...
    with open(file, 'r') as csvfile:
        ...
        for row in reader:
            row_count = row_count + 1
            if len(row) > 0 and row_count > 1 :

We're operating at three different levels here:

  1. Iterate over files
  2. Iterate over the lines of a file
  3. Parse a line based on number of segments

You could easily break out one or two helpers to assist with that, so the calling function is focused on doing just One Thing well.

Also, that row_count += 1 would be more conveniently handled with for row_count, row in enumerate(reader):

Iterating over bom_dict.items() could also readily be broken out into a helper. Defining a few helpers would make it much easier to run automated tests of this code.

\$\endgroup\$
1
\$\begingroup\$

Comments

Every comment in the code should be deleted. Most are commented-out code, which just adds clutter. The last one seems like a stray typo.

Unused code

The following line can be removed because it is not needed:

import pyodbc

This was confirmed by running the ruff lint tool.

DRY

This long directory path is duplicated:

INPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM'
OUTPATH = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM\\output'

This avoids the repetition:

INPATH  = 'C:\\Users\\Alli\\PycharmProjects\\TestingFiles\\BOM'
OUTPATH = f'{INPATH}\\output'

Simpler

The 2 comparisons:

if fileext != ".csv" and fileext != '.CSV':

can be combined into one by making it case-insensitive:

if fileext.lower() != ".csv":

The following:

        row_count = row_count + 1

can be simplified using the special assignment operator:

        row_count += 1

This is quite common in Python, and it reduces repetition.

Efficiency

You needlessly execute this call to len several times:

len(segments)

It would be better to assign the result to a variable, then use that variable where needed:

segments = segments.split('.')
number_of_segments = len(segments)
if number_of_segments == 1:

These 4 separate if statements, starting with:

            if len(segments) == 1:
                level = 'Parent'
                parent_item = row[1]
            if len(segments) == 2 :

should be combined into a single if/elif statement:

            if len(segments) == 1:
                level = 'Parent'
                parent_item = row[1]
            elif len(segments) == 2 :

The len(segments) expression can only have one value, so the checks are mutually exclusive. This makes the code more efficient since you don't have to perform the 2nd check if the first is true, etc.

Documentation

Add a docstring to the top of the code to summarize the description of the code as well as the expected input and what output it creates:

"""
Read SolidWorks CSV files and output new files...
"""

I've got it hard coded to take up to 3 levels deep at this point, but was wondering if there was a better way to do this to accommodate any level of inner assemblies.

After implementing the suggestions above, it should be easier to change the code to accommodate any number of levels. There is some common code in the multiple if len(segments) branches; perhaps it can be extracted out into a function. Perhaps the multiple branches can become a for loop.

\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.