How to modify a materials list, given a CSV file?

[pilotgeorge]

I have a CSV file which lists items like this;

Item Quantity Part Reference Designator
1 || 3 || 2K || R1,R2,R3
2 || 2 || 10uF || C1,C2

I need to convert this to;
Item Quantity Part Reference Designator

1    ||     1   ||      2K   ||   R1
     ||     1   ||      2K   ||   R2
     ||     1   ||      2K   ||   R3
2    ||     1   ||    10uF   ||   C1
     ||     1   ||    10uF   ||   C2

Is there a way to do this format conversion automatically. I have a List Of Materials that has 200 items on it. converting this manually would be a real pain!

[Wanderer]

@pilotgeorge

Is there a way to do this format conversion automatically

IMHO no, unless you code a macro.
You have to

• count , in last column
• insert new lines according to number of ,
• copy cells starting with second column
• “distribute” contents of last column

then go down according to number of , or inserted lines (or until you find a filled first column.
.
Maybe work from last to first row (easy to detect end of work in second or first row).

[CRDF]

@Wanderer

code a macro.
You have to
[…]

1. Do it in code: .csv → .csv.
2. Import ConvertedBOM.csv into Calc.

Something like (replace || to |):

import csv
file = open('/home/.../Documents/new1.csv', 'r')
csv = csv.reader(file, delimiter= '|')
# Converted csv:
converted = open('/home/.../Documents/BOM.csv', 'a')
#===================================================
id = 0
for r in csv:
    n = int(r[1])
    designator = r[3].split(',')
    for i in range(n):
        bom_row = f"{id + 1},{r[0]},{r[2]},{designator[i]}\n"
        id = id + 1
        print(bom_row)
        converted.write(bom_row)
converted.close()        
        

1,1,2K,R1
2,1,2K,R2
3,1,2K,R3
4,2,10uF,C1
5,2,10uF,C2

[Lupp]

The task is very strange and the problems should be easily avoided at the source side.
I spent some time to get something like a solution without user code.
However, it requires a recent version of LibreOffice supporting the TEXTSPLIT() function.
See
disask_133199_special_rearrangement_of_csv_imported_data.ods (49.1 KB)

The solution can’t handle cases where any part-designator is not unique.

= = Editing 2026-03-17 = =

• There’s a dilemma: Last night I dreamt a more compact solution and would like to post it.
• @cwolan posted a comment which may contain a similar solution.
• However, I would be able to understand an example file, but I don’t study large animated images. Never got clear information from such a thing.

So I attach my example without knowing if it adds anyting to the mentioned comment.
disask_133199_special_rearrangement_of_csv_imported_data_dream.ods (19.7 KB)

[cwolan]

@Lupp

it requires a recent version of LibreOffice supporting the TEXTSPLIT() function

TEXTSPLIT() was added in LibreOffice 25.8.
A less elegant solution would be to use REGEX() and SEQUENCE(). The latter was introduced in version 24.8.

On the other hand, in place of an array formula =TEXTSPLIT(TEXTJOIN(",", 0, D2:D6), “,”, “,”, 0)
one could:

1. add a comma replacement formula =SUBSTITUTE(Dx,",";CHAR(10))
   in a separate column in all appropriate cells
2. choose Edit ⟶ Cut
3. import via Edit ⟶ Paste Special ⟶ Unformatted Text
   ⠀

[cwolan]

@cwolan

TEXTSPLIT() was added in LibreOffice 25.8.
A less elegant solution would be to use REGEX() and SEQUENCE(). The latter was introduced in version 24.8.

@Lupp

• There’s a dilemma: Last night I dreamt a more compact solution and would like to post it.
• @cwolan posted a comment which may contain a similar solution.

My comment wasn’t meant to suggest that kind of solution.
I merely applied REGEX() in combination with SEQUENCE() to the result of TEXTJOIN() to get the same result as your TEXTSPLIT() formula.

[karolus]

Hallo

def strech_to_single_items(*_):
    """
    select your datasets without the header-row
    and run me
    """
    doc = XSCRIPTCONTEXT.getDocument()
    data = (sel:=doc.CurrentSelection).DataArray
    out = []
    for item, _, part, designators in data:
        ids = iter(designators.split(','))
        out.append((int(item), 1, part, next(ids)))
        for designator in ids:
            out.append( ("", 1, part, designator ) )
    sel.Spreadsheet[1:len(out)+1, :4].DataArray = out

its python! maybe use apso.oxt for organizing your python-stuff