Moving from Ms excel to MySQL

excel to MySQL, parse excel,

[marisha]

I have a design question to ask

Background
----------
I am developing a DB application for one of the academic departments. The department gets reports from various recycling vendors in the form of excel sheets. These excel sheets are huge. For example one excel sheet contains 5 to 6 columns with 70 rows minimum.

What have I done
----------------
I have designed DB in MySQL from the fields in the excel sheets. The design is quite good.

My Problem
--------------
1) We are still going to get the reports from vendors in the form of excel sheets.
2) I have designed few Php forms in which the Vendors can enter some part of data but I want to know how can I get the data from those excel sheets. I intend to present the following idea to department heads.

IDEA
------
1.1) let the vendor populate some data though website and upload their data in excel sheet on the website. I will design those excel sheets that will reflect the way I am storing data in MySql Db.
1.2) Being the DB administrator I will import the data in excel sheet into the DB on the monthly basis.

I want to know the opinion of the experts on this. I dont want to parse the whole excel sheet in a Php code and enter the data.


Please tell me If my approach is correct

Thanks,
Marisha

[brotherhood of LAN]

I see phpMyAdmin allows the import of excel sheets, I'm not sure how they process them though.

My suggestion:
1. Ask your vendors to export to a CSV type format, this option is readily available in all spreadsheet apps like Microsoft Excel or Open Office Spreadsheet. It would take 10 seconds to do this.
2. Use LOAD DATA INFILE [dev.mysql.com] to import the data
3. If data is imported to more than one table, import the data in 2) to a temporary table and update your permanent tables accordingly.

You shouldn't have to use PHP to process the data at all, though validating column data may be required if you feel the need.

There is some information on that page about importing .xls files that may mean you could skip step 1.

[jmccormac]

If you are going to be importing Excel data into a spreadsheet, it would be a good thing to clean/sanitise it first to prevent any errors getting though. There are some PEAR extensions that can be used for importing and working with Excel spreadsheets in PHP. It would also be very important to make sure that all vendors are using the same spreadsheet template and that there is no deviation from the format. The CSV idea that Brotherhood of LAN mentioned above is a very good and effective idea as it reduces the data to a bare minimum.

Regards...jmcc

[marisha]

I am aware of this LOAD DATA INFILE
I am using Php MyAdmin as well

so the steps to be followed will be this
1) They will send the data to us in EXCEL or CSV format
2) I will be the one doing this data import into MySql DB

Actually the people I am going to give presentation are Managerial level I am the only IT person in the office. I have to convey the following points to them with proper reasoning

WHAT THEY THINK
-----------
1) The vendor will submit his excel sheet on website and the whole data will get imported in the MySql

My Point of View
----------------
1) It is not a good idea to do it through a website. If the data is incomplete or wrong I have to write the whole validation logic in Php which is a waste of effort

My Proposal
-----------
1) Let the vendors send us the Excel or CSV file in the format I want. The number of columns , naming convention etc.
2) As Db admin I will import this data in DB though SQL scripting or MyPhpAdmin(I have imported data through MyPhpAdmin)

WHAT THEY THINK
--------------
1) Why is it not possible to just submit excel on website and import the whole data though a button click
2) We are still dependent on excel sheets and what is the use of MySql

WHAT I have to Convince them
-------------------------------
1) It is not advisable to do this in a DB system.
2) This can lead to error prone data being imported
3) I have to code alot for validation which is a waste of effort and time
4) The benefit of MySQL is you are importing error free data. The data once stored in Db is going to help you get reports and error free reports.
5) I believe that all systems that have to maintain data from various sources have a staging area for us its excel and then we are importing it



I want to be sure my reasoning is correct before I go ahead and say this.... being a new developer I need strong reasons behind what I say

Thanks
Marisha

[johnblack]

Sorry this is such a short reply and probably not a 'best fit' for your solution, but have you thought about using Google Docs?

The vendor enters his data in an online spreadsheet and then your colleagues can view the data online? I know you can share s/sheets. Would probably mean each vendor would have to have their own google logon, and then a global logon for your colleagues. Anyway just a thought.

[deejay]

Marisha, can I give you my experience and point of view?

I run a website based database with a couple of hundred users entering technical data (some by keystroke entry, some by auto uploads) - this data is used in live individual and group analyses on the website that they can all access before I ever have a chance to correct their stuffups. It's bloody scary. But at the same time if that data were not immediately uploadable and accessible to them, I would not have a successful marketable product.

I've spent 10 years building offline databases with data submitted to me, entered by me, reports produced by me and then fed back to the clients. As powerful and perfect and valuable as those services were, they always limped along. They just couldn't produce the sense of ownership or immediacy that the online user-run system can.

The challenge then, for me, is to make the system safe.To push my own limits and knowledge to make it work the way it needs to work, rather than the way I currently know how to make it work. To produce a system that is explicit and prescriptive in the way clients set up their data, and yet simple enough that the computer-phobes among them can feel in control.

That's the challenge I accept. And it means that as the system grows and confidence in it grows and more people are coming to me and saying 'hey, can we....?', I'm learning something new just about every darn day 0 - even after 10+ years designing databases, even after 3 years or so writing mysql and php.

My Point of View
----------------
1) It is not a good idea to do it through a website. If the data is incomplete or wrong I have to write the whole validation logic in Php which is a waste of effort

If I may say so, that sounds like fear talking to me. It sounds like you aren't confident of being able to build it.

If, as I take it from your post, this is data from more than one source and this is an ongoing requirement, then it is absolutely worth the effort to build a system for this. There's no question it is more input up front, but if it's an ongoing situation it is absolutely worthwhile.

We'll help you. You get to do the work, but we can guide and help troubleshoot.

Brotherhood of LAN has hit the nail on the head with LOAD DATA INFILE. jmccormac is bang on with his comments too.

..... of course I may have completely missed the point, in which case apologies. :)