Split a warehouse log by two colons and preserve missing cells
Admin ·
Preview a pasted text log before copying it into a spreadsheet or script. A blank value can have a different meaning from a record that ends before that field, so choose the export based on whether you need that distinction.
Split the warehouse example on two colons
Load the warehouse example. Choose Custom, enter ::, and keep First record is a header, Trim cell whitespace, Skip blank records, and Respect quoted fields enabled.
Confirm there are three data rows, three columns, one missing cell and one skipped blank record. Café has note label :: urgent in one cell. The row beginning غرب shows Missing for Note. East shows Empty for Count.
Export JSON when an empty field carries meaning
Choose JSON and inspect the result. The first row keeps Count as a string with the digits 03. The row beginning غرب gives Note the value null, while East has an empty string for Count.
Choose CSV when the destination needs a rectangular delimited file. The table pads missing positions, so both cases appear as empty CSV fields. If a spreadsheet converts 03 to 3, set the destination column to text when you import it.
RFC 4180 CSV record and quoting conventionsRFC 8259 JSON strings, nulls and object member names
Review quote and separator errors using the record number
In quoted mode, a pair of enclosing double quotes protects delimiter text and line breaks inside the field. Write two adjacent quotes for each literal quote inside a quoted value.
If a raw log uses quotes as ordinary characters, turn Respect quoted fields off. With that setting each input line is a separate record, and every delimiter divides a field. Check the preview again before exporting.
RFC 4180 CSV record and quoting conventionsGitHub Flavored Markdown table cell syntax
Common questions
Will switching to a Markdown export execute an HTML tag from the input?
Cell text is escaped in the exported Markdown. An input tag displays as literal text. Exported line breaks use generated break elements, which render as line breaks when the destination allows that markup.
Why does the CSV row still have an extra comma at the end?
The trailing separator accounts for the last empty field in a rectangular row. Use JSON when consumers need to see whether that field was explicitly empty or missing in the source.
Sources
Put it into practice.
- Text to Columns
- CSV Viewer and HTML Table Exporter
- CSV Delimiter Detector and Converter
- Markdown Table Repair