How to Convert MS Excel to MySQL With PHP
Microsoft Excel is a computer program used to create and maintain spreadsheets. These spreadsheets take the format of a table, like multiplication tables used in school. MySQL is a tool used to manage databases on servers (computers) that contain tables, similar to those used in Excel. While the organization of information might be similar, the formats are different; Excel saves spreadsheets in .csv format and MySQL saves them as .sq. Problems arise when you have a spreadsheet you would like to add to your database but don't know how.
Other People Are Reading
Instructions
-
-
1
Save your spreadsheet as a .csv file on your computer. When your spreadsheet is open in Excel, open the \"File\" menu and select \"Save As.\" Type in your desired file name. Make note of the location for future use.
-
2
Click on the \"Download file here\" link at Tips and Tricks (see References) to download the \"PHP script to import csv data into mysql.\" Your browser saves this .zip file to your computer.<br /><br />Open the file with a program such as WinZip. Follow prompts that Winzip gives to unzip the file. You will now see a file named \"simplecsvimport.php.\"
-
-
3
Open a text editor such as Notepad which is available on most Windows systems. Select \"File\" and \"Open,\" then navigate to the location of simplecsvimport.php.<br /><br />Locate the line toward the top of the file which reads \"Edit the entries below to reflect the appropriate values.\" You will see entries named \"$databasehost\", \"$databasename\", \"$databasetable\", \"$databaseusername\" and \"$databasepassword.\" Replace the sample values between quotation marks by typing your database host, name, table name, username and password into those fields, respectively. <br /><br />Keep the values for entries \"$fieldseparator\" and \"$lineseparator\" as they are to convert to default SQL format. <br /><br />Change the value of \"$csvfile\" to the file name of your .csv (spreadsheet). If your spreadsheet is named \"sample.csv\" this line will look like:<br /><br />$csvfile = \"sample.csv\"<br />GO<br /><br />Locate the value \"$save\", which has a default value of \"1.\" This informs the script to save the information from your spreadsheet to a file as well as the database. Type a value of \"0\" if you do not want the information saved as a file.<br /><br />Save simplecsvimport.php by clicking \"File\" and \"Save\" from your text editor menu.
-
4
If you have entered a value of \"1\" for the entry \"$save,\" create an empty file with your text editor by clicking \"File\" and \"New.\" Save it as output.sql.
-
5
Upload your .csv file, simplecsvimport.php and output.sql (if you want your information saved in the file). You can use the file manager in your website control panel or a file transfer protocol (FTP) client such as Filezilla.<br /><br />Locate the upload tool in your file manager. Select \"Browse\" and click on the file you wish to upload. Confirm the upload.<br /><br />Open the folder containing these files in Filezilla. Double click the file to upload to your server. <br /><br />The files should now be copied to your server.
-
6
Change the permissions (via FTP) of output.sql to \"777\" to allow the script access to write the file. Right click on the file name (in the server/remote view of Filezilla) and select \"File Permissions\" or \"CHMOD.\" Type \"777\" and confirm.
-
7
Run simplecsvimport.php by typing the location of the file (for example, http://yourdomain.com/simplecsvimport.php) into the address bar of your web browser. If the script connects to your database, the script displays a confirmation about the number of records found in the .csv file. The contents of the file will now be in your database table and (optionally) saved to output.sql.
-
8
Delete simplecsvimport.php from the server--via FTP (right click and select \"Delete\") or file manager (select the file and click the \"Delete\" option)--so that others cannot access it and see your database information. You can also delete your .csv file if you no longer need it.
-
1
Tips & Warnings
Ask your host or webmaster to complete this process, if you are not comfortable with MySQL, PHP or FTP.<br />If you do not know your database information, ask your webmaster or website host.<br />If the script cannot connect to your database, double check that your username, password and database name are entered correctly.<br />The table (\"databasetable\") must already exist in order for this script to work properly.
Do not keep unprotected files--like \"simplecsvimport.php\"--with sensitive information (such as database credentials) on your server longer than necessary. Others can access these files and view the information included, which then gives them access to your database.
