I’ve just encountered a silly problem with the way a CSV file that was given to me is formatted. For some reason the creator has used
Bob Dylan, note the
I’m trying to gather some stats using LibreCalc but I have no idea how to import this file properly. The Text Import wizard doesn’t give me the ability to limit the number of columns that are to be recognized (it seem just like me the creators never expected for someone to format a CSV in such a silly way).
Let’s say I have the following CSV content (I’ve used
# to mark
a1###A a2###B a3###C ... a10##A#B a11##A.B.#C a12##A#D#(ABC) ... a998#Y a999#Z
LibreCalc is smart enough to remove all the excessive blanks so multiple occurrences of a delimiter (
### instead of just
#) are treated as a single one and the rest is discarded. However setting the delimiter to a whitespace produces the following (obvious) result:
A B C D E ----|----|-----|----|------ 1 |a1 |A | | 2 |a2 |B | | 3 |a3 |C | | ... |... |... |... |... 10 |a10 |A |B | 11 |a11 |A.B. |C | 12 |a12 |A |D |(ABC) ... |... |... |... |... 998 |a998|Y | | 999 |a999|Z | |
The result that I’m looking for is
A B C ----|----|----- 1 |a1 |A 2 |a2 |B 3 |a3 |C ... |... |... 10 |a10 |A B 11 |a11 |A.B. C 12 |a12 |A D (ABC) ... |... |... 998 |a998|Y 999 |a999|Z
Is there a way to fix this CSV using LibreCalc or do I have to manually (or with some code of mine) do this tedious task?