Sunday, July 24, 2011

Import Excel Data into MySQL in 5 Easy Stepss

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.
  1. Save your Excel data as a csv file (In Excel 2007 using Save As)
  2. Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
  3. Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
  4. Enter this command:
    LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);
    [Edit: Make sure to check your single quotes (') and double quotes (") if you copy and paste this code - it seems WordPress is changing them into some similar but different characters]
  5. Done!
Very quick and simple once you know it 
Some notes from my own import – may not apply to you if you run a different language version, MySQL version, Excel version etc…
  • TERMINATED BY – this is why I included step 2. I thought a csv would default to comma separated but at least in my case semicolon was the deafult
  • ENCLOSED BY – my data was not enclosed by anything so I left this as empty string ”
  • LINES TERMINATED BY – at first I tried with only ‘\n’ but had to add the ‘\r’ to get rid of a carriage return character being imported into the database
  • Also make sure that if you do not import into the primary key field/column that it has auto increment on, otherwhise only the first row will be imported
Link : 
http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html

Install IIS 7, PHP5 and MySQL 5.1 on Windows Server 2008

Adding Internet Information Server 7 Role

IIS 7 is now a server role in Windows 2008 Server as opposed to previous versions of Windows Server (2000 and 2003) where IIS was installed as a component.
To add the IIS7 server role, launch the Server ManagerStart => Programs => Administrative Tools => Server Manager
Right-click on Role from the left panel and select Add Roles from the contextual menu. From the Add Roles Wizard, check the Web Server (IIS 7) checkbox. Click on Next until the Roles Services screen.
Adding IIS 7 Role On Windows 2008 Server
Here are the options I’ve selected:
  • Web Server 
    • Common HTTP Features: Static Content, Default Document, Directory Browsing, HTTP Errors and HTTP Redirection.
    • Application Development: All options.
    • Health And Diagnostic: HTTP Logging.
    • Security: Request Filtering.
    • Performance: Static Content Compression.
  • Management Tools: IIS Management Console, IIS Management Scripts And Tools, Management Service
    • IIS 6 Managament Compatibility: IIS 6 Metabase Compatibility.
  • FTP Publishing Service: All options.
Click on Next again and then on Install. When the installation is completed, you can now access your new IIS 7 web server.
IIS 7 On Windows Server 2008
Stay tuned as I’ll post how to install PHP5 and MySQL Server 5.1 on Windows 2008.

Continue here :

Installing FastCGI & PHP5 on Windows Server 2008

Installing FastCGI And PHP on Windows Server 2008

As opposed to previous versions of Windows Server, the FastCGi module is already bundled with Windows Server 2008 so you don’t have to install it.
First of all, let’s make sure that the CGI role service is installed. Click on Start => Programs => Administrative Tools => Server Manager. Expand Roles, right-click on Web Server (IIS) and select Add Role Services. Make sure that CGI is checked and if not proceed with its installation.
iis7-cgi-role-service.jpg
Next head over to PHP.net’ download page and download the PHP Non-thread-safe Win32 binariesAt the time of this writing, the latest version of PHP is 5.2.5. Extract the content from the PHP archive into c:\php.
Launch the Internet Information Server Manager by clicking on Start => Programs => Administrative Tools => Internet Information Server Manager. Select your server from the left panel and then double-click on the Handler Mappings icon.
iis7-handler-mappings1.jpg
Click on Add Module Mapping from the right column:
iis-add-module-mapping.jpg
Fill-in the PHP module mapping information as illustrated below and click on OK when done:
iis-php-cgi-mapping.jpg
At this point PHP should now be working even though there no php.ini configuration file yet. To find out if PHP files are parsed correctly, create a new file at the root of website’s folder and name it phpinfo.php. Insert the following content into the file and save it:
Now try pointing your web browser to http://yourserver.com/phpinfo.php. If everything works properly you should see this page:
phpinfo.jpg

Configuring PHP

By default, IIS will look for PHP’s configuration file (php.ini) in c:\windows. To change this, launch the registry editor (Start => Run => regedit) and add a new key named PHP inHKEY_LOCAL_MACHINE\SOFTWARE\. Under the newly created key, create a new value named IniFilePath and set its value data to c:\php.
php-registry.jpg
Close the registry editor when you’re done. Using the Windows Explorer, renamec:\php\php.ini-recommended to c:\php\php.ini and open it with your favorite text editor (no word processor). Find the extension_dir parameter and set its value to “c:\php\ext”. You might also want to uncomment extension=php_mysqli.dll and / orextension=php_mssql.dll if you plan to use MySQL or MS-SQL.
Once you’re done editing PHP’s configuration file, save it and close your file editor. You will need to restart IIS in order to apply the changes. This can be done through the IIS Manager:
restart-iis.jpg
I’ll try to perform some benchmark test to see how this setup compares to using php5isapi.dll.

Continue here..