I have a csv 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 for importing into a different system.

Any improvements are welcome and appreciated. Thanks!

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)

#