How to split a messy CSV column for parts data

Your parts export may put a part number, size, material, and finish together in one column. A database import often expects each detail in its own field, and guessing where to split can put the wrong value in the wrong place.

You can separate the values manually if you work from a copy, decide on consistent rules, and check the result before importing. Use the CSV Editor to inspect the rows, find repeated patterns, and edit cells in your browser.

Split the column by hand

1. Make a working copy. Keep the original export unchanged, then open a copy in the CSV Editor. Your file stays on your device while you work. This gives you a way back if you overwrite a value or discover that your splitting rule does not fit every row.
2. Find a few representative rows. Look at the beginning, middle, and end of the messy column. For example, you might see PN-104 | M6 x 20 | stainless steel in one row and PN-105 | M8 x 30 | zinc plated in another. Note the separators and the order of the details.
3. Decide on the destination fields. Write down the fields your database needs, such as Part number, Size, and Material or finish. Use existing empty columns for these values. If the file has none, prepare a working copy with the required columns in your export template or other spreadsheet tool, then open that copy in the CSV Editor.
4. Split each row using the same rule. Read one source value at a time, then type each part into its matching destination cell. In the example, PN-104 goes in the part number field, M6 x 20 in the size field, and stainless steel in the material or finish field. Do not move to the next row until the values line up.
5. Review and save a new CSV. Check several edited rows against the original column, including rows with unusual values. Save or export the edited data as a new CSV file. Reopen that file and inspect a few rows before using it for the database import.

The CSV Editor lets you find and edit values, but this is a manual process, not an automatic split based on a separator. For a large file, work in small sections and keep your original copy available for comparison.

Choose a consistent field layout

The same details appear in the same order

If each row follows a pattern such as part number, size, then material, make a destination field for each detail and use that order throughout. Keep a component blank when the source does not provide it. Do not shift the other values left to fill the gap, because that can make a material appear as a size or a size appear as a part number.

Descriptions use different separators

A file may use a pipe in some rows, a comma in others, or just spaces. Treat punctuation as a clue, not a guarantee. Check whether the surrounding text and the part's known format support your interpretation before editing. A comma can also be part of a quoted description, so do not assume every comma marks a new detail.

Part numbers contain leading zeros

Keep identifiers exactly as written. For example, 00127 is not necessarily the same part number as 127. Do not remove zeros, spaces, hyphens, or letters just because the value looks like a number. Check the exported CSV to confirm that the ID still matches the source.

One source value contains extra details

Some rows may include a color, thread type, or packaging note that other rows do not have. Decide whether that detail belongs in an existing field or needs its own field in the database template. If the meaning is unclear, leave the value unchanged in the working copy and verify it with the parts record rather than guessing.

Check the split before importing

1. Compare the original and destination values. Confirm that every part number and descriptive detail from a sample of rows appears in the intended field. Check a few rows with short values and a few with long ones.
2. Look for shifted data. A row with a missing separator can leave later details in the wrong fields. Check for an unexpectedly long part number, a material value in the size field, or a blank field beside a value that seems out of place.
3. Check punctuation and units. Preserve units such as mm and in, and keep dimensions together if the database expects one size value. Do not change M6 x 20 into separate numbers unless the import schema specifically has separate diameter and length fields.
4. Reopen the saved file. A file can look correct while you edit it but export with a missing header or an altered value. Open the saved copy in the CSV Editor and inspect its headings, several edited rows, and any rows that were difficult to interpret.

Fix common splitting mistakes

1. Do not split on every space. A part description such as stainless steel has a space within one value. Use a clear separator when one exists, and apply your written field rules rather than treating each word as a separate field.
2. Leave uncertain rows for review. If one row does not match the pattern, do not force its contents into the usual fields. Keep the full source value in your copy or mark the row for checking against a catalog or parts record.
3. Keep editing aligned with the row. Avoid sorting the file while you are transferring values by hand. Sorting can change row order and make it harder to compare a destination value with its original. If you do sort for another reason, use a stable part number to match rows afterward.
4. Check the import template's required headers. A database may require exact column names and a particular field order. Match the template before importing, and confirm that the values are under the intended headings rather than relying on the column positions alone.

The CSV Editor works in your browser, and the file stays on your device while you review and edit it.