Export CSV File from more than a single query

I need to generate a csv file for my application, however the data needs some values formatted before it is written to the file.

Here is an example
field1,field2,field3.“2018-07-27 11:07:58”,field4

Above all the field1,2,3,4 entries are perfect as they are returned however for the date i need it formatted to YYYY-MM-DD only and the time needs to be removed. Is there a way within the query to format the returned data. Or is there a way to join multiple queries together in the Export Data step of the Export CSV File component, i have tried a number of ways bue most often it returns an error saying “Data is not an array.” so im not sure how to get around this unfortunately.

did you try view (mysql view)

Or you could create an extra column in your table,eg:

ADD COLUMN date date AS (cast(ts_modified as date)) VIRTUAL NULL AFTER ts_created

I don't know much about virtual columns but I think this will work. This will take no extra space in your database, but I suppose it's extra data to be downloaded. It would be interesting to know if there is away to do this in Wappler.

Hi @s.alpaslan can you please explain what you mean in more detail, I’m a bit unsure.

@TomD if it were just a single field with this sort of issue that could be a possibility however I probably have about 30 columns across 6 tables with this sort of issue. I’m going to try research the virtual columns thing though because I have never even heard of it, so maybe it will work.

@psweb maybe this link will help

yeap you can use view , virtual table , temp tables or call procedures (but wappler doesnt support call procedures )…

Ok @s.alpaslan & @TomD, you guys are obviously MySQL experts, I am testing and it seems like this could actually work, thank you both so much, never knew you could even create a virtual table or a virtual column in MySQL, damn this could be a life changer for me. Very very happy.

I will probably be asking more about this very shortly, when i get the first section working :slight_smile:

Ok so my questions are coming up way faster than imagined, unfortunately i have to keep within the parameters of the Sage Pastel Accountancy package that will be importing this CSV File. Here is their spec.

http://stage.helpcentre.pastel.co.za/xpress/xpress-exporting-and-importing-data/?open=913#accordion-15

The problem i have is that the CSV needs what they call a Header row with a single row or 23 fields containing dynamic values.
Then it needs multiple Detail rows, with the product data, this needs 14 fields containing dynamic values.
Because the header row and the detail rows have different column amounts i assume this is not a conventional CSV Header row.

Any Suggestions of how i could do this

are you using dw extensions or wappler ? Which one ?
Because we cant coloumn mapping with wappler with query builder. But you can do coloumn mapping with dmxzone dreamweaver (query builder).

if you done other steps you can use default value (with dmxzone data formatter) while data importing …

If you are using wapplers you will not be able to do this.
This feature removed from query builder …

Hmm, ok i see, I have both with a year subscription so i could use DMXZone Query Builder.

Just an idea, but i think it could work, just have to figure it out.

If I right click App in App Structure and choose to Add Data > Variable, give it an id of var_csv, then I can bind all the values i want directly into it, like dmx-bind="‘Header’,serverconnect1.data.query1[0].field1,serverconnect1.data.query1[0].field2,serverconnect1.data.query1[0].field3"

Then in Server Connect, i create an action called CSV
Global > $_GET > Right Click Add Array add LinkedField of var_csv[] and select the multiple checkbox
Then in Execute > Steps, i add a database connection, and a Export CSV File with Export Data set to {{$_GET.var_csv}}

However i have tried this in every wa i can think of and all i keep getting back is an error about the data not being an array.

This should work in the same way as a multi file upload step, i mean you can store 30 uploaded names all into your database in the same way so i do not really understand why it does not work in the same way.

So I think I am almost there, what I have done is create a dummy hidden form, in the form i am adding dummy inputs, in each input i am binding the relative values, which means i can do all the data formatting i want too.
I have given all the inputs a unique id header1, header2, header3, header4
I have given all the inputs the same name header[]

In my action, in Globals i imported my form, which gave me a single $_POST variable named header, with a linked field of header[] and the check box for multiple selected.
In my Export CSV File step for Export Data I have {{$_POST.header}} and a delimiter of ,

Well it writes a csv file now to my server, and when i open the csv file all my values are in it, but instead of comma delimited each record is on its own row. hmm, so close, yet so far.

So my output looks like this
Header
SH277
" "
Y

And should look like this
“Header”,‘SH277’," ",“Y”

can you share screen shot of server connect …

Sure

yeap true, because this is an array … if you want to return your result like “header 1,header 2, header 3 …” you can use split

The Export CSV File has to have an array in the Export Data field, so i could leave all the inputs as separate values, of header1 and header2 and header3 and header4, and then try put them into an array using the Set Value step but I am not sure how to enter the Expression for Set Value

I Tried
{{$_POST.header1}}{{$_POST.header1}}{{$_POST.header1}}{{$_POST.header1}}
Then
{{$_POST.header1}}.{{$_POST.header1}}.{{$_POST.header1}}.{{$_POST.header1}}
Then
{{{{$_POST.header1}}{{$_POST.header1}}{{$_POST.header1}}{{$_POST.header1}}}}
Then
{{$_POST.header1}}+{{$_POST.header1}}+{{$_POST.header1}}+{{$_POST.header1}}

its like i need a component to add values into an Array

This is where I am at now…

First off thanks @s.alpaslan and @TomD for the advice on using Create View virtual tables, without that, I would be nowhere.

I have managed to create a virtual table for the header with its 23 columns to always return a single row, and also to format the data exactly the way i want it.
Then I have managed to create a virtual table for my detail section with its 14 columns to only return the rows i want, and again format the data exactly as i want it.

In Wappler I have managed to query each of these virtual tables and make it write 2 dynamically named csv files one called header_277.csv and another called detail_277.csv and the 277 changes according to the invoice number in my application so it will always be unique.

Now all I need to somehow do is get these 2 files to merge into one single file when a user clicks a button on my app, in php i would have done something like

file_put_contents('final_277.csv',
file_get_contents('header_277.csv') .
file_get_contents('detail_277.csv'));

But I do not think i can do this type of thing when i need a user to click a button to make it execute, do either of you have any other ideas for me, or @Teodor if you still around.

@psweb congratulations… :+1:

I dont know … how to this with wappler … I think merge files imposible.
But
Maybe …

  1. create temp table in mysql
  2. Create multiple query in server action. read tables "header, detail etc… " then insert them into new temp table
  3. create triger in mysql (use after insert or before insert)
  4. trigger will write all data to new table
    then you will read with wappler and export

Thats exactly what i tried first but i must be doing something wrong because my results to my MySQL query need to be structured like this

header1, header2, header3, header4, header5
detail1a, detail2a, detail3a
detail1b, detail2b, detail3b
detail1c, detail2c, detail3c

What MySQL gives me is like this where it repeats header1, header2, header3, header4, header5 in each row.

header1, header2, header3, header4, header5, detail1a, detail2a, detail3a
header1, header2, header3, header4, header5, detail1b, detail2b, detail3b
header1, header2, header3, header4, header5, detail1c, detail2c, detail3c

Do you know if there is a way around this while creating my temp table in MySQL

And only a short 37 hours later i have this damn thing working… ouch.