Php How to Check if Uploaded File Is Csv or Excel

Import and Export feature is very useful for the information management section. The Import functionality allows the user to upload and insert multiple information in the database. Using the Import feature, the bulk data can exist inserted in the database on a single click. The export functionality allows the user to download the table data list and relieve in a file for offline use. Using the Export feature, multiple records can exist downloaded in a file format.

Generally, the CSV file format is used to import and export data in the web application. CSV (comma-separated values) file stores the information in plain text format and helps to motility data between programs. The import and export functionality can be easily implemented with a CSV file using PHP and MySQL. Import CSV file data in database / Export data to CSV file both can exist integrated with PHP and MySQL. In this tutorial, we will show you how to import and export CSV file data in database using PHP and MySQL.

In the example import and export script, the following functionality volition be implemented.

• Fetch the fellow member'south information from the database and listed in the webpage.
• Import CSV file data into MySQL database using PHP.
• Export data to CSV using PHP and MySQL.

Create Database Table

To store the member's data, a tabular array needs to be created in the database. The following SQL creates a members tabular array with some bones fields in the MySQL database.

          CREATE          Tabular array          `members`          (          `id`          int(xi)          NOT          NULL          AUTO_INCREMENT,          `name`          varchar(50)          COLLATE          utf8_unicode_ci          Non          NULL,          `email`          varchar(fifty)          COLLATE          utf8_unicode_ci          NOT          Naught,          `phone`          varchar(15)          COLLATE          utf8_unicode_ci          NOT          Zilch,          `created`          datetime          NOT          NULL,          `modified`          datetime          Not          NULL,          `condition`          enum('Agile','Inactive')          COLLATE          utf8_unicode_ci          Non          Nada          DEFAULT          'Active',  Primary          Central          (`id`) )          ENGINE=InnoDB          DEFAULT          CHARSET=utf8          COLLATE=utf8_unicode_ci;

CSV File Format

Based on the database table structure, the CSV file should have these fields – Name, Email, Phone, and Condition. To import CSV file data in the database, the CSV file format will similar to the following screen.

When the data export to CSV file, the downloaded format will look like the following screen.

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and proper name ($dbName) every bit per your MySQL database credentials.

          <?php                    // Database configuration                    $dbHost          =          "localhost"          ;                    $dbUsername          =          "root"          ;                    $dbPassword          =          "root"          ;                    $dbName          =          "codexworld"          ;                    // Create database connexion                    $db          = new          mysqli          (          $dbHost          ,          $dbUsername          ,          $dbPassword          ,          $dbName          );                    // Check connexion                    if (          $db          ->          connect_error          ) {     dice(          "Connection failed: "          .          $db          ->          connect_error          ); }        

CSV File Upload and Download (alphabetize.php)

Initially, the member's data is listed in the HTML table with import and export option.

• Existing members data are fetched from the database and listed in a tabular format.
• An Import push is placed at the top of the list.
  • By clicking the Import button, a CSV file upload form appears.
  • On submission, the form is submitted to the importData.php file for importing the CSV file data to the database.
  • formToggle() – This JavaScript function is used to Bear witness/Hide the CSV upload form and it triggered when the Import button is clicked.
