Skip eerste lijn van csv
Een csv van mijn bank wil ik in MSQL importeren.
Alleen de 1e line (Header) wil ik overslaan.
Van alles geprobeerd, maar het lukt me niet.
Graag hulp bij dit script..,
B.v.d. Henque
Code (php)
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
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
<?php
define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'geen');
define('DB_NAME', 'rabobank');
@$conn = mysql_connect (DB_SERVER, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME,$conn);
if(!$conn){
die( "Sorry! There seems to be a problem connecting to our database.");
}
error_reporting (E_ALL-E_NOTICE);
$table_db = 'csvimport';
function errors($error){
if (!empty($error))
{
$i = 0;
while ($i < count($error)){
$showError.= '<div class="msg-error">'.$error[$i].'</div>';
$i ++;}
return $showError;
}// close if empty errors
} // close function
if (isset($_POST['upfile'])){
if (!$error){
$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
for ($c=0; $c < 1; $c++) {
//only run if the first column if not equal to veld1
if($data[0] !='veld1'){
mysql_query("INSERT INTO ".$table_db."(
veld1,
veld2,
veld3,
veld4,
veld5,
veld6,
veld7,
veld8,
veld9,
veld10,
veld11,
veld12,
veld13,
veld14,
veld15,
veld16,
veld17,
veld18,
veld19,
veld20,
veld21,
veld22,
veld23,
veld24,
veld25,
veld26
)VALUES(
'".mysql_real_escape_string($data[0])."',
'".mysql_real_escape_string($data[1])."',
'".mysql_real_escape_string($data[2])."',
'".mysql_real_escape_string($data[3])."',
'".mysql_real_escape_string($data[4])."',
'".mysql_real_escape_string($data[5])."',
'".mysql_real_escape_string($data[6])."',
'".mysql_real_escape_string($data[7])."',
'".mysql_real_escape_string($data[8])."',
'".mysql_real_escape_string($data[9])."',
'".mysql_real_escape_string($data[10])."',
'".mysql_real_escape_string($data[11])."',
'".mysql_real_escape_string($data[12])."',
'".mysql_real_escape_string($data[13])."',
'".mysql_real_escape_string($data[14])."',
'".mysql_real_escape_string($data[15])."',
'".mysql_real_escape_string($data[16])."',
'".mysql_real_escape_string($data[17])."',
'".mysql_real_escape_string($data[18])."',
'".mysql_real_escape_string($data[19])."',
'".mysql_real_escape_string($data[20])."',
'".mysql_real_escape_string($data[21])."',
'".mysql_real_escape_string($data[22])."',
'".mysql_real_escape_string($data[23])."',
'".mysql_real_escape_string($data[24])."',
'".mysql_real_escape_string($data[25])."'
)")or die(mysql_error());
}
$tot++;}
}
fclose($handle);
$content.= "<div class='success' id='message'> CSV File Imported, $tot records added </div>";
}// end no error
}//close if isset upfile
$er = errors($error);
$content.= <<<EOF
<h3>Import CSV Data</h3>
$er
<form enctype="multipart/form-data" action="" method="post">
File:<input name="uploaded" type="file" maxlength="20" /><input type="submit" name="upfile" value="Upload File">
</form>
EOF;
echo $content;
?>
define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'geen');
define('DB_NAME', 'rabobank');
@$conn = mysql_connect (DB_SERVER, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME,$conn);
if(!$conn){
die( "Sorry! There seems to be a problem connecting to our database.");
}
error_reporting (E_ALL-E_NOTICE);
$table_db = 'csvimport';
function errors($error){
if (!empty($error))
{
$i = 0;
while ($i < count($error)){
$showError.= '<div class="msg-error">'.$error[$i].'</div>';
$i ++;}
return $showError;
}// close if empty errors
} // close function
if (isset($_POST['upfile'])){
if (!$error){
$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
for ($c=0; $c < 1; $c++) {
//only run if the first column if not equal to veld1
if($data[0] !='veld1'){
mysql_query("INSERT INTO ".$table_db."(
veld1,
veld2,
veld3,
veld4,
veld5,
veld6,
veld7,
veld8,
veld9,
veld10,
veld11,
veld12,
veld13,
veld14,
veld15,
veld16,
veld17,
veld18,
veld19,
veld20,
veld21,
veld22,
veld23,
veld24,
veld25,
veld26
)VALUES(
'".mysql_real_escape_string($data[0])."',
'".mysql_real_escape_string($data[1])."',
'".mysql_real_escape_string($data[2])."',
'".mysql_real_escape_string($data[3])."',
'".mysql_real_escape_string($data[4])."',
'".mysql_real_escape_string($data[5])."',
'".mysql_real_escape_string($data[6])."',
'".mysql_real_escape_string($data[7])."',
'".mysql_real_escape_string($data[8])."',
'".mysql_real_escape_string($data[9])."',
'".mysql_real_escape_string($data[10])."',
'".mysql_real_escape_string($data[11])."',
'".mysql_real_escape_string($data[12])."',
'".mysql_real_escape_string($data[13])."',
'".mysql_real_escape_string($data[14])."',
'".mysql_real_escape_string($data[15])."',
'".mysql_real_escape_string($data[16])."',
'".mysql_real_escape_string($data[17])."',
'".mysql_real_escape_string($data[18])."',
'".mysql_real_escape_string($data[19])."',
'".mysql_real_escape_string($data[20])."',
'".mysql_real_escape_string($data[21])."',
'".mysql_real_escape_string($data[22])."',
'".mysql_real_escape_string($data[23])."',
'".mysql_real_escape_string($data[24])."',
'".mysql_real_escape_string($data[25])."'
)")or die(mysql_error());
}
$tot++;}
}
fclose($handle);
$content.= "<div class='success' id='message'> CSV File Imported, $tot records added </div>";
}// end no error
}//close if isset upfile
$er = errors($error);
$content.= <<<EOF
<h3>Import CSV Data</h3>
$er
<form enctype="multipart/form-data" action="" method="post">
File:<input name="uploaded" type="file" maxlength="20" /><input type="submit" name="upfile" value="Upload File">
</form>
EOF;
echo $content;
?>
Code (php)
1
fgetcsv($handle, 1000, ",")
Die "eet" dan je header regel op.
Gewijzigd op 31/08/2018 17:56:56 door Rob Doemaarwat
Ga ik proberen.
Toevoeging op 31/08/2018 18:04:04:
Krijg een error bij de "while"
Parse error: syntax error, unexpected 'while' (T_WHILE) in /Applications/XAMPP/xamppfiles/htdocs/bank/csvimport.php on line 34
$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
fgetcsv($handle, 1000, ",")
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
for ($c=0; $c < 1; $c++) {
Als het bestand erg groot is kan dit mogelijk vertragen aangezien het bestand nu 2 keer wordt gelezen.
Je kunt ook de eerste regel skippen met een if else.
Code (php)
Let er op dat je gebruikt maakt van de verouderde mysql functie. Is PHP7 bestaat deze functie niet meer en in versies daarvoor zou je een 'deprecated' (verouderd) waarschuwing moeten krijgen. Check deze pagina over het verschil van de oude en nieuwe functie.
Bedankt