Insert data into SQL table from CSV file with PHP
I need to import data from CSV file to SQL table with PHP code, I have followed 2 guids from Youtube step by step and I always get the same error
"Notice: Undefined offset: 1 in C:xampphtdocs..."
This code I copy/paste with just a few littel changes because I start to doubt I'm not writing correctly the code,,,, take a look
<?php
$connect = mysqli_connect("localhost", "websever", "Solna201056", "ts_excel");//databse connectivity
if(isset($_POST["submit"])) {
if($_FILES['file']['name']) {
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv') {
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file {
$item1 = mysqli_real_escape_string($connect, $data[0]);
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file
$query = "INSERT into ts_excel
(ts_name, ts_email)
values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV</title>
</head>
<body>
<form method="post" enctype="multipart/form-data">
<label>Select CSV File:</label>
<input type="file" name="file">
<br>
<input type="submit" name="submit" value="Import">
</form>
</body>
</html>
And this is the sorce, I also creatde a new database and table with the exact same names that is used in the guide to make sure that it is not me doing somthing wrong, when I choose the file and uplode it I get "Notice: Undefined offset: 1 in C:xampphtdocs at the SQL table, the data dont get saparated i think
take a look https://imgur.com/YvDdzDV
and the cvs file looks like this https://imgur.com/5Prr0cH
so the "blalabla@gmail.bla" should be under "ts_email". I think there is the problem. Any suggestions?
php mysql sql csv
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
|
show 3 more comments
I need to import data from CSV file to SQL table with PHP code, I have followed 2 guids from Youtube step by step and I always get the same error
"Notice: Undefined offset: 1 in C:xampphtdocs..."
This code I copy/paste with just a few littel changes because I start to doubt I'm not writing correctly the code,,,, take a look
<?php
$connect = mysqli_connect("localhost", "websever", "Solna201056", "ts_excel");//databse connectivity
if(isset($_POST["submit"])) {
if($_FILES['file']['name']) {
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv') {
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file {
$item1 = mysqli_real_escape_string($connect, $data[0]);
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file
$query = "INSERT into ts_excel
(ts_name, ts_email)
values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV</title>
</head>
<body>
<form method="post" enctype="multipart/form-data">
<label>Select CSV File:</label>
<input type="file" name="file">
<br>
<input type="submit" name="submit" value="Import">
</form>
</body>
</html>
And this is the sorce, I also creatde a new database and table with the exact same names that is used in the guide to make sure that it is not me doing somthing wrong, when I choose the file and uplode it I get "Notice: Undefined offset: 1 in C:xampphtdocs at the SQL table, the data dont get saparated i think
take a look https://imgur.com/YvDdzDV
and the cvs file looks like this https://imgur.com/5Prr0cH
so the "blalabla@gmail.bla" should be under "ts_email". I think there is the problem. Any suggestions?
php mysql sql csv
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Error checking but if you cannot be bothered, Addini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.
– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO"websever"and you do ot check that the connection was actually made cuccessfully
– RiggsFolly
Nov 22 '18 at 19:48
2
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
This warning has nothing to do with SQL. It's probably telling you$data[1]doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is tofgetcsv()if it's something else. See the documentation php.net/fgetcsv
– Havenard
Nov 22 '18 at 19:55
|
show 3 more comments
I need to import data from CSV file to SQL table with PHP code, I have followed 2 guids from Youtube step by step and I always get the same error
"Notice: Undefined offset: 1 in C:xampphtdocs..."
This code I copy/paste with just a few littel changes because I start to doubt I'm not writing correctly the code,,,, take a look
<?php
$connect = mysqli_connect("localhost", "websever", "Solna201056", "ts_excel");//databse connectivity
if(isset($_POST["submit"])) {
if($_FILES['file']['name']) {
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv') {
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file {
$item1 = mysqli_real_escape_string($connect, $data[0]);
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file
$query = "INSERT into ts_excel
(ts_name, ts_email)
values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV</title>
</head>
<body>
<form method="post" enctype="multipart/form-data">
<label>Select CSV File:</label>
<input type="file" name="file">
<br>
<input type="submit" name="submit" value="Import">
</form>
</body>
</html>
And this is the sorce, I also creatde a new database and table with the exact same names that is used in the guide to make sure that it is not me doing somthing wrong, when I choose the file and uplode it I get "Notice: Undefined offset: 1 in C:xampphtdocs at the SQL table, the data dont get saparated i think
take a look https://imgur.com/YvDdzDV
and the cvs file looks like this https://imgur.com/5Prr0cH
so the "blalabla@gmail.bla" should be under "ts_email". I think there is the problem. Any suggestions?
php mysql sql csv
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
I need to import data from CSV file to SQL table with PHP code, I have followed 2 guids from Youtube step by step and I always get the same error
"Notice: Undefined offset: 1 in C:xampphtdocs..."
This code I copy/paste with just a few littel changes because I start to doubt I'm not writing correctly the code,,,, take a look
<?php
$connect = mysqli_connect("localhost", "websever", "Solna201056", "ts_excel");//databse connectivity
if(isset($_POST["submit"])) {
if($_FILES['file']['name']) {
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv') {
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file {
$item1 = mysqli_real_escape_string($connect, $data[0]);
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file
$query = "INSERT into ts_excel
(ts_name, ts_email)
values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV</title>
</head>
<body>
<form method="post" enctype="multipart/form-data">
<label>Select CSV File:</label>
<input type="file" name="file">
<br>
<input type="submit" name="submit" value="Import">
</form>
</body>
</html>
And this is the sorce, I also creatde a new database and table with the exact same names that is used in the guide to make sure that it is not me doing somthing wrong, when I choose the file and uplode it I get "Notice: Undefined offset: 1 in C:xampphtdocs at the SQL table, the data dont get saparated i think
take a look https://imgur.com/YvDdzDV
and the cvs file looks like this https://imgur.com/5Prr0cH
so the "blalabla@gmail.bla" should be under "ts_email". I think there is the problem. Any suggestions?
php mysql sql csv
php mysql sql csv
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
edited Nov 22 '18 at 19:47
RiggsFolly
70.5k1864111
70.5k1864111
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
asked Nov 22 '18 at 17:55
webbserverprogrameringwebbserverprogramering
11
11
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Error checking but if you cannot be bothered, Addini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.
– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO"websever"and you do ot check that the connection was actually made cuccessfully
– RiggsFolly
Nov 22 '18 at 19:48
2
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
This warning has nothing to do with SQL. It's probably telling you$data[1]doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is tofgetcsv()if it's something else. See the documentation php.net/fgetcsv
– Havenard
Nov 22 '18 at 19:55
|
show 3 more comments
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Error checking but if you cannot be bothered, Addini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.
– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO"websever"and you do ot check that the connection was actually made cuccessfully
– RiggsFolly
Nov 22 '18 at 19:48
2
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
This warning has nothing to do with SQL. It's probably telling you$data[1]doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is tofgetcsv()if it's something else. See the documentation php.net/fgetcsv
– Havenard
Nov 22 '18 at 19:55
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Error checking but if you cannot be bothered, Add
ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.– RiggsFolly
Nov 22 '18 at 19:48
Error checking but if you cannot be bothered, Add
ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO
"websever" and you do ot check that the connection was actually made cuccessfully– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO
"websever" and you do ot check that the connection was actually made cuccessfully– RiggsFolly
Nov 22 '18 at 19:48
2
2
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
This warning has nothing to do with SQL. It's probably telling you
$data[1] doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is to fgetcsv() if it's something else. See the documentation php.net/fgetcsv– Havenard
Nov 22 '18 at 19:55
This warning has nothing to do with SQL. It's probably telling you
$data[1] doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is to fgetcsv() if it's something else. See the documentation php.net/fgetcsv– Havenard
Nov 22 '18 at 19:55
|
show 3 more comments
1 Answer
1
active
oldest
votes
It might be that Excel separates the columns by tabs and fgetcsv() expects a comma instead by default. Change one or the other and see how it goes.
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436141%2finsert-data-into-sql-table-from-csv-file-with-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It might be that Excel separates the columns by tabs and fgetcsv() expects a comma instead by default. Change one or the other and see how it goes.
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
add a comment |
It might be that Excel separates the columns by tabs and fgetcsv() expects a comma instead by default. Change one or the other and see how it goes.
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
add a comment |
It might be that Excel separates the columns by tabs and fgetcsv() expects a comma instead by default. Change one or the other and see how it goes.
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
It might be that Excel separates the columns by tabs and fgetcsv() expects a comma instead by default. Change one or the other and see how it goes.
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
answered Nov 22 '18 at 19:25
DanManDanMan
8,46432954
8,46432954
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436141%2finsert-data-into-sql-table-from-csv-file-with-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please post the first few rows of underlying CSV (not as a screenshot) which you can copy/paste from Notepad view of file. Redact any sensitive info as needed but carefully keep structure especially quotes and delimiters. With an example csv, I cannot reproduce your issue.
– Parfait
Nov 22 '18 at 18:59
Error checking but if you cannot be bothered, Add
ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors.– RiggsFolly
Nov 22 '18 at 19:48
This looks like a TYPO
"websever"and you do ot check that the connection was actually made cuccessfully– RiggsFolly
Nov 22 '18 at 19:48
2
Possible duplicate of "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP
– miken32
Nov 22 '18 at 19:49
This warning has nothing to do with SQL. It's probably telling you
$data[1]doesn't exist, so either the CSV only has 1 column or it failed to parse it altogether. Perhaps the delimiter in the file isn't a comma, you have to specify what the delimiter is tofgetcsv()if it's something else. See the documentation php.net/fgetcsv– Havenard
Nov 22 '18 at 19:55