• If the CSV file import request already submitted, the status bulletin is retrieved from the URL and the import status is displayed on the web folio.
• An Export button is placed at the height of the list.
  • The Export link navigates to the exportData.php file for exporting tabular array data to CSV file.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;                    // Become status bulletin                    if(!empty(          $_GET          [          'status'          ])){     switch(          $_GET          [          'status'          ]){         instance          'succ'          :          $statusType          =          'alarm-success'          ;          $statusMsg          =          'Members data has been imported successfully.'          ;             break;         case          'err'          :          $statusType          =          'alert-danger'          ;          $statusMsg          =          'Some problem occurred, please try again.'          ;             break;         instance          'invalid_file'          :          $statusType          =          'alarm-danger'          ;          $statusMsg          =          'Please upload a valid CSV file.'          ;             pause;         default:          $statusType          =          ''          ;          $statusMsg          =          ''          ;     } }                    ?>                      <?php            if(!empty(            $statusMsg            )){            ?>            <div              class="col-xs-12">            <div              class="warning                <?php                echo                $statusType                ;                ?>">            <?php            echo            $statusMsg            ;            ?>            </div>            </div>            <?php            }            ?>            <div              class="row">                        <div              form="col-md-12 caput">            <div              grade="float-right">            <a              href="javascript:void(0);"              class="btn btn-success"              onclick="formToggle('importFrm');">            <i              class="plus">            </i>            Import</a>            <a              href="exportData.php"              form="btn btn-primary">            <i              course="exp">            </i>            Consign</a>            </div>            </div>                        <div              class="col-md-12"              id="importFrm"              style="display: none;">            <course              action="importData.php"              method="post"              enctype="multipart/grade-data">            <input              type="file"              name="file"              />            <input              type="submit"              form="btn btn-main"              proper name="importSubmit"              value="IMPORT">            </class>            </div>                        <table              class="table table-striped table-bordered">            <thead              form="thead-night">            <tr>            <thursday>#ID</th>            <th>Proper noun</th>            <th>E-mail</th>            <th>Phone</th>            <th>Condition</th>            </tr>            </thead>            <tbody><?php            // Become member rows            $event            =            $db            ->            query            (            "SELECT * FROM members ORDER BY id DESC"            );         if(            $effect            ->            num_rows            >            0            ){             while(            $row            =            $upshot            ->            fetch_assoc            ()){            ?>            <tr>            <td>            <?php            echo            $row            [            'id'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'name'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'email'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'phone'            ];            ?>            </td>            <td>            <?php            echo            $row            [            'status'            ];            ?>            </td>            </tr>            <?php            } }else{            ?>            <tr>            <td              colspan="5">No fellow member(s) found...</td>            </tr>            <?php            }            ?>            </tbody>            </table>            </div>                    <!-- Show/hide CSV upload form -->          <script>                                    function              formToggle(ID){            var            element =            document.getElementById(ID);            if(element.style.display ===            "none"){         element.way.brandish =            "block";     }else{         chemical element.fashion.display =            "none";     } }          </script>        

This example code uses Bootstrap 4 library to styling the HTML Tabular array, Class, and Links. You can omit it to include if you don't desire to use Bootstrap construction. Otherwise, include the Bootstrap library file and custom stylesheet file (if whatever).

            <link            rel="stylesheet"            href="assets/bootstrap/bootstrap.min.css">                    <link            rel="stylesheet"            href="avails/css/style.css">        

Import CSV Data to Database (importData.php)

The importData.php file handles the CSV file upload and data import procedure with PHP and MySQL.

