What I'm trying to export to, CSV:
joe,phone1,phone2,phone3, sue,cell4 ,home5,, alice,atrib6,x7,y9,z10
What needs to be done so that it can be exported to CSV:
<ul> <li>joe</li> <ul>phone1</ul> <ul>phone2</ul> <ul>phone3</ul> <li>sue</li> <ul>cell4</ul> <ul>home5</ul> <li>alice</li> <ul>atrib6</ul> <ul>x7</ul> <ul>y9</ul> <ul>z10</ul> </ul>
But not sure how to get from the above list, which I can rewrite as XML, to something which BaseX can then export as the desired CSV. I know that it has to be wrapped in <csv> and that <record> is used, as well. Something like:
<csv> <record> <Name>Huber</Name> <First_Name>Sepp</First_Name> <Address>Hauptstraße 13</Address> <City>93547 Hintertupfing</City> </record> </csv>
is what I see in the documentation, but not sure how to get there.
thanks,
Thufir
On Sat, Feb 15, 2020 at 11:12:36AM -0800, thufir scripsit:
What I'm trying to export to, CSV:
joe,phone1,phone2,phone3, sue,cell4 ,home5,, alice,atrib6,x7,y9,z10
This is the outcome you want?
What needs to be done so that it can be exported to CSV:
<ul> <li>joe</li> <ul>phone1</ul> <ul>phone2</ul> <ul>phone3</ul> <li>sue</li> <ul>cell4</ul> <ul>home5</ul> <li>alice</li> <ul>atrib6</ul> <ul>x7</ul> <ul>y9</ul> <ul>z10</ul> </ul>
This isn't flat. Which means you have a column mapping problem.
Does the thing expecting the CSV have a fixed list of columns? Do you know what that is? (If the first answer is "yes" and the second answer is "no", or if the first answer is unknown, that's the first thing to do.)
is what I see in the documentation, but not sure how to get there.
Generally speaking XQuery is not the best option for transforming XML content.
There is no requirement to use the CSV functions; those are there to be convenient, and if they're not convenient you don't have to use them.
That vaguely-list thing thing you referenced needs to be split; the usual way in XQuery would be tumbling windows. http://docs.basex.org/wiki/XQuery_3.0#window
One you've got a single record,
string-join($record/descendant::text(),',')
will give you one row of CSV. It won't necessarily look like you want it to; that column mapping problem again.
You can use file:write-text-lines, which appends line ending characters for you, so something like:
file:write-text-lines('path/to/file.csv',for $record in $data return string-join($record/descendant::text(),','))
will get you a CSV file.
The fun part is likely to be some combination of the column mapping and the tumbling windows.
-- Graydon
I'll read your response more carefully, but key takeaway is to maybe use something else? I'm futzing with powershell, seems reasonable for the task (if a bit odd).
On 2020-02-15 11:47 a.m., Graydon wrote:
On Sat, Feb 15, 2020 at 11:12:36AM -0800, thufir scripsit:
What I'm trying to export to, CSV:
joe,phone1,phone2,phone3, sue,cell4 ,home5,, alice,atrib6,x7,y9,z10
This is the outcome you want?
What needs to be done so that it can be exported to CSV:
<ul> <li>joe</li> <ul>phone1</ul> <ul>phone2</ul> <ul>phone3</ul> <li>sue</li> <ul>cell4</ul> <ul>home5</ul> <li>alice</li> <ul>atrib6</ul> <ul>x7</ul> <ul>y9</ul> <ul>z10</ul> </ul>
This isn't flat. Which means you have a column mapping problem.
Does the thing expecting the CSV have a fixed list of columns? Do you know what that is? (If the first answer is "yes" and the second answer is "no", or if the first answer is unknown, that's the first thing to do.)
is what I see in the documentation, but not sure how to get there.
Generally speaking XQuery is not the best option for transforming XML content.
There is no requirement to use the CSV functions; those are there to be convenient, and if they're not convenient you don't have to use them.
That vaguely-list thing thing you referenced needs to be split; the usual way in XQuery would be tumbling windows. http://docs.basex.org/wiki/XQuery_3.0#window
One you've got a single record,
string-join($record/descendant::text(),',')
will give you one row of CSV. It won't necessarily look like you want it to; that column mapping problem again.
You can use file:write-text-lines, which appends line ending characters for you, so something like:
file:write-text-lines('path/to/file.csv',for $record in $data return string-join($record/descendant::text(),','))
will get you a CSV file.
The fun part is likely to be some combination of the column mapping and the tumbling windows.
-- Graydon
On Sat, Feb 15, 2020 at 07:10:46PM -0800, thufir scripsit:
I'll read your response more carefully, but key takeaway is to maybe use something else? I'm futzing with powershell, seems reasonable for the task (if a bit odd).
XQuery's entirely suitable; you might not find the csv functions specifically suitable, it what I was trying to get at. Those suppose that your XML is already structured in a specific regular way.
CSV (and JSON) suffer from this expectation that they're easy formats. In practice, for any kind of non-trivial data, this isn't the case. A certain amount of design is required for any conversion; in the CSV case, that's usually "do all my rows/records have the same columns in the same order?" but it can be other things. ("Everything I might want to use as a separator exists in the data, as do a lot of double quotes", for example.)
-- Graydon
Take a look here https://en.wikibooks.org/wiki/XQuery/Displaying_Lists
Loren Cahlander
On Feb 15, 2020, at 10:40 PM, Graydon graydonish@gmail.com wrote:
On Sat, Feb 15, 2020 at 07:10:46PM -0800, thufir scripsit:
I'll read your response more carefully, but key takeaway is to maybe use something else? I'm futzing with powershell, seems reasonable for the task (if a bit odd).
XQuery's entirely suitable; you might not find the csv functions specifically suitable, it what I was trying to get at. Those suppose that your XML is already structured in a specific regular way.
CSV (and JSON) suffer from this expectation that they're easy formats. In practice, for any kind of non-trivial data, this isn't the case. A certain amount of design is required for any conversion; in the CSV case, that's usually "do all my rows/records have the same columns in the same order?" but it can be other things. ("Everything I might want to use as a separator exists in the data, as do a lot of double quotes", for example.)
-- Graydon
basex-talk@mailman.uni-konstanz.de
-
Graydon -
Loren Cahlander -
thufir