Simple web application for convert xlsx to SQL format.
- Download code import_xlsx_to_sql
- simple set database connection and xlsx file name to below code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>Convert xlsx to sql</title> <meta name="description" content="This tutorial will learn how to import excel sheet data in mysql database using php. Here, first upload an excel sheet into your server and then click to import it into database. All column of excel sheet will store into your corrosponding database table."/> <meta name="keywords" content="import excel file data in mysql, upload ecxel file in mysql, upload data, code to import excel data in mysql database, php, Mysql, Ajax, Jquery, Javascript, download, upload, upload excel file,mysql"/> </head> <body> <?php /* * ********************** YOUR DATABASE CONNECTION START HERE *************************** */ //////// CONFIG define("DB_HOST", "localhost"); // set database host define("DB_USER", "root"); // set database user define("DB_PASS", "root"); // set database password define("DB_NAME", "book"); $inputFileName = 'Book.xlsx'; $databasetable="table_book"; //////// END CONFIG /* * ********************** YOUR DATABASE CONNECTION END HERE *************************** */ $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection."); $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database"); set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/'); include 'PHPExcel/IOFactory.php'; // This is the file path to be uploaded. try { $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); $arrayCount = count($allDataInSheet); // Here get total count of row in that Excel sheet $first_row = ($allDataInSheet[1]); $alll_filed = (array_keys($first_row)); $num_of_all_filed = count($alll_filed); function clean($string) { $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens. return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars. } //Create dynemic table $insert_table = "CREATE TABLE IF NOT EXISTS `" . $databasetable . "` ( `" . $databasetable . "_id` int(11) NOT NULL AUTO_INCREMENT, "; for ($z = 0; $z < count($alll_filed); $z++) { $filed_name = str_replace(" ", "", trim($first_row[$alll_filed[$z]])); $filed_name = str_replace(".", "", $filed_name); $filed_name = str_replace("-", "", $filed_name); $filed_name = strtolower($filed_name); $filed_name = clean($filed_name); if ($filed_name != "") { $insert_table.="`" . $filed_name . "` text NOT NULL,"; } $first_row[$alll_filed[$z]]=$filed_name; } $insert_table.=" PRIMARY KEY (`" . $databasetable . "_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 "; $sql = $insert_table; if (mysql_query($sql, $link)) { echo 'Table created'; } else { echo "Error creating database: " . mysql_error(); die; } echo '<pre>'; echo '<table border=1 style="border-collapse: collapse">'; echo '<tr>'; echo '<td>'; echo $databasetable . "_id"; echo '</td>'; for ($z = 0; $z < count($alll_filed); $z++) { echo '<td>'; echo $first_row[$alll_filed[$z]]; echo '</td>'; } echo '<td>'; echo 'status'; echo '</td>'; echo '</tr>'; $j = 0; for ($i = 2; $i <= $arrayCount; $i++) { echo '<tr>'; echo '<td>'; echo $j = $j + 1; echo '</td>'; $sql = "INSERT INTO " . $databasetable . ""; for ($z = 0; $z < count($alll_filed); $z++) { $column[$z] = trim($allDataInSheet[$i][$alll_filed[$z]]); $column[$first_row[$alll_filed[$z]]] = mysql_escape_string($column[$z]); echo '<td>'; echo $column[$first_row[$alll_filed[$z]]]; echo '</td>'; if($first_row[$alll_filed[$z]]!=""){ $key['key'][$z] = $first_row[$alll_filed[$z]]; $key['value'][$z] = "'" . $column[$first_row[$alll_filed[$z]]] . "'"; } } $mysql_key = implode(",", $key['key']); $mysql_value = implode(",", $key['value']); $sql.= " (" . $mysql_key . ") VALUES (" . $mysql_value . ")"; echo '<td>'; if (mysql_query($sql, $link)) { echo "Database my_db created :" . $sql; } else { echo "Error creating database: " . mysql_error(); } echo '</td>'; echo '</tr>'; } echo '</table>'; ?> <body> </html> |
More Stories
How to set EC2 Ubuntu default password
CPU & Memory usage in PHP
Install PHP mcrypt extension on Ubuntu