[Solved] Query export as CSV

Mickey12 · Post by **Mickey12** » Wed Apr 29, 2020 4:30 pm

Hi is it possible yet to save and export a query in csv format?
Thanks

edit this topic's initial post · Post by **Villeroy** » Wed Apr 29, 2020 7:51 pm

Create a view instead of a query and a text table with matching column types. Drag or copy the view icon onto the text table icon.

Mickey12 · Post by **Mickey12** » Thu Apr 30, 2020 10:09 am

Thanks for the replies. What would the SQL command look like for creating the text file with fields First Name Last Name (from clients table) and N (from the tags table)

Post by **robleyd** » Thu Apr 30, 2020 10:37 am

How is the relationship between client table and tags table defined? Is it a one to one. one to many or many to many relationship?

Mickey12 · Post by **Mickey12** » Thu Apr 30, 2020 11:24 am

Hi one to many

edit this topic's initial post · Post by **Villeroy** » Thu Apr 30, 2020 11:39 am

Call the query designer, add all 3 tables and double-click the fields you need. Existing relations will be added automatically as defined in the relations designer.
The resulting SQL will look like this with B as linking table:

Code: Select all

SELECT A.X, C.Y 
FROM A, B, C
WHERE A.ID = B.AID
AND C.ID = B.CID

this would equivalent:

Code: Select all

SELECT A.X, C.Y 
FROM A 
JOIN B ON A.ID = B.AID
JOIN C ON C.ID = B.CID

The query designer always creates the first variant.

All sorts of joins: [SQL] JOINing 2 row sets

Mickey12 · Post by **Mickey12** » Thu Apr 30, 2020 12:57 pm

Struggling with this one.
I want to be able to export the QueryforCSV query in CSV format.
I tried doing a table in Tables Create View and copying the query into that table. Shows as text.
The SQL commands to create a text table in CSV format referencing the QueryforCSV query is for now beyond me.
Regards

edit this topic's initial post · Post by **Villeroy** » Thu Apr 30, 2020 2:36 pm

I still use the extracted version of Sunday's "Integral Health2.odb" of 2020-Apr-26. Otherwise I have no database to solve the problem with.

Starting with view

Code: Select all

SELECT "Clients".*, 
    "Tags"."N" AS "Tag" 
FROM "Clients_Tags", "Clients", "Tags" 
WHERE "Clients_Tags"."CID" = "Clients"."IDCLIENTS" AND "Clients_Tags"."TID" = "Tags"."ID"

we get all the clients fields plus the tag names.

The following statement creates a new text table "Export" based on the Clients table definition in the script file plus the tag name field. Base can not do that. It has to be done in plain SQL on the bare bones of the database.

Code: Select all

CREATE TEXT TABLE "Export"(
    "IDCLIENTS" INTEGER NOT NULL,
    "Email Address" VARCHAR(50),
    "First Name" CHARACTER(10),
    "Last Name" VARCHAR(50),
    "Mobile" NUMERIC(50),
    "Home" NUMERIC(50),
    "Subscribed" BOOLEAN,
    "Updated" DATE,
    "Tags" VARCHAR(100),
    "Comments" VARCHAR(50), 
    "Tag" VARCHAR(100)
);

Don't forget to call menu:View>Refresh Tables in order to notify Base that you have changed the database structure.
This connects out text table definition with a concrete file ClientsTags.csv using the default settings as described in the HSQL text table documentation:

Code: Select all

SET TABLE "Export"  SOURCE "ClientsTags.csv";

--------------------------------------------------------------
Now I can copy the view icon, select the "Export" icon, paste and simply confirm the dialog with button [Create] because I have taken care of the column order and all the column types as you can see when you click [Next >].
The csv file ClientsTags.csv can be found in the database directory and looks like this:

Code: Select all

0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Life Coaching
4,joe@gmail.com,Joe       ,Bloggs,,,true,2020-04-23,,,Biokineticist
0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Osteopath
0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Psychiatrist
0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Coach
0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Addiction Recovery
0,bird@nyc.jazz,Charlie   ,Parker,,,false,,,testing,Trauma Therapist
4,joe@gmail.com,Joe       ,Bloggs,,,true,2020-04-23,,,Alternative

