7SzALRaL

1<?php
2
3 $server = "localhost";
4      $username = "root";
5      $pass = "";
6      $dbname = "test";
7
8
9      $conn = new PDO("mysql:host=$server;dbname=$dbname", $username, 
10$pass);
11      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
12
13
14// Parameters: filename.csv table_name
15
16$file = 'C:UsersHPDesktopACC.DBF.csv';
17$table = 'acc';
18
19// get structure from csv and insert db
20ini_set('auto_detect_line_endings',TRUE);
21$handle = fopen($file,'r');
22// first row, structure
23 if ( ($data = fgetcsv($handle) ) === FALSE ) {
24echo "Cannot read from csv $file";die();
25}
26$fields = array();
27$field_count = 0;
28for($i=0;$i<count($data); $i++) {
29$f = strtolower(trim($data[$i]));
30if ($f) {
31    // normalize the field name, strip to 20 chars if too long
32    $f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 20);
33    $field_count++;
34    $fields[] = $f.' VARCHAR(255)';
35}
36}
37
38$sqlcreate = $conn->prepare("CREATE TABLE $table (" . implode(', ', $fields) . ')');
39$sqlcreate->execute();
40
41echo "Create Table success" . "<br /><br />";
42//$db->query($sql);
43while ( ($data = fgetcsv($handle) ) !== FALSE ) {
44$fields = array();
45for($i=0;$i<$field_count; $i++) {
46    $fields[] = '''.addslashes($data[$i]).''';
47}
48 $sqlinsert = $conn->prepare("Insert into $table values(" . implode(', ', 
49 $fields) . ')');
50 $sqlinsert->execute();
51echo "Insert Table success" ; 
52
53}
54fclose($handle);
55ini_set('auto_detect_line_endings',FALSE);
56
57
58
59?>
60	
61<?php
62
63$fileName = './WP.csv';
64
65
66function connectDB()
67{
68    $server = "mysql2345";
69    $username = "root";
70    $pass = "root";
71    $dbname = "sc1";
72    $conn = new PDO("mysql:host=$server;dbname=$dbname", $username, $pass);
73    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
74    return $conn;
75}
76
77function createDb($csv_path, $db)
78{
79
80    if (($csv_handle = fopen($csv_path, "r")) === false) {
81        throw new Exception('Cannot open CSV file');
82    }
83
84    if(!isset($delimiter)) {
85        $delimiter = ',';
86    }
87
88    if (!isset($table)) {
89        $table = preg_replace("/[^A-Z0-9]/i", '', basename($csv_path));
90    }
91
92    if (!isset($fields)) {
93        $fields = array_map(function ($field){
94            return $field;
95        }, fgetcsv($csv_handle, 0, $delimiter));
96    }
97
98    $create_fields_str = join(', ', array_map(function ($field){
99        return "$field VARCHAR(200) NULL";
100    }, $fields));
101
102    echo $create_table_sql = "CREATE TABLE IF NOT EXISTS $table ($create_fields_str)";
103
104
105    $db->query($create_table_sql);
106
107    return ['table'=>$table, 'fields'=>$fields];
108
109}
110
111function loadData($fileName, $tableName, $fields, $db)
112{
113    $fieldStr = implode(',', $fields);
114
115    $query = <<<eof
116    LOAD DATA LOCAL INFILE '$fileName'
117     INTO TABLE $tableName
118     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
119     LINES TERMINATED BY 'r'
120    ($fieldStr)
121eof;
122
123    echo $query;
124
125    $db->query($query);
126
127}
128
129$db = connectDB();
130
131$tableInfo = createDb($fileName, $db);
132
133loadData($fileName, $tableInfo['table'], $tableInfo['fields'], $db);