READ CSV FILE AND INSERT INTO MYSQL PHP HOW TO
READ CSV FILE AND INSERT INTO MYSQL PHP CODE

Step 3: Create HTML Form To Upload CSV FileĬreate a simple HTML upload csv file form and add the following code into index. Step 2: Create a Database Connection FileĬreate a file name db.php and execute the below code into your file. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active', `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, The structure of this table is shown below.`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, So the table will have these new values.Īs an example, I created a table named engineeringfirm.

Omitting this parameter (or setting it to 0) the line length is not limited, which is slightly slower. Must be greater than the longest line (in characters) in the CSV file. If this statement executes successfully, then the data (rows and columns) in the CSV file will be uploaded to the table. Specifies the open file to return and parse a line from. If you are not using commas in any of the columns of the table, then a comma is seen as the default value, so to speak. Need to use another character as the delimiter.

The reason you may use another character besides the comma is if you are using commas inside of one of the fields. Using the character you chose to be the delimiter. You would then specify, 'FIELDS TERMINATED BY 'character', You could also use others symbols, such as '|' or any other character. You don't have to use a comma as the delimiter. This loads the CSV-formatted text file, data.txt' into the table named table_name, where the columns of the data are separated by a comma. This $load variable is set equal to a mysql_query()įunction that has the parameter 'LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE table_name FIELDS TERMINATED BY ',''. So the following code gets the data required to make a connection to a database and actually selects a database.Īfter we do this, we create a variable named $load. The PHP code to load the data from a CSV file into a MySQL table is shown below.

READ CSV FILE AND INSERT INTO MYSQL PHP HOW TO

Now we will show how to tie this in with PHP. So the above PHP code loads the file data.txt into the table named table_name where the columns of the table are LOAD DATA LOCAL INFILE 'data.text' INTO TABLE table_name FIELDS TERMINATED BY ',' The general format of the MySQL query to load data from a CSV file to a MySQL table is shown below. MySQL & PHP, in the same way, can read plain text files that have been formatted in CSV format. csv and then specify this file to be loaded into So if you created an Excel spreadsheet with certain data and you want that data to be inserted into a MySQL database table, you simply save it with a. PHP and MySQL can read these files just as it can read a plain text file with data organized in rows and columns. csv to indicate an Excel spreadsheet that is used as a CSV. In the commercial world of CSVs, certain companies such as Microsoft have created a special file extension with Or it can just be a plain text file, with data organized in rows and columns in that text file. It can be one created by such a company as Microsoft with a special. This CSV-formatted file can then be used to transfer data to tables of databases. It is a file formatted so that the data inside of it It isĪ common format used to transfer information between various databases. This type of file must be a comma-separated values (CSV) file, also called a comma-delimited file. The file must be specially formatted for database tables. You can't just load any file into a MySQL table. In this article, we show how to load a CSV file into a MySQL table using PHP. How to Load a CSV File into a MySQL Table Using PHP

Read csv file and insert into mysql php