Fairly Confused.. Excel and Databases

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wetwilly92
Forum Newbie
Posts: 4
Joined: Wed Jan 21, 2009 10:06 pm

Fairly Confused.. Excel and Databases

Post by wetwilly92 »

I decided to learn php/mysql as a hobby last summer, and have been working on developing my ability to use the language. For the most part, I am still a newbie to the language (well, both languages). Recently, I was offered to work on a new project that is confusing me quite thoroughly. I have searched for an answer, to no avail.

The project includes a company that has been sorting data into excel and sort of using that excel file as a database (as I understand is quite common). I'm not used to seeing excel used to this extent...

However, This data is sorted into may columns, and innumerable rows that have been highlighted with various colors. It's really quite the creative piece of art. In the beginning, I assume this was most convenient for the company, but of late they have found this to become overly tedious to edit and have decided to move this onto a CVS server using software called http://www.icdevgroup.org/i/dev/index?i ... U&mv_pc=81. However, they have resorted to inputting each set of data into interchange. Where it is somehow magically displayed on their website.

This is all very confusing to me, and I'm not even sure if I have the order of operations correct, but I believe it goes from:

A text file -> Excel -> Interchange -> Web

I've seen nothing about a mysql database.

They did, however, hire a programmer to write a program that looks like the most complicated crap I have ever seen. Its more than likely not, but I can really understand only about 30% of it.

Would somebody be as kind to point me to a starting point?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Fairly Confused.. Excel and Databases

Post by Burrito »

wetwilly92 wrote:Would somebody be as kind to point me to a starting point?
to what? I didn't see any questions in that post.... 8O
wetwilly92
Forum Newbie
Posts: 4
Joined: Wed Jan 21, 2009 10:06 pm

Re: Fairly Confused.. Excel and Databases

Post by wetwilly92 »

Burrito wrote:
wetwilly92 wrote:Would somebody be as kind to point me to a starting point?
to what? I didn't see any questions in that post.... 8O
Good Point,

Okay, I suppose that I should first ask, Why are these people using Excel and how does this method work (in an ideal example).
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Fairly Confused.. Excel and Databases

Post by Burrito »

I can only speculate as to why they're using excel but I think my speculation will be pretty close to spot-on.

Most people in an organization will utilize the tools with which they are most comfortable. Most people have a basic understanding of excel because it's a pretty easy application to use. Data sharing starts out easy using excel. John decides to add some numbers for the company's ROI for a marketing endeavor and logs it to the spreadsheet then shares with his colleagues. Julie then updates it with new numbers for the next endeavor etc etc. This is easy to do but 9 times out of 10 becomes a trap that people fall into as they become reliant on the sheet and as the sheet grows in size, it takes longer to open, more people need to use it simultaneously, etc etc.

This is where the limitations of a spreadsheet for data-sharing become apparent. It's not scalable (AT ALL). The problem is pushing people away from the idea as it fits like an old shoe. Really, in order to get people to take hold of a central database for data-sharing you need to do one of two things. Build something that is as easy to use as the spreadsheet that users will put stock in right away, or force them out of the spreadsheet with a clean cut into something that 'just works'. Obviously scenario one is much more preferable. This is why larger organizations will pay 'project managers' tremendous amounts of money to develop a design that can be put forth into action that will ease the pain of moving from an old standard to a new with as little friction as possible.

I don't know how smooth the transition is going for you with the new implementation of Interchange...but my guess is that it's not going exactly how you anticipated it would (thus your post here). I've always been a firm believer of significant planning for major projects like this (90% planning 10% action) as when the action part hits, if you have a solid plan in place, it will go a million times smoother.

Really, in your case, I would have researched several different options for a data-sharing implementation. Run some test scenarios with real users to determine ease of use, reliability, scalability etc before making the leap into a new software. Perhaps you did and decided that Interchange was the best fit for your situation. In the long run, you're much better off using a central database for data-sharing than using an excel spreadsheet...even if that means a lot of pain initially.
wetwilly92
Forum Newbie
Posts: 4
Joined: Wed Jan 21, 2009 10:06 pm

Re: Fairly Confused.. Excel and Databases

Post by wetwilly92 »

Interchange was the company's idea. I would have initially gone with a mysql database because that is what I am comfortable with.

Either way, we're getting somewhere, the next step is in understanding this "Interchange" software. From what I took, it is used to mimic a mysql database, and their website is pulling information from the interchange client, similar to how I would pull information from a mysql database. Would I be correct in this assumption, or is there a more complicated process going on in background?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Fairly Confused.. Excel and Databases

Post by Burrito »

wetwilly92 wrote:Would I be correct in this assumption, or is there a more complicated process going on in background?
no idea...I've never heard of it.
wetwilly92
Forum Newbie
Posts: 4
Joined: Wed Jan 21, 2009 10:06 pm

Re: Fairly Confused.. Excel and Databases

Post by wetwilly92 »

Okay,

So I guess the better question (or questions, rather) to ask is what is that standard for managing many pieces of information such as this? Is there an article about the standard? Is it possible to quickly and efficiently extract that data from excel and make it standards-compliant?
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Fairly Confused.. Excel and Databases

Post by Mark Baker »

wetwilly92 wrote:So I guess the better question (or questions, rather) to ask is what is that standard for managing many pieces of information such as this? Is there an article about the standard? Is it possible to quickly and efficiently extract that data from excel and make it standards-compliant?
Can't comment on the standard, or what is standards-compliant.

But it's fairly straightforward to read data from an Excel workbook using PHP and libraries such as PHPExcel, and then the data can be manipulated in PHP/stored in a database/written out as XML/or whatever
Post Reply