Brief Outline: The company I work for is planning on migrating to a Database Storage and Networking solution.

Currently we are using Excel spreadsheets to store all of our data.

These spreadsheets are spread accross various other spreadsheets that make up our daily data intake...

The Problems: I am debating over what database would be best to use and what methods should be used to

impliment this project.

Break down:
So Far I have decided to use a typical Apache MySQL Php setup but installed on Win2k3/XP Pro. I have started to build a

powerful server with all the bells and whistles and the processor will be a 64bit Dual Core CPU. Plus a lot of RAM, etc

I will be installing and configering the server myself with the above mentioned software and creating a Secure web app using

PHP and MySQL for the frontend and a basic login system for the clients to login to the main app/database (backend)

I need some advice on ANY likely pitfalls that I will be likely to encounter along the way or any other surgestions

that you may have.

Is this the correct way to go about this Project or is there a better way to do this.

Many Thanks in Advance

Simon

PS
Can I import CVS files that were exported from Excel into MySQL automatically ? Or better still can MySQL read data from an

Excel Spreadsheed (.xls) If not would MSSQL be able to do it ???

You can write code to extract data from an Excel spreadsheet and transport it to a MySQL database (Perl is easy, PHP isn't bad, other languages may be more to your taste). The only database engine that I know of that has any chance of doing this "out of the box" is Microsoft SQL Server... SQL-2000 has DTS (with wizards for importing from Excel), and SQL-2005 has IS with similar capabilities.

-PatP

for CSV files into mysql it's very easy:
LOAD DATA (local) INFILE /path/to/data INTO TABLE your_table IGNORE x LINES

You'll run into some catches with some things. Since EXCEL doesn't do a good job of letting you customize your save options you are best off opening and looking at a sample file with a text editor then getting rid of quotes that you don't need.

If you have dates then you'll have another problem because mysql stores dates in standard SQL format of 'yyyy-mm-dd' but doesn't have functions to convert to other formats and excel stores dates as serial dates (so January 1, 1900 is stored as 00001 in excel). You can either convert your dates in excel to text fields or you can save them as serial dates in mysql then add a column that is equal to ADDDATE(1990-01-01 INTERVAL serial_date_column DAY) unless you have dates which are between Jan 1, 1900 and March 1, 1990 (spreadsheet bug from Lotus 123 which claimed there was a leap year in 1900 which there wasn't). I'm actually quite tired and can't do math right now. the formula might be ADDDATE(1990-01-01 INTERVAL serial_date_column+2 OR -2). Actually I'm pretty sure it's -2.

you can also use 'mysql_set_import_default' to set a default path to files to save some keystrokes.

Anyway, if you want to import directly you would need to use a 3rd party tool.

You may want to look at phpMyAdmin for mysql DB management tool. I'm pretty sure it's available on windows.

if cash is tight, and I'm guessing it probably is you are choosing Apache / MySQL,then don't write off MSDE / SQL Server workstation (or whatever its called these days)

I'd also consider using Access as a front End to what ever back end datastore you use, ypou may have to buy a copy of Access runtime or perhaps not if your PC's have Access already installed - that advantages it means your users are staying within the microsoft stable so there shold be less of a familiarisation curve, some of the everyday features are ready tright out of the box (searches etc), reporting is a breeze (usually), its also high on the comfort factor for non IT managers

All of your surgestions have been a great help on my journey through this project. I am glad to see that there is such an active community here and not just active for the sake of replying to question asked, but real answers that have been given some thought and are of substance. I have taken onboard all of your replys and am on the way to finishing off my DataBase Application. For all of you guy's (and Girls) who want to know what I decided to use in the end for my project I will give a brief out line of it next:
Hardware & Software
- For the Database I stuck with MySQL v5.03,
- Webserver Apache .v2.2
- OS WinXP 64bit Edition - Had a problem with (Lack of) Driver Suport for my WiFi LAN USB Stick by Netgear model, WT111G
- The language used for the dynamic elements was PHP with a sprinkle of JavaScript over the edges.
- For Testing am using XAMPP as a local testing server (No for final Production)

Outline of Application
I decided to have a frontend and a backend (pretty obvious) The frontend will be available to the World but will need a login to be able to progress though to the backend. Upon reaching the backend of the app you are greated with three different options for the three distincly different Databases one for Insurance one for accessories and one for orderd Application Data.

Three different databases yet are still interconnected and intersearchable. I have also had to include realtime data from other sources that needed to be included in my application as form values so that employees could select this whilst process the orders, etc.

In a Nutshell
This project so far has has proven to be quite an undertaking. I started working on it on the 23rd May 2006 and am now entering into the testing phase of the whole project I will then be starting to add the content for the frontend and the help system for the backend (The authorized section for emplyee's)

Last Words If there is anything else that you guys (and Girls) could help me with before I finish the project off? I still need to import all to the Excel data from the worksheets that have been used by the company for ages into my DBase (MySQL) 50000 Records plus on avarage 250 customers per day. This is the biggest headache I have got. What would be the best format to use for exporting from excel and to import to MySQL ?

CSV for the excel. If you don't have any comma's in your excel sheets then you can pretty safely open the file in notepad and do search/replace to remove the quotes. Or you can just import them then remove them.

You can also try "navicat" which seems to feel it's a good program. Never used it myself. Or you can learn how to parse files with PhP. That's what I do when I get reports on .pdf files.