Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- $server = "localhost";
- $username = "root";
- $pass = "";
- $dbname = "test";
- $conn = new PDO("mysql:host=$server;dbname=$dbname", $username,
- $pass);
- $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- // Parameters: filename.csv table_name
- $file = 'C:UsersHPDesktopACC.DBF.csv';
- $table = 'acc';
- // get structure from csv and insert db
- ini_set('auto_detect_line_endings',TRUE);
- $handle = fopen($file,'r');
- // first row, structure
- if ( ($data = fgetcsv($handle) ) === FALSE ) {
- echo "Cannot read from csv $file";die();
- }
- $fields = array();
- $field_count = 0;
- for($i=0;$i<count($data); $i++) {
- $f = strtolower(trim($data[$i]));
- if ($f) {
- // normalize the field name, strip to 20 chars if too long
- $f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 20);
- $field_count++;
- $fields[] = $f.' VARCHAR(255)';
- }
- }
- $sqlcreate = $conn->prepare("CREATE TABLE $table (" . implode(', ', $fields) . ')');
- $sqlcreate->execute();
- echo "Create Table success" . "<br /><br />";
- //$db->query($sql);
- while ( ($data = fgetcsv($handle) ) !== FALSE ) {
- $fields = array();
- for($i=0;$i<$field_count; $i++) {
- $fields[] = '''.addslashes($data[$i]).''';
- }
- $sqlinsert = $conn->prepare("Insert into $table values(" . implode(', ',
- $fields) . ')');
- $sqlinsert->execute();
- echo "Insert Table success" ;
- }
- fclose($handle);
- ini_set('auto_detect_line_endings',FALSE);
- ?>
- <?php
- $fileName = './WP.csv';
- function connectDB()
- {
- $server = "mysql2345";
- $username = "root";
- $pass = "root";
- $dbname = "sc1";
- $conn = new PDO("mysql:host=$server;dbname=$dbname", $username, $pass);
- $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- return $conn;
- }
- function createDb($csv_path, $db)
- {
- if (($csv_handle = fopen($csv_path, "r")) === false) {
- throw new Exception('Cannot open CSV file');
- }
- if(!isset($delimiter)) {
- $delimiter = ',';
- }
- if (!isset($table)) {
- $table = preg_replace("/[^A-Z0-9]/i", '', basename($csv_path));
- }
- if (!isset($fields)) {
- $fields = array_map(function ($field){
- return $field;
- }, fgetcsv($csv_handle, 0, $delimiter));
- }
- $create_fields_str = join(', ', array_map(function ($field){
- return "$field VARCHAR(200) NULL";
- }, $fields));
- echo $create_table_sql = "CREATE TABLE IF NOT EXISTS $table ($create_fields_str)";
- $db->query($create_table_sql);
- return ['table'=>$table, 'fields'=>$fields];
- }
- function loadData($fileName, $tableName, $fields, $db)
- {
- $fieldStr = implode(',', $fields);
- $query = <<<eof
- LOAD DATA LOCAL INFILE '$fileName'
- INTO TABLE $tableName
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY 'r'
- ($fieldStr)
- eof;
- echo $query;
- $db->query($query);
- }
- $db = connectDB();
- $tableInfo = createDb($fileName, $db);
- loadData($fileName, $tableInfo['table'], $tableInfo['fields'], $db);
Add Comment
Please, Sign In to add comment