Convert XLS to CSV custom Field separators(string)

[endless]

Hi

I am trying to convert a XLS to CSV with a string separator like '_A_'

I read the documentation and to use the '_' as separator you have to use the ASCII character 95 :

Code: Select all

 /usr/bin/soffice --headless  --convert-to csv:"Text - txt - csv (StarCalc)":95,34,76,,,,true "file.xls" 

it is working fine.

For multiple values documentation says:

1. Field separator(s) as ASCII values. Multiple values are separated by the slash sign ("/"), that is, if the values are separated by semicolons and horizontal tabulators, the token would be 59/9. To treat several consecutive separators as one, the four letters /MRG have to be appended to the token. If the file contains fixed width fields, the three letters FIX are used.

I tried with 95/65/95 (ASCII for _A_ ) but it ignore 65/95

ex:

Code: Select all

 /usr/bin/soffice --headless  --convert-to csv:"Text - txt - csv (StarCalc)":95/65/95,34,76,,,,true "file.xls" 

I also tried with the /MRG after the 95:

Code: Select all

 /usr/bin/soffice --headless  --convert-to csv:"Text - txt - csv (StarCalc)":95/65/95/MRG,34,76,,,,true "file.xls" 

and

Code: Select all

 /usr/bin/soffice --headless  --convert-to csv:"Text - txt - csv (StarCalc)":95/MRG/65/MRG/95/MRG,34,76,,,,true "file.xls" 

but it only add the 95( _ ) separator.

Is it possible to use a word as a field separator?

[RoryOF]

If i were doing this, I would use a unique token as separator in the CSV file, then edit with a plain text editor and do a global Find and Replace to Find my token then Replace with WORD.

[FJCC]

I suspect that the handling of multiple separators is only for the import process and that exporting is limited to one separator.The MRG option just doesn't make sense for exporting. RoryOF's suggestion is probably the best path.

[endless]

Thanks for the suggestion.

I am using the token 7(a non printable character for BEL)
and replacing it with sed

Code: Select all

  sed -i 's/\d7/_A_/g' "file.csv"

I hope nobody uses this non-printable character on an XLS file.

[Lupp]

You surely will intend to use the created files for input again some time. What import filter/software will be in charge of that then?

[endless]

You surely will intend to use the created files for input again some time. What import filter/software will be in charge of that then?

I am processing the CSV with jq.
with jq, I can convert CSV to json.
but, I was having a problem with strings with ',' like :

"apple,orange", 10, "blue"

So I created this custom delimiter.

Also In jq, I can't find a way to use an ASCII character as a delimiter. So I am using a word as delimiter

[Zizi64]

I am processing the CSV with jq.
with jq, I can convert CSV to json.
but, I was having a problem with strings with ',' like :

"apple,orange", 10, "blue"

The TSV type file is not appropriate for you? (TSV: TAB Separated Values - In the locales where the "," used as decimal separator the comma is unusable as data separator for the files containing some numbers)

[endless]

the xls is generated by the user.
As I have no control over what they put in the fields I have to make sure the separator is not used in the xls.
I believe any non-printable character will work.