[basex-talk] how to export to CSV?
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: joe phone1 phone2 phone3 sue cell4 home5 alice atrib6 x7 y9 z10 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 and that is used, as well. Something like: Huber Sepp Hauptstraße 13 93547 Hintertupfing is what I see in the documentation, but not sure how to get there. thanks, Thufir
Re: [basex-talk] how to export to CSV?
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: > > > > joe > phone1 > phone2 > phone3 > sue > cell4 > home5 > alice > atrib6 > x7 > y9 > z10 > 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
Re: [basex-talk] how to export to CSV?
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: joe phone1 phone2 phone3 sue cell4 home5 alice atrib6 x7 y9 z10 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
Re: [basex-talk] how to export to CSV?
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
Re: [basex-talk] how to export to CSV?
Take a look here https://en.wikibooks.org/wiki/XQuery/Displaying_Lists Loren Cahlander > On Feb 15, 2020, at 10:40 PM, Graydon 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