No column labels, comma separated columns, ISO dates, point decimals (if there were any), no quotes around text. These defaults can be canged in the SET TABLE statement.

The copy&paste operation appends data from the view to the text table. If the text table is not there, it will be generated on the fly according to your specifications.

Mickey12 · Post by **Mickey12** » Thu Apr 30, 2020 4:38 pm

Hi I really appreciate all your help.
I'm getting some errors on running the code.

"Comments" VARCHAR(50),
"Tag" VARCHAR(100)
)]
also
1: user lacks privilege or object not found: Export

Perhaps Sunday's and today's attachment are a bit different?
Thanks

edit this topic's initial post · Post by **Villeroy** » Thu Apr 30, 2020 5:24 pm

It says CREATE TEXT TABLE "Clients"( ... because I copied the table definition from the existing Client table in order to use it with some modifications for the "Export" table.

1) it should be CREATE TABLE "Export" (which I forgot to change). You are free to use any other name if you modify the SET TABLE statement accordingly.
CREATE TEXT TABLE creates a special database table to be filled with data from text files. Base can't do that. You have to talk to HSQL directly.
2) I did change the ID field to a normal integer. It is no longer unique and can't serve as primary key in this context.
3) I did append the "Tag" VARCHAR(100) field for the tag names

You find the whole SQL which defines your tables, indices and relations in the database's script file. Running that script file creates a copy of your database without data.

edit this topic's initial post · Post by **Villeroy** » Thu Apr 30, 2020 5:30 pm

Dropping a table, query or view into a spreadsheeet and then saving the spreadsheet as csv is viable but more prone to errors and inconsistencies because by default Calc exports all data as shown in the cell grid which may be exactly what you want or not, and it may change with some obscure setting. SQL simply does the right thing.

Mickey12 · Post by **Mickey12** » Thu Apr 30, 2020 6:46 pm

One step at a time.
All 3 of your scripts ran ok.
Export table created (blank)
Not sure about this, but I created a query and named it Clientstags.csv
Then did copy and paste to the export table.
I got An error occurred. Do you want to continue copying?
Clicking yes does not do anything.
I have an idea that I am not doing something correctly

edit this topic's initial post · Post by **Villeroy** » Thu Apr 30, 2020 6:56 pm

Yes, the text table is empty since there is no text file or an empty text file. It is just the definition of a blank table which reads from and writes to the text file specified in the SET TABLE command.

The query needs to be a view in this case.
If your query runs OK and delivers the right row set:
1) Right-click>Open the query in SQL mode and copy the SQL code.
2) Switch to the tables section and call menu:Insert>View(Simple)...
3) Paste the code and save the view.

From now on, you only need to copy the view icon, select the text table icon and paste in order to fill the text file. If there is no text file, it will be created from scratch. If there is a text file, the row set will be appended.

Open the text table. It should contain the copied row set.
FInd the corresponding text file which actually keeps the text table data and open it with any program you want.

Mickey12 · Post by **Mickey12** » Fri May 01, 2020 11:56 am

Good Morning
I get the following error and information when I try to copy the View1 icon in tables and paste it in the Export icon in tables
SQL Status: 22018
Error code: -3438

data exception: invalid character value for cast

edit this topic's initial post · Post by **Villeroy** » Sat May 02, 2020 10:25 am

I opened the old document with the embedded database from the other topic.where we solved the many-to-many problem.
I added a query, created the text table and linked it with my 3 statements of 2020-04-30 12:36 (SELECT ... , CREATE TEXT TABLE ... SET TABLE ...)
Then I created a view from the query (right-click>"Create As View..."). The view name does not matter. Notice that views can't be edited in the Base GUI once they are saved. Therefore I keep the query which makes it possible to edit the SELECT statement in the Base GUI and then delete and rebuild the view from the query.

When I copy the view icon and paste onto the text table icon, I dump data to file ClientsTags.csv in the database directory, which is the document's directory.
The resulting document is attached here. Open it, go to the tables section, click on "ExportView", copy, click on "Export", paste, confirm the import dialog.

