I need to format columns in a spreadsheet generated from CSV using
this format on the command line on a Mac:

_([$NAD] * #,##0.00_);_([$NAD] * (#,##0.00);_([$NAD] * "-"??_);_(@_)

In case you wonder, the Namibian Revenue Service requires this
format and if I can generate the spreadsheet it'll save time to
upload the data which I now have to do manually every month even
though the CSV is generated semi-automagically from the book
keeping software's backend.

Preferably XLSX because I use QSV (or rather its TO XLSX command,
but ODS will of course also work because conversion is easy and
they are working on something like TO ODS.

Language Preference in descending order: Perl, LUA, Python, Any
(working on the Mac/Homebrew).

I do not have Excel so an Apple Script would need to work on
NeoOffice.

I can write the bash function to tie this together if the script
can accept a file name as parameter, the formatting can be
hardcoded of course.

Any help would be greatly appreciated.

greetings, el

On 2023-05-17, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
> I need to format columns in a spreadsheet generated from CSV using
> this format on the command line on a Mac:
>
> _([$NAD] * #,##0.00_);_([$NAD] * (#,##0.00);_([$NAD] * "-"??_);_(@_)

A plain specification with input-output examples is better.
Not everyone knows what this formatting notation is.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

Neither do I more of less.

It seems to put NAD on the left of the cell and an amount on the
right. If the amount is 0 it des not put NAD in but a '-' sign.

But, I need to figure out how to cells in an existing spreadsheet
from the command line, never mind the actual formatting (for the
time being).

el

On 18/05/2023 00:21, Kaz Kylheku wrote:
> On 2023-05-17, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>> I need to format columns in a spreadsheet generated from CSV using
>> this format on the command line on a Mac:
>>
>> _([$NAD] * #,##0.00_);_([$NAD] * (#,##0.00);_([$NAD] * "-"??_);_(@_)
>
> A plain specification with input-output examples is better.
> Not everyone knows what this formatting notation is.
>

On 5/17/23 18:21, Kaz Kylheku wrote:
> On 2023-05-17, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>> I need to format columns in a spreadsheet generated from CSV using
>> this format on the command line on a Mac:
>>
>> _([$NAD] * #,##0.00_);_([$NAD] * (#,##0.00);_([$NAD] * "-"??_);_(@_)
>
> A plain specification with input-output examples is better.
> Not everyone knows what this formatting notation is.

That looks like the stuff spreadsheets use to specify formats for their
cells. I don't know how portable it is. It might be proprietary to
each individual spreadsheet program. There are certainly similarities
between Excel and LibreOffice Calc formatting codes, but I don't know if
they're interchangeable. And I know even less about other spreadsheets!

IOW, the example formatting string might be useless without knowing
which spreadsheet program it came from.

If the OP wants to write to a spreadsheet from the command line, there's
a Perl library specifically for writing XLSX:

https://metacpan.org/pod/Excel::Writer::XLSX
https://github.com/jmcnamara/excel-writer-xlsx

However, using that is a discussion that's probably more suited for a
Perl newsgroup rather than here in comp.unix.shell.

No,

I want to (re-)format an existing spreadsheet. I.e. one that is
generated by a tool which can not format cells.

el
On 18/05/2023 17:31, John-Paul Stewart wrote:
[...]
> If the OP wants to write to a spreadsheet from the command line, there's
> a Perl library specifically for writing XLSX:
[...]

On Thu, 18 May 2023 15:35:25 -0400, Dr Eberhard W Lisse <nospam@lisse.na> wrote:

> No,
>
> I want to (re-)format an existing spreadsheet. I.e. one that is
> generated by a tool which can not format cells.

Include what program created the existing spreadsheet, what program will be
using the output, enough lines from the existing spreadsheet to demonstrate
the expected variety of input conditions, and the format lines.

Regards, Dave Hodgins

On 2023-05-18, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
> Neither do I more of less.
>
> It seems to put NAD on the left of the cell and an amount on the
> right. If the amount is 0 it des not put NAD in but a '-' sign.
>
> But, I need to figure out how to cells in an existing spreadsheet
> from the command line, never mind the actual formatting (for the
> time being).
>

I wonder if you could use the .XML file format that was used by
Excel 2003.

This is very simple, if we are to believe this Github repo:
https://gist.github.com/ilyazub/2bced708865150b275b3

Such a thing could be generated from tabulated data by simple text
processing.

An XLSX file is considerably more complicated. It's actually a ZIP
archive containing multiple files related together.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

On Thu, 18 May 2023 21:35:25 +0200, Dr Eberhard W Lisse wrote:
> No,
>
> I want to (re-)format an existing spreadsheet. I.e. one that is
> generated by a tool which can not format cells.
>

Based on those requirements, sounds easy enough to use a while loop to
read each line, use "set" command to parse each column into a variable
followed by a print or echo statement to dump the variables in the desired
order and separator.

For example the following set command will split columns at every comma.

#!/bin/bash
while read -r line; do
set -- $(IFS=',' ; echo $line)
col1=$1
col2=$2
col3=$3
echo $col3,$col1,$col2 >> new_fn_here
done < <(cat filename_for_input_here)

Suggested homework,
man set

It MUST be XLSX as it needs to be uploaded to the Tax Authority.

But I will look at the link.

el

On 19/05/2023 03:59, Kaz Kylheku wrote:
> On 2023-05-18, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
[...]
> I wonder if you could use the .XML file format that was used by
> Excel 2003.
>
> This is very simple, if we are to believe this Github repo:
> https://gist.github.com/ilyazub/2bced708865150b275b3
>
> Such a thing could be generated from tabulated data by simple text
> processing.
>
> An XLSX file is considerably more complicated. It's actually a ZIP
> archive containing multiple files related together.
>

And how is that changing anything in the XLSX?

el

On 19/05/2023 06:44, Bit Twister wrote:
> On Thu, 18 May 2023 21:35:25 +0200, Dr Eberhard W Lisse wrote:
>> No,
>>
>> I want to (re-)format an existing spreadsheet. I.e. one that is
>> generated by a tool which can not format cells.
>>
>
> Based on those requirements, sounds easy enough to use a while loop to
> read each line, use "set" command to parse each column into a variable
> followed by a print or echo statement to dump the variables in the desired
> order and separator.
>
> For example the following set command will split columns at every comma.
>
> #!/bin/bash
> while read -r line; do
> set -- $(IFS=',' ; echo $line)
> col1=$1
> col2=$2
> col3=$3
> echo $col3,$col1,$col2 >> new_fn_here
> done < <(cat filename_for_input_here)
>
> Suggested homework,
> man set

None of the below is going to assist in the issue of modifying a cell
format in an existing XLSX spreadsheet (never mind the format, for the
time being).

el

On 19/05/2023 00:07, David W. Hodgins wrote:
> On Thu, 18 May 2023 15:35:25 -0400, Dr Eberhard W Lisse
> <nospam@lisse.na> wrote:
>
>> No,
>>
>> I want to (re-)format an existing spreadsheet. I.e. one that is
>> generated by a tool which can not format cells.
>
> Include what program created the existing spreadsheet, what program will be
> using the output, enough lines from the existing spreadsheet to demonstrate
> the expected variety of input conditions, and the format lines.
>
> Regards, Dave Hodgins

Maybe can I try to read it with Spreadsheet::Reader::ExcelXML to
and then generate a new spreadsheet with the formatting using
something Excel::Writer::XLSX.

Sounds like a little Perl project for the weekend. I will revert.

el

On 18/05/2023 21:35, Dr Eberhard W Lisse wrote:
> No,
>
> I want to (re-)format an existing spreadsheet. I.e. one that is
> generated by a tool which can not format cells.
>
> el
> On 18/05/2023 17:31, John-Paul Stewart wrote:
> [...]
>> If the OP wants to write to a spreadsheet from the command
>> line, there's a Perl library specifically for writing XLSX:
> [...]

In article <kcomtbFm9q3U3@mid.individual.net>,
Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>None of the below is going to assist in the issue of modifying a cell
>format in an existing XLSX spreadsheet (never mind the format, for the
>time being).

The obvious question at this point is: Why did you post this question to a
shell newsgroup on Usenet?

People are entitled to give answers that are based on (i.e., "use") the
tool(s) mentioned in the newsgroup title (i.e., here the operative word is
"shell"). For example, if you ask a question in a Perl newsgroup, people
are likely to (and entitled to) give answers using Perl - even if Perl is
not really the right tool for that job (viewed in the abstract). Ditto the
previous sentence for just about any other tool/language.

Here, a shell answer is reasonable - even though shell is hardly the right
tool for your task. Somebody has already given an answer based on the
common (but usually misguided) "while read; do ...; done" shell idiom.

Moving back to your task... It seems clear to me that the obvious and
correct solution to your problem is to use Excel and some form of
automation to do the changes you need. Easiest and most straightforward
would be to use an Excel macro (and invoke it from the command line; I know
I've done this in the past - but the details escape me at the moment).\

Now, I hear you say: But one of the conditions of the problem is that I
don't have Excel. To which I say: That's like saying "I want to bake
bread, but I don't have access to flour". The only valid response to
*that* is "Get your checkbook (or credit card) and go to the grocery store
and buy some flour". Note that this analogy is not entirely facetious,
because I believe there actually are baking recipes for making stuff
without flour, but I believe the results of doing are inedible... So, it
may be possible to do this without Excel, but the results are not going to
be good.

That all said, you might find it possible to automate LibreOffice (or
similar) to do what you want (if you really can't use Excel). But in any
case, this is *not* a shell problem.

--
If you think you have any objections to anything I've said above, please
navigate to this URL:
http://www.xmission.com/~gazelle/Truth
This should clear up any misconceptions you may have.

On 19.05.2023 06:44, Bit Twister wrote:
> On Thu, 18 May 2023 21:35:25 +0200, Dr Eberhard W Lisse wrote:
>> No,
>>
>> I want to (re-)format an existing spreadsheet. I.e. one that is
>> generated by a tool which can not format cells.

(There were already hints about using more appropriate newsgroups.)

>
> Based on those requirements, sounds easy enough to use a while loop to
> read each line, use "set" command to parse each column into a variable
> followed by a print or echo statement to dump the variables in the desired
> order and separator.
>
> For example the following set command will split columns at every comma.
>
> #!/bin/bash
> while read -r line; do
> set -- $(IFS=',' ; echo $line)

This appears a bit convoluted; input to 'read' can also be splitted
on ',' and you can read data directly into variables; if there's a
fixed number of columns)

while IFS=, read -r col1 col2 col3
do ...

or in case of an arbitrary number of fields - and provided that you
assume bash - use an array instead of using the positional arguments

while IFS=, read -r -a col ## for Ksh use -A instead of -a
do
col0=${col[0]}
col1=${col[1]}
col2=${col[2]}
...
done

(Just to turn that non-shell thread towards a shell topic.)

And of course the simple solution would be to use an awk-onliner for
that task instead of shell.

Janis

> col1=$1
> col2=$2
> col3=$3
> echo $col3,$col1,$col2 >> new_fn_here
> done < <(cat filename_for_input_here)
>
> Suggested homework,
> man set
>

On 19.05.2023 16:14, Janis Papanagnou wrote:
>
> And of course the simple solution would be to use an awk-onliner for
> that task instead of shell.

I meant awk /one-liner/.

For example awk 'BEGIN {FS=OFS=","} {print $3,$1,$2}'

Janis

On 2023-05-19, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
> None of the below is going to assist in the issue of modifying a cell
> format in an existing XLSX spreadsheet (never mind the format, for the
> time being).

I see; so your opening words:

"I need to format columns in a spreadsheet generated from CSV using
this format on the command line on a Mac:"

literally mean that you have a spreadsheet object which needs to be
manipulated, not that you have the CSV and are trying to generate
the spreadsheet with certain formatting.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

In article <20230519074149.723@kylheku.com>,
Kaz Kylheku <864-117-4973@kylheku.com> wrote:
>On 2023-05-19, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>> None of the below is going to assist in the issue of modifying a cell
>> format in an existing XLSX spreadsheet (never mind the format, for the
>> time being).
>
>I see; so your opening words:
>
> "I need to format columns in a spreadsheet generated from CSV using
> this format on the command line on a Mac:"
>
>literally mean that you have a spreadsheet object which needs to be
>manipulated, not that you have the CSV and are trying to generate
>the spreadsheet with certain formatting.

Which, if you really think about it, is the only sensible interpretation of
those (i.e., OP's) words.

But of course, as I've noted in my previous post on this thread, people
*are* entitled to re-interpret your (meaning OP's) words, in order to
re-frame them in such a way so as to have it make sense to have been posted
in the newsgroup to which it was posted.

--
Modern Conservative: Someone who can take time out from demanding more
flag burning laws, more abortion laws, more drug laws, more obscenity
laws, and more police authority to make warrantless arrests to remind
us that we need to "get the government off our backs".

I do not have Excel. And do not want to.

I generate the XLSX from the command line (using tools like QSV),
but that's beside the point.

I need to do this on/from the command line so it can be part of a
script.

I read the (non-)answers here as I am not the only one who does
not know how to do it.

el

On 19/05/2023 16:14, Janis Papanagnou wrote:
> On 19.05.2023 06:44, Bit Twister wrote:
>> On Thu, 18 May 2023 21:35:25 +0200, Dr Eberhard W Lisse wrote:
>>> No,
>>>
>>> I want to (re-)format an existing spreadsheet. I.e. one that is
>>> generated by a tool which can not format cells.
>
> (There were already hints about using more appropriate newsgroups.)
[...]

On Sun, 21 May 2023 21:29:04 +0200, Dr Eberhard W Lisse wrote:

> I do not have Excel. And do not want to.
>
> I generate the XLSX from the command line (using tools like QSV),
> but that's beside the point.
>
> I need to do this on/from the command line so it can be part of a
> script.
>
> I read the (non-)answers here as I am not the only one who does
> not know how to do it.

You are likely correct. I doubt that many (read "any") here have
had experience generating income tax data for the Namibian Revenue
Service.

The problem here may be that we don't really have enough information
to formulate an acceptable solution for you. For myself, I'd want
to know exactly what the Namibian Revenue Service requires within the
XLSX file. Specifically, I'd need to know how XLSX applies formatting
information to each cell, and whether the Revenue Service would accept
an XLSX file in which cell contents has no formatting information, but
have been "pre-formatted". In other words, do they want the cells to
contain numbers, and formatting code, or would they accept cells with
strings that express the same information as numbers and formatting code.

/IF/ the Namibian Revenue Service accepts the latter, I'd
a) export the data in CSV form from the book-keeping software,
b) read the CSV and apply a script to transform the numeric values into
formatted strings, generating a new CSV file with formatted data, and
c) apply a CSV-to-XLSX transform (such as your QSV "TO XLSX" command)
against the formatted data CSV to create the XLSX to submit to
the revenue service.

On the surface, this all seems doable from a script.

Again, /IF/ the NRS would accept such a file, several posters here have
given you advice on how to perform step "b" (reformat cells in the CSV).

[snip]

HTH
--
Lew Pitcher
"In Skills We Trust"

On 2023-05-21, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
> I do not have Excel. And do not want to.
>
> I generate the XLSX from the command line (using tools like QSV),
> but that's beside the point.

OK, since you have the XLSX generation down, what's preventing you from
manipulating the inputs to your tools, or their behavior. to get the
required format?

Cracking open the XLSX to fix it up seems like a circuitous route.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

In article <20230521153007.658@kylheku.com>,
Kaz Kylheku <864-117-4973@kylheku.com> wrote:
>On 2023-05-21, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>> I do not have Excel. And do not want to.
>>
>> I generate the XLSX from the command line (using tools like QSV),
>> but that's beside the point.
>
>OK, since you have the XLSX generation down, what's preventing you from
>manipulating the inputs to your tools, or their behavior. to get the
>required format?
>
>Cracking open the XLSX to fix it up seems like a circuitous route.

But, like it or not, that *is* what OP wants to do.

And, as that great sage Woody Allen has said, the heart wants what it
wants.

--
The randomly chosen signature file that would have appeared here is more than 4
lines long. As such, it violates one or more Usenet RFCs. In order to remain
in compliance with said RFCs, the actual sig can be found at the following URL:
http://user.xmission.com/~gazelle/Sigs/ForFoxViewers

On 2023-05-22, Kenny McCormack <gazelle@shell.xmission.com> wrote:
> In article <20230521153007.658@kylheku.com>,
> Kaz Kylheku <864-117-4973@kylheku.com> wrote:
>>On 2023-05-21, Dr Eberhard W Lisse <nospam@lisse.NA> wrote:
>>> I do not have Excel. And do not want to.
>>>
>>> I generate the XLSX from the command line (using tools like QSV),
>>> but that's beside the point.
>>
>>OK, since you have the XLSX generation down, what's preventing you from
>>manipulating the inputs to your tools, or their behavior. to get the
>>required format?
>>
>>Cracking open the XLSX to fix it up seems like a circuitous route.
>
> But, like it or not, that *is* what OP wants to do.
>
> And, as that great sage Woody Allen has said, the heart wants what it
> wants.

Wanting to crack the XLSX is half the fun. The other fun is making
a newsgroup full of fools guess about what it is one wants.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

In article <20230521211719.905@kylheku.com>,
Kaz Kylheku <864-117-4973@kylheku.com> wrote:
....
>>>Cracking open the XLSX to fix it up seems like a circuitous route.
>>
>> But, like it or not, that *is* what OP wants to do.
>>
>> And, as that great sage Woody Allen has said, the heart wants what it
>> wants.
>
>Wanting to crack the XLSX is half the fun. The other fun is making
>a newsgroup full of fools guess about what it is one wants.

Indeed. Well put.

Anyway, I doubt that "cracking" the XLSX is going to be a fruitful
endeavor. The only sensible way to edit an XLSX file is to use a
spreadsheet program, e.g., Excel or LibreOffice. Trying to do it in a way
that would be on-topic here (*) (or, for that matter, just about any other
Usenet newsgroup; I don't know if there are any newsgroups dedicated to
Excel or to MS Office tools in general) is, to put it charitably, misguided.

BTW, OP has stated that he does not have Excel (where, again, trying to
solve this sort of problem w/o Excel is like trying to make bread w/o
flour), but what about LO? Can he use that?

(*) "Here" being comp.unix.shell

--
"There are two things that are important in politics.
The first is money and I can't remember what the second one is."
- Mark Hanna -