Post #64,187
11/20/02 10:18:13 PM
|
DTS?
I know that SQL Server DTS services will export and import Excel spreadsheets with ease.
I'm guessing that Access would too, if the data requirements aren't too large.
I do mostly DB2 stuff, but every once in a while, I have to import/export to/from SQL Server and DTS does the job.
I'd say try that.
|
Post #64,190
11/20/02 10:30:17 PM
11/20/02 10:38:33 PM
|
Spreadsheet grows up to be a Real Application
Reading through the other posts, I'm not really sure what you want.
Do you want to have your users populate the spreadsheet with a form interface, then move the data into a database in a batch?
Or, do you want this to be an interactive thing, where you create a form and something behind the form inserts directly into the database (with no batches)? That something could be VB, Access, VC++, C#, or any number of tools...
In the initial post it sounded like you were leaning more toward the first, but the subsequent posts led me to thinking that you might be considering the interactive thing.
I put together a database forms application from a Web Interface using ASP in about two weeks.
The quick and dirty would be to do the form in Excel (since it sounds like you already have the form in Excel), and then use DTS to import to SQL Server.
The interactive thing would take longer, but you're not left doing the import thing once a day, or once a week, etc. But then again, the DTS is so easy that you can easily train an hourly person to do it.
I've seen this happen a number of times at different jobs. You have a spreadsheet that has grown up to the point where it really needs to be an application now. It also has happened with Access. Once you get about 10 users and a few million rows in the tables, and you start doing complex joins, then Access starts breaking and it's time to move up to SQL Server, Oracle, DB2, Sybase, Informix, or something.
The problem is see in most of these situations is that there isn't any funding to create the application. The need is there, but management doesn't understand that what was built doesn't work anymore, and therein lies the problem.
I hope you have understanding management. I sincerely hope you get some budget to do this.
Glen
Edited by gdaustin
Nov. 20, 2002, 10:38:33 PM EST
|
Post #64,243
11/21/02 6:57:58 AM
|
I'm looking at Access and MySQL now.
Thanks for your post. Time for an update on what I'm doing.
My data is in an Excel 97 spreadsheet. It's only about 50 columns by 150 rows at the moment, but it's getting too unweildy to put new data in using Excel. (There are another 100 columns of data (measurements) that should really go in a different table/spreadsheet, so that'll be split off soon.) So I want to make some forms for new data entry, but I want the same interface to be able to display and print old data. Like a database form.
Excel can make forms for applications like this, but seems to be limited in the number of columns it can support. I found a [link|http://sam308.com/excel/html/database_for_microsoft_excel_.html|shareware application] that'll make Excel 97 act more like a database, but it only supports up to 30 columns.
I received the "Running Excel 97" book by Dodge, et al., that I ordered a few weeks ago. It's worthless in trying to figure out the best way to do database applications with Excel. E.g. it mentions nothing at all about the Data -> Template Wizard...
I've ordered "Mastering Access 97 Development" but haven't received it yet. In the mean time I've made a new spreadsheet and exported it into a table that I can use as an Access database. I've been playing with making a form for it using Access's Form Design View, but am amazed (well, not really) that MS was able to ship such a limited tool. E.g. there doesn't seem to be any way to change the "magnification" of the view so that it's possible to align fields without squinting at the screen, it doesn't seem possible to change the grid, the printed form apparently must look like the displayed form (so if you want a dark background on the screen you get a dark background when it's printed), etc., etc. I'm also amazed at the size of the .mdb file - it's already almost 200 kB and I only have 3 columns of data in it (even after compacting)?!? (The Excel spreadsheet is less than 200 kB when fully populated with lots of colors and formatting...) It looks like Access will do what I want, but only after lots of fighting with it. And I still don't like the idea of using it (due to its reputation for being buggy and crashy), and it seems it's not quite as friendly to get the data on a Palm device as Excel.
I've downloaded MySQL for Win32 along with a lot of add-ons (visual tools, etc.) but haven't had a chance to play with it. I'm concerned that many, including [link|/forums/render/user?username=admin|admin] feel that PostgreSQL is much better, but I'm hoping that it'll be easy to migrate to PostgreSQL if the need arises. I haven't been able to find PosgreSQL binaries for Win32 and building it isn't something I feel able to do at present. I'm waiting for a copy of "PHP and MySQL Development" by Welling and Thomson to arrive. Having the data in MySQL and building a web interface to the data should be the best way to go as everyone will be able to use a browser to get at the data.
Do you want to have your users populate the spreadsheet with a form interface, then move the data into a database in a batch?
I want a cleaner interface to add data to some datastore and display old data. Having the data in Excel is convenient because it lets us have it on Palm and PocketPC devices, but it's not really the right tool for this much data nor for what I want to do. I want to be able to do sorting and searching and grouping of the data in ways that Excel isn't really designed to do. So I want it in a database, and once it's in the database we won't go back to keeping data in the spreadsheet (we'll export tables back to Excel if necessary).
I hope you have understanding management. I sincerely hope you get some budget to do this.
Ha! :-) This is just a small part of my job - keeping track of data and making sure it's in a usable form. No budget for it. :-)
Thanks!
Cheers, Scott.
|
Post #64,334
11/21/02 4:49:28 PM
|
Access, MySQL, PostgreSQL? Why don't you consider a REAL...
...RDBMS -- with stored procedures, SQL-code triggers, "Generators" (="Sequences"), UDFs, etc, etc...?
Available in a [link|http://www.interbase.com|fully commercial version], an [link|http://firebird.sourceforge.net|Open Source version], and a [link|http://www.ibphoenix.com|commercially-supported 'certified' variant] of the Open Source version; something for every taste and wallet.
I just don't understand why people persist in futzing around with stupid kludges, when there's so much better stuff out there...?
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
|
Post #64,342
11/21/02 5:03:11 PM
|
Christian, WTF are you on about?
Point out one of those features that PostgreSQL doesn't have, apart from the commercial version...
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,369
11/21/02 6:59:02 PM
11/21/02 7:03:19 PM
|
In that case, I'll point to one it *does* have:
A certain Web Forum administrator constantly whining about its horrible drivers, that's one feature PgSql certainly *does* seem to have! :-)
Naah, might not be too much wrong with PgSql; it just annoys the Hell out of me how it seems that once people realize MySql isn't the only open-source RDBMS in the world, it's only because they think PgSql is the only alternative... :-(
Oh yeah, one more thing: How big is PgSql? It just struck me, one other feature it might have, might be Codus Bloatitis... Is it a lot more than a two-and-a-half meg download?
Not that the features I mentioned are the only ones IB/FB has; there's a lot more (among them, a few things Soothsayer is advertising as Great Innovations... Dunno how long IB/FB has had External Tables, f'rinstance).
But the main fault with it, IMO, is its sheer "mind-share", which AFAICS can't help but be totally out of proportion to its merit (and in that sense is, at least in large part, "undeserved"); a "feature" where, it seems to me, PgSql is beaten only by MySql.
See where I'm coming from?
[Edit:] So the "persist in futzing around with stupid kludges" bit was really more about Access and MySql; PgSql got thrown in mostly because it was -- NOW do you SEE where I'm coming from?!? -- the only alternative to those that seemed to have even OCCURRED to the Other Scott.
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
Edited by CRConrad
Nov. 21, 2002, 07:03:19 PM EST
|
Post #64,413
11/21/02 10:23:35 PM
|
Um, my tinfoil hat seem to work better on you than on admin.
:-)
I looked, a little bit, at lots of other databases. Paradox 10, Interbase/Firebird, Filemaker Pro, and others. I'm not wedded to Access nor MySQL - it's just a place to start for the moment.
...PgSql got thrown in mostly because it was -- NOW do you SEE where I'm coming from?!? -- the only alternative to those that seemed to have even OCCURRED to the Other Scott.
I thought my post was too long as it was! :-)
Cheers, Scott.
|
Post #64,443
11/22/02 2:23:48 AM
|
Yours?!? Isn't it *I* who should be wearing one?
The Other Scott: I looked, a little bit, at lots of other databases. Paradox 10, Interbase/Firebird, Filemaker Pro, and others. I'm not wedded to Access nor MySQL - it's just a place to start for the moment. Yeah, we know that *now*... But, no prob: ...PgSql got thrown in mostly because it was -- NOW do you SEE where I'm coming from?!? -- the only alternative to those that seemed to have even OCCURRED to the Other Scott. See how by this stage, I used the _past tense_ of "seemed"? :-) I thought my post was too long as it was! :-) Yours (almost?) never are.
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
|
Post #64,431
11/21/02 11:33:32 PM
|
PostgreSQL Python drivers...
... are not distributed with Postgres... they are '3rd party' drivers.
The source download is about 9 megs, which includes the backend, utilities, interface APIs (jdbc, tcl, c/c++, odbc, perl, python {not the one I'm using}, and a couple other I don't recognize), the stored proc languages (plpgsql, tcl, perl, python), and the test suite.
The full suite of RHAT RPMs looks to be about 6M or so.
What are External Tables?
Does Interbase have polymorphism/inheritance for its tables?
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,447
11/22/02 3:14:19 AM
|
Lot better than I thought... All your fault, though! ;^)
AdmiScott: PostgreSQL Python drivers [...] are not distributed with Postgres... they are '3rd party' drivers. Yeah, I suppose I knew that, actually... Hey, I was only trying to make my outburst seem half-way coherent; don't go robbing a poor man of his cobbled-up defense, nowwouldya!?! :-) The source download is about 9 megs [...] The full suite of RHAT RPMs looks to be about 6M or so. Wow, that's a LOT better than I thought -- I had the impression it must be in the tens (at LEAST -- possibly HUNDREDS, I'd have thought!) of megabytes. That must be because you (and p[ossi|roba]bly some others) have so often compared it to Oracle -- which, as you know, doesn't even fit on a single CD! (WTF do they DO with all those hundreds of megabytes?!?) [...] which includes the backend, utilities, interface APIs (jdbc, tcl, c/c++, odbc, perl, python {not the one I'm using}, and a couple other I don't recognize), the stored proc languages (plpgsql, tcl, perl, python), and the test suite. That may well be a bit more than at least FB's ~2.5-MB download includes; depends a bit on what you mean by "utilities", and I can't recall at the moment what language inerfaces it includes (probably not as many). And IB proper, while it probably has more stuff, is of course a whole lot bigger, too. What are External Tables? The most elegant, IMO (and perhaps easiest), way of getting external data into your DB: just declare a table as residing not actually in your db-file/"table space", but in an external (flat ASCII) file. If you want to move data into the DB permanently (which I assume is a requirement in order to get more efficient indexing, and prolly stuff like referential integrity and so on), just do an SQL copy to another, regular DB-internal, table. You'll have to define field delimiters or column positions, perhaps line-end styles, and so on -- but at least you don't have to mess around with separate programs, which of course require that anyway (coughSQL*Loadercough), any more. Does Interbase have polymorphism/inheritance for its tables? Not that I know of... What is *that*? (Unless you mean the easy way of "inheriting" structure as well as data into a new table in some RDBMSes, by doing a " SELECT <whatever> INTO <New Table Name>"? :-)
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
|
Post #64,457
11/22/02 5:32:44 AM
|
Re: Lot better than I thought... All your fault, though! ;^)
(WTF do they DO with all those hundreds of megabytes?!?) They keep the HD manufacturers in business with them, of course... What are External Tables? The most elegant, IMO (and perhaps easiest), way of getting external data into your DB: just declare a table as residing not actually in your db-file/"table space", but in an external (flat ASCII) file. If you want to move data into the DB permanently (which I assume is a requirement in order to get more efficient indexing, and prolly stuff like referential integrity and so on), just do an SQL copy to another, regular DB-internal, table. You'll have to define field delimiters or column positions, perhaps line-end styles, and so on -- but at least you don't have to mess around with separate programs, which of course require that anyway (coughSQL*Loadercough), any more.
Hmm. I guess I would rather just use the [link|http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/sql-copy.html|COPY] command and be done with it... Does Interbase have polymorphism/inheritance for its tables? Not that I know of... What is *that*?
[link|http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/inherit.html|http://www.us.postgr...gres/inherit.html] Other interesting features of Postgres include the geometric types, user-defined types/functions/operators, and (one of the best things) the ability to use nearly any language you want to as a stored procedure (with a little extra work to integrate it first if it hasn't already been done).
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,466
11/22/02 9:37:28 AM
|
"user-defined types" - would that be SQL-standard 'domains'?
If so, then that (along with roles and views and Idunno what all) is among the many IB/FB features I neglected to mention. As opposed to UDFs, which IIRC I *did* mention... :-)
So what are you saying -- that Larry is collecting a percentage from Seagate, Maxtor, et al?
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
|
Post #64,467
11/22/02 9:40:59 AM
|
user-defined types
As in, write some external code with save/restore functions, etc., to make your very own new FarbleBanger base type.
[link|http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/xtypes.html|http://www.us.postgr...tgres/xtypes.html]
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,480
11/22/02 10:51:43 AM
|
Wow, cool; the FB dev list is only now talking about that...
...kind of thing. Or, OK, they've been mentioning it, on and off, for a bit over a year AFAICR. But it ain't there yet. (Also somewhat reminiscent of how to build TField-descendant object classes, for client-side apps that attach to different kind of DBMSes, in Delphi; those "save/restore functions" are analogous to the getter/setter methods for the AsString property.)
Cool to see that PgSql seems to have copied IB's basic "multigenerational" ("multiversion", in Postgres-speak) architecture, too. (Doesn't seem quite consistently pervasive, though.)
Yeah yeah, I'll lay off the bashing... If the freaking WORLD lays off the hyping! :-)
Christian R. Conrad Microsoft is a true reflection of Bill Gates' personality - the sleaziest, most unethical, ugliest little rat's ass the world has seen unto this time. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=42971|Andrew Grygus]
|
Post #64,481
11/22/02 11:04:29 AM
|
Postgres is really a research database
For trying out new things. People use it for the real world too, though. :-)
Some of the contrib items are pretty nifty as well, like the fulltext indexing I use here.
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,491
11/22/02 11:41:26 AM
|
That...is frickin cool.
I am way too much of an LCD designer to ever use it but I can dream, can't I?
Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
|
Post #64,494
11/22/02 11:46:22 AM
|
What, the inheritance?
Yeah, very.
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #64,495
11/22/02 11:47:06 AM
|
Cool!
APL as a stored procedure langauge - must investigate...
That is very hip, thanks for pointing it out (would never have guessed).
-drl
I'm so happy I could scalp someone. Mark Twain
|
Post #64,353
11/21/02 5:41:03 PM
|
I glanced at it, but didn't bite.
Don't ask me why... I'll keep it on the list. Thanks for the reminder!
Cheers, Scott.
|