Import 2 column CSV files to some sort of DB… preliminary planning

Question

Every day I have a CSV file generated by a script. It has two columns. Column 1 is a name, column 2 is the size of their mailbox.

I have a years worth of these files. I would like to be able to import them into a database (We have SQL in house, or I could install MySQL, or anything else for that matter)

I want to be able to see growth patterns for these users over time. Basic reports, which is another problem that I'll solve at a later time. For now I just want the data in a DB instead of hundreds of flat files.

What kind of DB is good for this? Simple is best. I'm not a DB guy. What would you do? This is mostly a learning project for me.

Answer 1

A while ago I wrote a couple of blog posts on loading logfiles into SQLite for analysis. This is an incredibly easy database to use, zero administration, bindings to any language you can think of (e.g. Python and Tcl/Tk as well as exotic ones like OCaml) supports a decent subset of SQL for complex reporting and can handle surprisingly large datasets (millions of rows). You don't say what OS you're on, but on Linux it's just a yum or an apt-get away. I'd definitely choose it over MySQL for this kind of work.

Answer 2

If you already have an SQL Server box in your place, then you would be able to import the files using SSIS packages. SSIS = SQL Server Integration Services, the SQL Server main tool for data load.

A nice starting tutorial for SSIS packages you can find on SQLServerCentral: Stairways on Integration Services and SSIS Automation. On MSDN you can find SSIS information here.

Another SSIS tutorial with hands-on lab and video you can find on MSDN - Introduction to Integration Services.

Answer 3

I use Pentaho Data Integration to load huge amount of text files like that.

If you are using MS Sql Server then you probably have access to SSIS. Using regular expressions, you will be able to load all the files from one folder like it was one very large file.