There’s some data at work that I’d like to have in a small database so I can do ad hoc and automated querying of it, since working with various Excel spreadsheets is tedious and cumbersome. Today I had some time without a lot of meetings so I decided to dip my toe back into the application developer world. I haven’t written code, or SQL statements in years, however, I remember most of the commands. My biggest issue was looking at the landscape of tools out there to figure out what to use.
Since I’m working on a Windows box I decided to make my first attempt at this using Powershell and SQL Server Express. I downloaded all the relevant tools and started to get to work. Powershell is an impressive scripting language with enough power to match what I needed. In relatively quick order I had a script set up that exported each Excel file to a CSV, ready for me to import into the database.
This is where the complications came in. SQL Express is a full featured database, and has it’s own data import wizards. I eventually want to automate this, but for the time being I just wanted to get the data in the system. When I fired off the importer it choked. I let it try to auto-create the table, and didn’t give itself enough space for each field. Then it started choking on text that was within quotes, ignoring the quotation marks and breaking up the lines with commas that were supposed to be ignored.
By this point I had to do some other work, but I came back to it later, and decided to try some Powershell modules. These modules ended up dying on me mid import, and never really worked well. So I went back to the SQL Express tools and dug into the configs a bit more. Eventually, I found more options for delimiters and after hours of work, got the first of the spreadsheets imported. Needless to say, this wasn’t the quick easy job I remember it being.
We’ll see if I keep pressing forward with this direction or if I abandon it all and just use MySQL and Perl or Python. It has been a lot of fun putzing in this world again. I always enjoyed using programming to solve problems.