Bad experience with Open Source... OOo

Miscellaneous and Personal Stuff I Must Share
1

Just had to rant. The OpenOffice.org “database” solution is really a piece of crap. Let’s put aside the crashing for now, and deal with the functionality.

Now, I need to use Access for a little side project of my own. I have a fixed width text file that I need to work with. Now that I’m not working in an office any more, I don’t have Access to Access (ha) but I know that it would work suitably for my needs, and Excel won’t cut the mustard, mostly because of the number of records (more than 65k) and also because I don’t need that much processing.

So I hunt around. What’s that new buzzword, Open Source? Hey, it’s free, might as well give it a try…

  1. I can’t import fixed width tables.
  2. I read the help file. It tells me to open then using the *.csv filter. Fine.
  3. There’s no such filter.
  4. I get pissed, read the help file again, google around… nothing. Find that damn *.csv filter
    4a) Follow someone’s advice to just drag and drop. Up comes the silly *.csv filterless dialog. :rolleyes:
  5. I look again. It’s not FRICKIN THERE
  6. Simmer for a bit, wonder if perhaps the filters were in another module that I didn’t install
  7. Install the whole damn thing.
  8. Suddenly, the *.csv filter is there. Okay, fine.
  9. Attempt to import. And what do I find?

THE NUMBER OF RECORDS IS LIMITED TO 65K!! DAMMIT!! THIS IS A FRAKING DATABASE PROGRAM HERE!!! WTF!!!

Now, I’m going to install mySQL on my computer, and access it from my computer through 127.0.0.1 (more rolleyes here) and hopefully it has something that can import fixed width tables.

GRRrrrrrrrrrrrrrrrrrrrrrrrrrr.

2

I got bad news for you: mySQL is open source, too. :wink:

3

uh. . .Doesn’t this belong somewhere else?

That being said, I think that and the power point programs are the weakest links in Open Office.

4

mySQL is a POS.

Use Postgres, or either of the free “express” editions of Oracle or SQL Server instead. :slight_smile:

5

I dunno, I thought it was pretty mild for the pit… and anyway, CS deals with computery things, right?

In any case, mySQL sorta worked… it’s just that I seem to have fogotten all my SQL. :stuck_out_tongue: And dear GOD if there’s any way they could make it less user unfriendly I’m glad they haven’t found it yet. Oh, for the ease of simply of making update queries and select queries and defining your own functions using VBScript…

Sigh. goes and checks Ebay

I may have to deal with this by splitting the file into 3 excel spreadsheets or something…

6

OK, this may be a little bit from way left field.

I give you lots of caveats.

First, a few years ago, I downloaded Oracle 9i to my personal computer, free of charge. Caveat 1, I haven’t done any RDBMS downloads fromt hem since, so I don’t know if the program is still in effect. But, for personal use, it was free. No charge.

Takes up a lot of space, but works like a dream for me.

Caveat 2, it is a full-fledged RDMBS, and you need to know the basics of database administration even if it is single-user.

Caveat 3, it is NOT for commercial use, for that you will need to buy it. But for personal use, you’re OK.

Caveat 4, I don’t think you can get the front end tools like Oracle Forms and ORacle Reports for free, you just get the database and use SQL.

Again, I emphasize, I don’t know if you can still do this, OR what tools you can also get with it. But you might think about investigating this.

See if SQL*Loader is available, since you want to import tables from ??? (some other source). I don’t know how much this impacts your situation or if it might not even be worth it.

7

PostgreSQL

8

Hmm… maybe a little explaination of what I’m intending to do…

I’ve got a fixed width table in plaintext form, with many many many lines that look like this.


0   XAM POS 357.170349 11.582619 1483.619019
0   XAM PYR 0.234 -2.041445 0
25  20  POS 698.834351 9.465625 1774.984375
33  8   PYR 0 1.278561 0

The first number is the number of milliseconds after the last entry something happened, that is, the change in time since the last event.

That’s not very useful, so I need to add a field to it, make a “time index” by summing up all the deltas up to that point. And I completely have no idea about how to go about doing this in raw SQL. :stuck_out_tongue:

9

SELECT SUM(field) AS total FROM mytable

Or something like that. I haven’t done databases for a couple of years now. There should be a SUM function (or ADD maybe) if you look through the reference, so from there it’s a matter of figuring out where in the query it goes.

If you have Perl, you could just write a script like:



open(IN, "<filename.txt") or die "Can't open in file!/n";

$val = 0;
while (<IN>) {
   if (/^(\d+) .*/) {
      $val += $1;
   }
}

print $val;

Not tested so I doubt the above works correctly.

10

Wouldn’t that return the sum of all the timedeltas? I don’t want the sum of all deltas, I only want the sum of all deltas up to that point… now, it’s just saying

“30ms after the last command, this command.”
“12ms after the last command, this command.”
“13ms after the last command, this command.”

I want to sum it such that it will give me (equivalent to the above)

“20000ms after the start, this command”
“20012ms after the start, this command”
“20025ms after the start, this command”

Something like… er…

UPDATE table SET table.timeindex = (SUM (SELECT timedelta FROM table WHERE primarykey < table.primarykey)); (Yes, that’s syntactically wrong, but I hope it gives the idea of what I’m trying to do)

Hmm… actually, now, come to think of it, perhaps if I had 2 seperate tables, it might work… Hmmm…

11

What you are trying to do kind of cuts against the grain of a relational database, where a table is considered to be an unordered collection of rows, so I doubt if it can be done with plain SQL. You could do it procedurally, though, but at the least you will have to specify in what order you want to process the rows.

12

Similar to what Sage Rat said, but this problem can be solved with a perl one-liner:

perl -ne ‘/^(\d+) /; $total += $1; s/^(\d+) /$total /; print;’ input.txt

13

Ah… Well you would need to do something like:

UPDATE mytable SET time=(
SELECT SUM(delta) FROM mytable WHERE item<X
) WHERE item=X

in a loop where you increment X by one for each row in the table. But if you’re doing this at the command line that may be difficult. MySQL probably allows for some sort of scripting language to be run against it, which would allow you to use a loop. But I’m not sure what that is.

Again, you could use Perl. The following should add the new field at the end of the line.


open(IN, "<filename.txt") or die "Can't open in file!
";
open(OUT, ">outfile.txt") or die "Can't open out file
";

$val = 0;
while (<IN>) {
   if (/^(\d+)(.*)/) {
      print OUT "$1$2	" . $val . "
";
      $val += $1;
   }
}

Again, this probably needs to be debugged.

14

That’s four lines. You just put them on one. :stuck_out_tongue:

How is that iterating all the lines? (I never tried figuring out how to do command-line apps so there may be some rule you’re using.)

15

It’s the ‘n’ in the ‘-ne’ command-line switch.

From here
we see:

So, actually, I could have made my one-liner shorter by using

perl -pe ‘/^(\d+) /; $total += $1; s/^(\d+) /$total /;’ input.txt

16

OK, poking around a little more, I have made it into a one-statementer :stuck_out_tongue:

perl -pe ‘s/^(\d+) /{$total+=$1} /e;’ input.txt

17

I think this best fits MPSIMS. Off ya’ go!

18

I myself find MySQL unusable without the aid of phpMyAdmin. Unfortunately, you need PHP first. I recalled that there was some client frontend for mySQL – I used those (which are rather sucky) before using phpMyAdmin.

19

Darn - the link to phpMyAdmin shall be http://www.phpmyadmin.net/
Apologies.

20

PERL; you just gotta hate it.