SQL Database Client GUI for Firebird?

[linux guy]

Hi people.

I have a huge CSV spreadsheet. I need to do a bunch of light duty data
manipulation.  Sort, total, export, that sort of thing.  I could almost do
it in the spreadsheet, but it would be too much copy/paste and the
combinations of data I want to get out are exhaustive.

I'm thinking of importing it into Firebird.   I'm looking for a good GUI
SQL client.

Thoughts/ advice ?

TOar ?
DBEaver ?
Something else ?  (Python?)

Thanks
LG

[Suvayu Ali]

On Fri, May 29, 2020 at 11:39 PM linux guy <linuxguy123(a)gmail.com&gt; wrote:
>  Hi people.
>
>  I have a huge CSV spreadsheet. I need to do a bunch of light duty data manipulation. 
> Sort, total, export, that sort of thing.  I could almost do it in the spreadsheet, but it
> would be too much copy/paste and the combinations of data I want to get out are
> exhaustive.
>
>  I'm thinking of importing it into Firebird.   I'm looking for a good GUI SQL
> client.
>
>  Thoughts/ advice ?
>
>  TOar ?
>  DBEaver ? 
Good GUI client, you could use it with SQLite and get the job done effectively

>  Something else ?  (Python?) 
I prefer Python, particularly Pandas.

  import pandas as pd
  df = pd.read_csv("/path/to/giant.csv", <other options, but usually
not required>)

Then you can use the Pandas API to do whatever you like really (see
the official docs, very thorough, and easy to understand, full of
examples).  If you prefer an SQL interface, you can try installing
`sqldf` alongside pandas, and run SQL inside the pandas DataFrame.

HTH,

-- 
Suvayu

Open source is the future. It sets us free.

[linux guy]

Thank you !
Great answer.  This is why I ask these questions here !

LG

On Fri, May 29, 2020 at 5:51 PM Suvayu Ali <fatkasuvayu+linux(a)gmail.com&gt;
wrote:

>  On Fri, May 29, 2020 at 11:39 PM linux guy
> <linuxguy123(a)gmail.com&gt; wrote:
>  >
>  > Hi people.
>  >
>  > I have a huge CSV spreadsheet. I need to do a bunch of light duty data
>  manipulation.  Sort, total, export, that sort of thing.  I could almost do
>  it in the spreadsheet, but it would be too much copy/paste and the
>  combinations of data I want to get out are exhaustive.
>  >
>  > I'm thinking of importing it into Firebird.   I'm looking for a good GUI
>  SQL client.
>  >
>  > Thoughts/ advice ?
>  >
>  > TOar ?
>  > DBEaver ?
>
>  Good GUI client, you could use it with SQLite and get the job done
>  effectively
>
>  > Something else ?  (Python?)
>
>  I prefer Python, particularly Pandas.
>
>    import pandas as pd
>    df = pd.read_csv("/path/to/giant.csv", <other options, but usually
>  not required>)
>
>  Then you can use the Pandas API to do whatever you like really (see
>  the official docs, very thorough, and easy to understand, full of
>  examples).  If you prefer an SQL interface, you can try installing
>  `sqldf` alongside pandas, and run SQL inside the pandas DataFrame.
>
>  HTH,
>
>  --
>  Suvayu
>
>  Open source is the future. It sets us free.
>  _______________________________________________
>  users mailing list -- users(a)lists.fedoraproject.org
>  To unsubscribe send an email to users-leave(a)lists.fedoraproject.org
>  Fedora Code of Conduct:
>  https://docs.fedoraproject.org/en-US/project/code-of-conduct/
>  List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines
>  List Archives:
>  https://lists.fedoraproject.org/archives/list/[e-mail]
>

[linux guy]

Update
I just got this task done.
I did not use Pandas, mainly because I wanted to work in SQL with a real
database instead of a CSV file.

I used sqliteBrowser to import the data from the CSV file.  It worked well
except that it got the data type wrong on some of the 150+ columns (fields)
and I had to go in and edit them manually.  It did this because the data in
the rows that it looked at was inconsistent.  It didn't make a mistake, the
data it was given was garbage.

I was able to edit the fields easily enough, but it was slow because the
user interface is a bit clunky.  No biggie, just took longer than it might
have.  Still worked well though.

At that point I had a big table that needed to be converted into actual
database records so that I could manipulate them properly with SQL.  My
table was 110K x 150 (row x  column).  I needed to convert that to ~16.5M
individual records.

I tried doing this with SQL statements and an SQL wizard could probably do
it, but I found it frustrating.  It wasn't that I couldn't convert the
individual cells into a record, it was that the records needed some data
checking and manipulation before being inserted into a new table.

I turned to my trusty friend, Python and found sqlite3, which is a Python
driver for accessing sqlite.  From there things went really well.

I encountered a small problem when I hit empty cells that were NULL in the
source table.  I needed to convert them to 0.0.   I found out that they
read as None in Python and so my fix was:

output = row[column]
       if (output is None):  #this is for a NULL field in the db
            output = 0.0
My hangup was that I tried comparing (using ==) them to "", '',
"None",
NULL, Null, etc.  Finally I realized that Python read them as None and then
it worked.

We are using this data to make some large decisions.  It is great to have
such fantastic tools available on a moment's notice, for free, to be able
to do this work.  The supplier of the data was pushing to the manipulation
for a hefty hourly fee.  I'm glad we didn't have to resort to that.  Now
that we have good tools to do this work, we can mine this data any way we
want, using SQL and/or Python.

Linux rocks !   Fedora rocks !

Thanks for the quick reply late in the day on a Friday.  Having finished
this up, I don't have to worry about it for the rest of the weekend.