[cosmie]

It's really not too hard. What type of SQL database are you wanting to import into? MySQL, MSSQL, etc?

The easiest way is to export/save the Excel file in a CSV format, which is a plaintext, comma-delimited file. Most database engines have fairly simple methods for importing a CSV file (MySQL instructions, MSSQL instructions).

[gushort]

Yes I figured I'd have to export to CSV, then import into a "flat" SQL database (MySQL/PostgreSQL) initially. The less straight forward part is transforming that "flat" database into a relational one.

The final structure/schema I'm looking to have is one db with basic business and contact info, another linked db with associated classification codes, another with associated tags/keywords, and final db with suppliers (which would just contain IDs linking back to the main db).

The classification codes and tags/keywords each have their own Excel column (one for class codes, a second for tags), with the codes/tags separated by commas. I'm not sure how these will end up in the exported CSV file. Perhaps I should just run a replace on those columns that changes all commas into periods, That way I can then parse those fields into multiple records in their associated db.

I suppose it is the breaking out of the data into multiple dbs, and the parsing of fields containing multiple values, that I'm more unsure about ...

[TheEmpty]

There are a lot of Ruby libraries to handle Excel then use ActiveRecord to drive your DB We use Excel + ActiveRecord + PostgreSQL for some reports at work.

[gushort]

ThatRailsGuy, on 22 February 2012 - 04:26 PM, said:

There are a lot of Ruby libraries to handle Excel then use ActiveRecord to drive your DB We use Excel + ActiveRecord + PostgreSQL for some reports at work.

So you're suggesting I use Ruby, with one of the available Excel handling libraries, to create a one-time app to do what I require?

I'm not interested in having an Excel aware app down the road, as this will be a one-time only conversion .... then again ... on second thought it probably would be a good idea to have the capability to generate reports in Excel format built-in, since it is one of the most common formats

Ok, I guess I should have a look at some of those libraries ... thanks ... I think

EDIT: It looks like Spreadsheet would be a good choice.

[TheEmpty]

Yeah, generally a script will be the best idea I know you <3 Ruby as much as I do. The thing is that if you're only doing it once you don't need to write a test suite or anything (unless it's buisness cirtical).

[Daniel15]

What database system are you using?

Depending on how simple the post-processing is, one approach is to import the data from a CSV into a temporary database table, and then write some database scripts to insert the data into the "real" database tables. With Microsoft SQL Server I'd probably write this as a T-SQL stored procedure, or a CLR stored procedure or C# app if there's more complex logic involved. You can do quite a lot of powerful stuff in stored procedures. No need to write a Ruby app when you can do it all in the database itself - Doing data processing in the database is going to be a lot faster.

On Postgres I'd also probably try doing it in stored procedures. MySQL's stored procedure support is very basic and probably not ideal for something like this.

[gushort]

Just an update. I ended up using Navicat Premium. The data was in a dozen XLSX (latest Excel data file type) files of around 25MB in size each. Since each file contained more than 65k rows, I had to save the files in CSV format to prepare for the import. Using Navicat, I easily mapped the CSV data to the corresponding fields in a new MySQL table I setup on my web host, and then imported the all the data in less than an hour.

I now have a huge MySQL table and will be setting up some indices on it, then running a couple of queries to parse out a couple of the fields into new linked tables to create a proper relational model. I'll then have a nice base to create some apps for