----------

Now I can add a driver directory with a new hsqldb.jar, run my macro pyDBA/ExtractHSQL/Main and get a working stand-alone HSQL2 database from the embedded one.
When I copy the view icon onto the text table icon, I notice 2 differences:
1) The text table icon "Export" shows the same icon as a normal table. In the embedded database it had a special icon with a green arrow. Nevertheless, it is the same text table. It may be a good idea to rename it in order to not confuse it with a normal table. That's easy: ALTER TABLE "Export" RENAME TO "Export_CSV" [and HSQL modifies the SET TABLE statement automatically]
2) The text file is created in the database directory which is now the subdirectory "database". It is the same text file as created by the embedded version.

It might be a better strategy to develop the database as an embedded one in the Base GUI and finally convert the working thing to a stand-alone database. At least this works as long as you don't need any HSQL2 functionality (regular expressions, dateadd function). Alternatively, you can develop a stand-alone HSQLDB with certain database development suites such as SQirreL In any case it is worth learning everything about CREATE, ALTER and DROP statements to overcome the limitations of Base's database design tools. With CREATE, ALTER and DROP you create, alter and drop tables, column, indices and relations. Download some example database from here with some relations and start excercising.

edit this topic's initial post · Post by **Villeroy** » Sat May 02, 2020 1:52 pm

This topic has a parallel topic which is very similar: viewtopic.php?f=9&t=101686

Mickey12 · Post by **Mickey12** » Sat May 02, 2020 6:19 pm

Thanks for all your help and useful information. I will tackle this tomorrow.

edit this topic's initial post · Post by **Villeroy** » Mon May 04, 2020 9:17 am

A HSQL2 database with text table and export view.
Extract the database document and the database folder from the zip, add a driver directory with a hsqldb.jar (you have one) and run my extraction macro pyDBA/ExtractHSQL/Main (you have used that). It will reconnect the document with the database directory on your computer. Currently it is linked to a directory on my computer which can not be found on your computer.

Now you get a file Export.csv when you copy the ExportView icon, select the Export icon and paste. The text file is UTF-8 encoded which is the most common standard. Columns are comma separated.
HSQL does not create any header row. If you need one, add it manually.
The text table uses the client id number as primary key. If you paste your current data set into the text table and the text table is not empty, you get an "integrity violation" error because you can not paste duplicates. Simply remove the existing Export.csv, menu:View>Refresh Tables and copy/paste again.

I adjusted some column types and the respective form fields as written in a PM.
I removed the embedded HSQLDB from the document. This database depends on HSQL2 because of the use of GROUP_CONCAT which concatenates the tag names.
The query "qExport" is the editable version of the ExportView which is not directly editable in Base. If you need to do any changes, apply them to the query, then delete the view, create a new view in SQL mode and paste the SQL code from the query to the view.

Mickey12 · Post by **Mickey12** » Mon May 04, 2020 12:54 pm

Hi There. I cannot thank you enough for this! All works as I would wish it to work. The updated form is also a pleasure to work with. I do notice I now have two dbases with the same name. I will just delete my old one and continue adding data to the new one. Regards.
(now to get a mac to access this via a lan)

Mickey12 · Post by **Mickey12** » Mon May 04, 2020 2:50 pm

I just realised that the query will always create a complete list of clients and tags. This is needed in some instances however I also need to be able to enter a particular tag and get a resulting list of names with that tag and then do the copy and paste to create the csv file.

edit this topic's initial post · Post by **Villeroy** » Mon May 04, 2020 6:58 pm

This is a little bit tricky. I have a simple solution where you pick one tag, say "Alternative" and you get all clients having this tag. However, the selected "Alternative" is the only tag that is shown in the output. I guess you want all clients having that tag among all the other tags which should also be written to the output file.
Are you sure that filtering by one particular tag (or none) is enough? You don't need a filter showing clients having a combination of tags?

edit this topic's initial post · Post by **Villeroy** » Mon May 04, 2020 9:14 pm

