I have access to the Met Office weather database. This sounds impressive but is really just a bunch of text files. Large text files mind, and this is the problem. Once I have merged each years files (The DOS Copy command is just brilliant, had forgotten about it) I end up with a file that can be approaching 10 GB. This is too large for Access or Excel to handle.
I was wondering if one of the flavours of SQL could handle it. And if it can how easy is it to use.
All I really need to do is some conditional searching on location and time and maybe a bit of stats work on them, though Excel does this nicely (and charting).
Anyone an expert on SQL?

<blockquote><cite>Posted By: SteamyTea</cite>Anyone an expert on SQL?</blockquote>

If you're on a 64-bit platform you're good up to between 2 and 4TB for the database size.

You'll have to design a table structure for the data, but it should be fairly straightforward.

http://dev.mysql.com/doc/refman/5.6/en/table-size-limit.html

http://www.phpmyadmin.net/home_page/index.php is useful to admin a database and run queries.

Paul in Montreal.

I'd say go for MySQL - you might want to go the linux route with LAMP (Linux, Apache, MySQL, PHP) - as this is a very common combination for web-based work, though you probably don't need Apache if you're not hosting a website.

See http://www.mysqlperformanceblog.com/2011/12/08/which-linux-distribution-for-mysql-server/

Paul in Montreal

I haven't had to do it myself - all the MySQL databases I use are on hosted webservers so there was nothing much to do.

However, take a look at these:

http://www.elated.com/articles/mysql-for-absolute-beginners/

http://www.dreamincode.net/forums/topic/42301-beginners-guide-to-creating-simple-mysql-tables/

Paul in Montreal.

Most of the MySQL admin tools are able to import directly from csv files.

You will probably want to play around with small files first rather than 10GB in one go.

I know next to nothing about relational databases (probably biggest single gap in my computing knowledge) but, yep, from what I understand MySQL is the obvious choice. The only plausible case for something else I can think of is that PostgreSQL has plug-ins which deal with geographical positions - e.g., find all records with lat/longs within 50 km of this point. If that's relevant it might be worth a look.

A while ago I downloaded the full precipitation record for the UK going back to 1850, in txt file. I couldn't figure out how to get all the data into usable database.

Maybe you could try ST, would be interesting to see what it shows.

Depending on the data formats and such in the file, you may have to tell the database how to interpret/convert a few fields when you import the file, such as dates for example. If fields are not important, best to skip them as it will help keep the table size and your query times down.

To enable queries to run in a reasonable time frame you may need to create some indexes, which act like an index in the back of a book does for a human. For example, an index on the "date" field sounds like it would be a good candidate given that you mention doing queries by date. I'd try a few queries once up and running and see.

More techy alternatives to MySQL would be PostgreSQL as mentioned (lightweight, more featureful), or IBM DB2 Express-C (not "Express", lots of documentation, tools) which is available free for personal use. SQL query syntax is very similar between all of the products until you start getting into esoteric stuff.

Posted By: Andrew_Doransuch as dates for example

Yes, I found this out, the date comes as YYYY-MM-DD hh:mm but Excel prefers (and I) DD/MM/YYYY hh:mm. Though I am not sure how to get around dates before 1900 when using Excel.
I don't mind using Access, just the file size is the problem.
Good tip about skipping data, had not thought of that one.

SQL express is worth a look, free with a 10GB data limit