Cumulus Support

I've had a request from someone who works at the university of bath, who is studying the river Frome, they would like me to share certain weather value for each day.

I know how to dump my SQL data to PDF's or CSV's etc, but is there a way to select just the columns I need and dump those in to a file?

You could just do a "select" of the data required in phpMyAdmin, then "export" the resulting rows.

As far as I am aware phpMyAdmin even in Custom export will only export all columns in a given table.

Personally I would export it as a CSV file, load it into Excel and just remove the columns you do not want by deleting the column.

Or as mark says

mcrossley wrote: ↑Thu 11 Aug 2022 11:01 am You could just do a "select" of the data required in phpMyAdmin, then "export" the resulting rows.

It's not the rows that I need, I need to pick out temperature, wind, solar, pressure, rainfall

I guess I'll have to download excel and edit it that way, as David suggested.

If this is required on regular basis you don't want to do that manually through excel isn't it.

I assume you can just do a dump that in a file and pass it on.
If you can't do that automatically, whats the use of having SQL?

HansR wrote: ↑Thu 11 Aug 2022 11:54 am If this is required on regular basis you don't want to do that manually through excel isn't it.

I assume you can just do a

Code: Select all

select [fieldname(s)] from [tablename]

dump that in a file and pass it on.
If you can't do that automatically, whats the use of having SQL?

The gentleman who wants the data wants to have it in a PDF or CSV format.

The question was about phpMyAdmin, and my method works for that, you just get the columns you select in the file.

But if you want it automating then you will have to ditch the GUI and use the command line...
eg.

Create a config file on your path - .my.cnf (You may want to secure that file)

EDIT: from MySQL 5.6 you can use the mysql_config_editor utility to create that file for you - and bonus, the password will be encrypted.
https://dev.mysql.com/doc/refman/8.0/en ... ditor.html

Then you can just run a command like...

Create View is the standard way in SQL to define just once the ability to see just selected columns from a table, I'm sure some Forum users have experience of using views.

phpMyAdmin supports defining default settings for export, so only need to define once. Settings not only support export output as CSV or PDF (as Mark says) but also whether export lists data in table or does a create view.

I have never tried creating a view in phpMyAdmin, nor using that option in Export, so it might not work as required. If you have time for experimentation, maybe could be explored.

I kicked in on Mapantz's original post which insinuates a daily action. That will be boring after a while, they invented computers for that

Mapantz wrote: ↑Thu 11 Aug 2022 10:28 am I've had a request from someone who works at the university of bath, who is studying the river Frome, they would like me to share certain weather value for each day.

So I think Mark's solution does well, you might even mail it automatically

mcrossley wrote: ↑Thu 11 Aug 2022 12:37 pm Then you can just run a command like...

Code: Select all

mysql SELECT LogDateTime, temp FROM realtime INTO OUTFILE '/tmp/realtime.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

I second using views. I use views to amalgamate my extra sensors data with standard MX data. But the reverse applies equally, if you need a cut-down view of a single table.

Thanks for the replies.

I don't need anything automated, the guy just wants the data up to this point in time.

I still see no option to be able to select certain columns in the dayfile to export, only rows?

Hold up; under the 'structure' bit?

No, just browse the table and edit the SQL, or select the SQL tab. Both will have the default "SELECT * FROM Dayfile...", just change the select * to the columns you want

Got it!

Thanks folks!