Friday, April 30, 2010

Project Update - The Re-Learning speed bumps

It amazes me how you can stop doing something for a while ( like the web design side of the house ) and you can really forget how things work.  I have been working on getting a navigation menu working for the project at work and it took me a good couple of days to figure nut how to get CSS horizontal drop dad written wasn't.  own menus working.  I was scratching my head till I decided to take a step back, do some reading and then examine what I had done thus far.

I had to start by taking an example menu and get its code working.  Then, modified it for the options I needed and voila, working menu.  Then, I had to plug it in to the site, where it needed to be and make sure that it was where the other menu was.  That was a bit easier.

Once the menu was working, I compared it to my original code and I then saw the structure and quickly figured out what I was missing and why the code I had written wasn't working at all.  I just sat there, shaking my head in wonder at what I had forgotten and what I was slowly remembering.

So, onward I push.  The next step is to get the search page for the client database, designed, and setup so that I can then code the validation for the entered data and the processing to pull data from the database.  I think the only part at this point that I need to figure out is, if I pull information from the database and there are more than one clients returned, how can I have a list presented and then display the page for the chosen client.  This is all a new realm to me, so I am venturing into it with an open mind and a willingness to learn.

Sunday, April 18, 2010

Project Update

Well, I have been working on the project actively for a few days now and unfortunately, I am not as far as I would have loved to have been.   My experience working with Perl DBI is not extensive, and is limited to the job that I got laid off from last year.  What makes that experience a bit lessened is the fact that the DBI code was already written and all I had to do was extend what was already existing.

So, I have these spreadsheets, one in particular which is rather large, containing almost 2000 lines.  Each line is the information that we have for a customer/client.   I created a database, created a table in the database, and had written some code to parse out the data from its csv file format (which I exported from the Excel format it was originally in.

The problem that I ran into was that the prepare() statement kept throwing an error.  So, I posted my question up on to the in my Perl forum in hopes of getting some help, and I got it.  One of the experts in the forum was able to throw a couple of ideas my way.  RonB was able to throw a couple of ideas my way to get the data initially loaded.

I took a look at what he provided for ideas and decided to do the initial load via the MySQL interface.  Doing the load the following way, I specified the file to use to get the data from, the field delimiter (which is a : when I exported the Excel data to csv) and the fields that I needed populated, in the order that the data was presented.   I needed to specify the fields because the 1st column of data is a unique ID that is auto incremented.  Here is the command that I used:

mysql> LOAD DATA INFILE  "/path/to/file.csv" INTO TABLE client_info FIELDS TERMINATED BY ':' (PartnerName,CorporationName,ContactGivenName,ContactPhone,ContactEmailID,CommunicationProtocol,SecurityType,ECID,Source,GEID,GEMatchType,CrossoverWithGTI,ECIDv1,CustomerName,UltimateECID,UltimateName,CostCenter,DomicileRegion,SalesRegion,SalesHead,SalesManager,TMO1SID,TMO1Name,TMO2SID,TMO2Name,BankerSID,BankerName,CSOMgrSID,CSOMgrName,CSOSID,CSOName,CSPSID,CSPName,LOB,SubLOB,Segment,Market,Industry,ClientType,ClientStatus);

This actually seemed to work quite well.  See, the script I was working with had a big issue with special characters.  Some names contained an apostrophe, names were entered into the original spreadsheet in the format of  "last, first middle", which caused an issue when I originally used a comma as the delimeter.  The LOAD DATA command in mysql is quite useful, I must say and offers other options for specifying the format of the data.  I recommend checking it out.

Now, I have to worry about the interface that we are going to use for accessing the data.  I plan on designing it first, so that it is built and operational, then I will worry about things like a login interface and sessions.

I also still need to work on a Perl script to load data from a spreadsheet in the case of updates.  Our client base is about to grow astronomically over this year and this needs to be in place soon.  The update script will need to compare against the db for duplicates and only load the new entries.  So, that should be interesting.

I sooooooo have a lot of work ahead of me, but must say that its nice to have a project to work on that I know is going to be actively used by others.

Monday, April 12, 2010

Catching up.... and a link

As any code monkey and/or geek can tell you, work can certainly cause you to not have time for much else.  Having gone back to the company I was at almost 3 years ago (which I love working for, but had to leave to realize that), I find that I am incredibly busy, but its a good thing.

When I was here before, I was doing client implementations work.   I was one of the two original members of the team who ended up staying around the longest and became the expert on the system and the solutions we provided.  When I came back this past October (after 7 1/2 months unemployed), I came back on the other side of the wall and am now doing Production support for the platform I was implementing on before.  

Don't get me wrong, things have changed a fair amount (while some things remained the same or had gotten worse).  To say that I really like my new position would be a complete understatement.... I LOVE IT!!!  It is always amazing to be able to walk into a job and already know the platform (for the most part).  I just had to learn the intricacies of how my new group works.  

Recently, after having a talk with my boss, it was determined that we needed a database of our own with a web interface to it so that we can all access it, and our boss can run his metrics. Far too often do we have to look in a plethora of different databases and sites to try and find all the information that we need. Then, even when we do find it, it is not always easy to get that information in one general place where it can be referenced again later.  This project will change that.  It will be one place where we can put all the information we gather and have, so that we can reference it and even update it as needed.  

That said, the project that I mentioned back in my last post has been modified to be this project.  So far, I have gotten a second machine out of it (nice quad processing machine with 4 gig ram and a third flat screen monitor for my desk.  (the first two are attached to my dock for my laptop).  I have the machine installed with Ubuntu and configured with Amache2, MySQL, Subversion, and Bugzilla.  

The initial data that will be populated into the database table(s) is coming from a couple of spreadsheets that I was sent.  I am right now writing some Perl code to not only parse the Excel spreadsheets (that wasn't too aweful to do, especially using the Spreadsheet::ParseExcel module), but also populate it into the table(s).   I am being careful about this code because I want to be able to use it to produce a script for periodic updates.  It will need to check for data already existing so as not to duplicate entries.

Ok, the link I promised.  I stumbled across this earlier tonight.  Its an interesting page where you can look up functions for PHP and JavaScript.  It is quite the language reference.  It even has a reference for CSS and MySQL.  So far, I am really glad to have found it.
Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.