Hi,
I´d like to convert a text file to csv, but I need some help please.
Source is a text file with software license codes (whicht I bought over several years),
target should be a CSV file.
Source:
each record is divided by an empty line,
first line contains name of the software,
followed by (1…x) lines with license information.
e.g.
Adobe Photoshop
License name: John Doe
s/n: 92409349u23r9jweifjse
ACDsee Ultimate
Licensed to John Doe
serial: 3ur9üusijfisdjgghisrdö
serial2: fjiöj49tutse9tu
Target:
1st column: name of the software (as in source 1st line)
2nd column: concat of all following lines of the record, with CR or any other divider
e.g.
Adobe Photoshop;License name: John Doe // s/n: 92409349u23r9jweifjse
ACDsee Ultimate;Licensed to John Doe // serial: 3ur9üusijfisdjgghisrdö // serial2: fjiöj49tutse9tu
I have no idea how to get a new record after each empty line.
Maybe someone could give a hint …?
But, if there are a variable number of rows, that makes things tricky. I’m not sure there is enough structure to work with. It would help if the labels (e.g. “License name:”) were the same for each package.
Unfortunately, the number of rows after the first line is variable, the only common identifyer is the empty line between the records. So I need something like “as long as there is no empty line, continue appending text to the field”.
There are no labels, so “simply” everything from line 2 up to the next empty line belongs to the same record.
We might be able to make the Concat Rows transform flexible enough to handle this in future. For example by adding an option to keep concatenating rows until you reach a row that matches a certain criteria (e.g. empty). But we don’t have that at present.
Maybe that could be a great improvement, having such a concat function with stop condition.
Meanwhile I’m experimenting with text editor and RegEx, but as a RegEx noob it’s not easy
My idea is
1.) replacing \r\n(any text of first line)\r\n by \r\n||S(any text of first line)||E\r\n,
2.) then replacing \r\n by []
and now everything is in one single line,
3.) after that divide in multiple lines by replacing ||S by \r\n
4.) and divide in multiple columns with EDT at divider ||E
… but I fail already at the first step