• Validate the submitted file whether a valid CSV file.
• Check the CSV file upload status using PHP is_uploaded_file() part.
• Open the CSV file using PHP fopen() role.
• Parse data from the CSV file using PHP fgetcsv() function.
• Insert or Update information into the database based on the member'due south email.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;  if(isset(          $_POST          [          'importSubmit'          ])){          // Allowed mime types          $csvMimes          = assortment(          'text/x-comma-separated-values'          ,          'text/comma-separated-values'          ,          'application/octet-stream'          ,          'awarding/vnd.ms-excel'          ,          'awarding/10-csv'          ,          'text/x-csv'          ,          'text/csv'          ,          'application/csv'          ,          'application/excel'          ,          'awarding/vnd.msexcel'          ,          'text/plain'          );          // Validate whether selected file is a CSV file          if(!empty(          $_FILES          [          'file'          ][          'name'          ]) &&          in_array          (          $_FILES          [          'file'          ][          'type'          ],          $csvMimes          )){          // If the file is uploaded          if(          is_uploaded_file          (          $_FILES          [          'file'          ][          'tmp_name'          ])){          // Open uploaded CSV file with read-only mode          $csvFile          =          fopen          (          $_FILES          [          'file'          ][          'tmp_name'          ],          'r'          );          // Skip the start line          fgetcsv          (          $csvFile          );          // Parse data from CSV file line by line          while((          $line          =          fgetcsv          (          $csvFile          )) !==          FALSE          ){          // Get row information          $proper name          =          $line          [          0          ];          $email          =          $line          [          1          ];          $phone          =          $line          [          2          ];          $status          =          $line          [          3          ];          // Check whether fellow member already exists in the database with the same e-mail          $prevQuery          =          "SELECT id FROM members WHERE email = '"          .          $line          [          ane          ].          "'"          ;          $prevResult          =          $db          ->          query          (          $prevQuery          );                                  if(          $prevResult          ->          num_rows          >          0          ){          // Update fellow member data in the database          $db          ->          query          (          "UPDATE members Fix proper noun = '"          .          $name          .          "', phone = '"          .          $phone          .          "', status = '"          .          $condition          .          "', modified = NOW() WHERE email = '"          .          $e-mail          .          "'"          );                 }else{          // Insert member data in the database          $db          ->          query          (          "INSERT INTO members (proper name, e-mail, phone, created, modified, status) VALUES ('"          .          $name          .          "', '"          .          $electronic mail          .          "', '"          .          $phone          .          "', Now(), At present(), '"          .          $status          .          "')"          );                 }             }          // Close opened CSV file          fclose          (          $csvFile          );          $qstring          =          '?status=succ'          ;         }else{          $qstring          =          '?status=err'          ;         }     }else{          $qstring          =          '?status=invalid_file'          ;     } }                    // Redirect to the listing page                    header          (          "Location: index.php"          .          $qstring          );        

Export Information to CSV (exportData.php)

The exportData.php file handles the data export process using PHP and MySQL.

• Fetch the records from the database.
• Create and open up a file with writing-only manner using PHP fopen() role.
• Gear up header columns, format as CSV and write it to the opened file using PHP fputcsv() function.
• Output information from the database, format as CSV and write it to file.
• Strength browser to download information as CSV format in a file.

          <?php                    // Load the database configuration file                    include_once          'dbConfig.php'          ;                    $filename          =          "members_"          .          date          (          'Y-m-d'          ) .          ".csv"          ;                    $delimiter          =          ","          ;                    // Create a file pointer                    $f          =          fopen          (          'php://memory'          ,          'w'          );                    // Prepare column headers                    $fields          = array(          'ID'          ,          'Name'          ,          'E-mail'          ,          'Phone'          ,          'Created'          ,          'Status'          );                    fputcsv          (          $f          ,          $fields          ,          $delimiter          );                    // Get records from the database                    $result          =          $db          ->          query          (          "SELECT * FROM members ORDER Past id DESC"          );  if(          $result          ->          num_rows          >          0          ){          // Output each row of the data, format line as csv and write to file pointer          while(          $row          =          $result          ->          fetch_assoc          ()){          $lineData          = array(          $row          [          'id'          ],          $row          [          'proper noun'          ],          $row          [          'electronic mail'          ],          $row          [          'phone'          ],          $row          [          'created'          ],          $row          [          'condition'          ]);          fputcsv          (          $f          ,          $lineData          ,          $delimiter          );      }  }                    // Move back to beginning of file                    fseek          (          $f          ,          0          );                    // Set headers to download file rather than displayed                    header          (          'Content-Blazon: text/csv'          );                    header          (          'Content-Disposition: attachment; filename="'          .          $filename          .          '";'          );                    // Output all remaining information on a file pointer                    fpassthru          (          $f          );                    // Exit from file                    exit();        

Export HTML Table Data to CSV using JavaScript

Conclusion

Our instance script helps yous to hands add the import and consign feature to the data list using PHP and MySQL. To make the data management section convenient, the export and import functionality is a great option. As well, you tin can enhance our import CSV file and export data to CSV script with PHP and MySQL as per your needs.

Are you want to get implementation assist, or modify or raise the functionality of this script? Submit Paid Service Request

If y'all accept any questions virtually this script, submit information technology to our QA community - Ask Question

barnesdartakifinee.blogspot.com

Source: https://www.codexworld.com/import-export-csv-file-data-using-php-mysql/

Share this post