OK, this exports all tags of the clients having one selected tag. Replace the database document and reconnect it by calling pyDBA/ExtractHSQLDB/Main. Replacing the document only won't touch your database. There is a new form "Filter Form". Pick a tag on the left and click OK. In the middle you should get clients having that tag. On the right you see the tags of the selected client. All clients should have the seleced tag. There should be no client not having the selected tag.
On top of the tags list there is a blank entry. Choose this one and click OK in order to clear all filtering.
This filter affects the ExportView which is pasted onto the text table "Export". The new "Filter" table stores the selected tag-id in the row with ID 0, so it can be used in queries and views. The filter is active until you clear the TID by using the filtering form or directly from the filter table.

Edit: All the objects in the tables section are stored in your database, therefore the new document won't show the filter table nor will it replace the definition of ExportView.
1) CREATE TABLE "Filter"(ID INT PRIMARY KEY, "TID" INT)
2) INSERT INTO "Filter" VALUES(0,NULL)
3) Delete ExportView. Go to the queries section. Right-click "qExport" and choose "Create as view...". Enter ExportView as name. Fixed.

Mickey12 · Post by **Mickey12** » Tue May 05, 2020 11:04 am

Hi there. Much thought on your side has gone into this.
1. Not too sure what is meant by replace the data base document though.
2. Can I specify a new path to reconnect to? I have started a new dbase called Integral Health2 New in a different folder as I seemed to be creating lots
of files called IntegralHealth2 .odb

edit this topic's initial post · Post by **Villeroy** » Tue May 05, 2020 11:26 am

You have a directory with a database subdirectory, a driver subdirectory and a database document Integral Health2.odb.
Download yesterdays attached document Integral Health2.odb and replace your exising Integral Health2.odb with the new one.
I also made 2 changes to my database which is no longer embedded in the Base document.
In order to reproduce my adjustments to the database, call menu:Tools>SQL... copy the following 3 statments and execute them

Code: Select all

CREATE TABLE "Filter"(ID INT PRIMARY KEY, "TID" INT);
INSERT INTO "Filter" VALUES(0,NULL);
DROP TABLE "ExportView"

Go to the tables section and call menu:View>Refresh Tables
There is a new "Filter" table now with 2 columns and one row.
The old "ExportView" is gone. There is a new version of it in the queries section but we need it in the tables section. Right-click query "qExport">Create as View... and enter the name ExportView when prompted for the name.
All these steps will not touch any data you may have stored in your database meanwhile.

Mickey12 · Post by **Mickey12** » Tue May 05, 2020 12:12 pm

I have two of each. database subdirectory, driver subdirectory,Integral Health2.odb document
They are in different areas on my PC
Also a Integral Health2 New.odb document. This is the one I use as I have started entering data.
Not sure how to proceed now with anything.

edit this topic's initial post · Post by **Villeroy** » Tue May 05, 2020 1:10 pm

I'm referring to my version of May 04, 2020 attached as a zip file with document and database.

Mickey12 wrote:Hi There. I cannot thank you enough for this! All works as I would wish it to work. The updated form is also a pleasure to work with. I do notice I now have two dbases with the same name. I will just delete my old one and continue adding data to the new one. Regards.
(now to get a mac to access this via a lan)

The new one.

Mickey12 · Post by **Mickey12** » Tue May 05, 2020 2:56 pm

Ok. How do I incorporate your version of May 04, 2020 into Integral Health2 New.odb?
Apologies for the rookie questions.

edit this topic's initial post · Post by **Villeroy** » Tue May 05, 2020 4:02 pm

Simply use this database document instead of the other one. Connect it with your database folder, run the SQL code and create the new view.

Mickey12 · Post by **Mickey12** » Tue May 05, 2020 4:55 pm

Everything working like magic now!
Thank you for your time and care in providing explanations as well.
Regards

edit this topic's initial post · Post by **Villeroy** » Tue May 05, 2020 5:09 pm

No, this is not magic. Therefore I recommend to test this carefully if it really does the right thing.

[Solved] Query export as CSV

[Solved] Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Query export as CSV

Re: Solved Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV

Re: [Solved] Query export as CSV