Mixing
no results found when loading Excel file mySQL
1
I am trying to load an excel file .XLSX, when running it shows Success, but no results were found. In the excel file there are results mainly 3 columns:
Region (text column)
2018M01 (decimal values)
2018M02 (decimal values)
My code is:
DROP TABLE IF EXISTS book;
CREATE TABLE book (
Region LONGTEXT,
2018M01 decimal(4,2),
2018M02 decimal(4,2)
);
LOAD DATA LOCAL INFILE 'C:/Users/BPerei23/Downloads/Book1.xlsx'
REPLACE INTO TABLE book
CHARACTER SET Latin1 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES;
Can someone pls advice why its not loading properly? Thank you
mysql excel csv
edited Jan 1 at 14:34
O. Jones
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
add a comment |
1
I am trying to load an excel file .XLSX, when running it shows Success, but no results were found. In the excel file there are results mainly 3 columns:
Region (text column)
2018M01 (decimal values)
2018M02 (decimal values)
My code is:
DROP TABLE IF EXISTS book;
CREATE TABLE book (
Region LONGTEXT,
2018M01 decimal(4,2),
2018M02 decimal(4,2)
);
LOAD DATA LOCAL INFILE 'C:/Users/BPerei23/Downloads/Book1.xlsx'
REPLACE INTO TABLE book
CHARACTER SET Latin1 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES;
Can someone pls advice why its not loading properly? Thank you
mysql excel csv
edited Jan 1 at 14:34
O. Jones
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
add a comment |
1
1
1
I am trying to load an excel file .XLSX, when running it shows Success, but no results were found. In the excel file there are results mainly 3 columns:
Region (text column)
2018M01 (decimal values)
2018M02 (decimal values)
My code is:
DROP TABLE IF EXISTS book;
CREATE TABLE book (
Region LONGTEXT,
2018M01 decimal(4,2),
2018M02 decimal(4,2)
);
LOAD DATA LOCAL INFILE 'C:/Users/BPerei23/Downloads/Book1.xlsx'
REPLACE INTO TABLE book
CHARACTER SET Latin1 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES;
Can someone pls advice why its not loading properly? Thank you
mysql excel csv
edited Jan 1 at 14:34
O. Jones
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
I am trying to load an excel file .XLSX, when running it shows Success, but no results were found. In the excel file there are results mainly 3 columns:
Region (text column)
2018M01 (decimal values)
2018M02 (decimal values)
My code is:
DROP TABLE IF EXISTS book;
CREATE TABLE book (
Region LONGTEXT,
2018M01 decimal(4,2),
2018M02 decimal(4,2)
);
LOAD DATA LOCAL INFILE 'C:/Users/BPerei23/Downloads/Book1.xlsx'
REPLACE INTO TABLE book
CHARACTER SET Latin1 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES;
Can someone pls advice why its not loading properly? Thank you
mysql excel csv
mysql excel csv
edited Jan 1 at 14:34
O. Jones
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
edited Jan 1 at 14:34
O. Jones
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
edited Jan 1 at 14:34
O. Jones
60.2k974107
edited Jan 1 at 14:34
O. Jones
60.2k974107
edited Jan 1 at 14:34
O. Jones
60.2k974107
60.2k974107
asked Jan 1 at 14:30
bp123bp123
697
asked Jan 1 at 14:30
bp123bp123
697
asked Jan 1 at 14:30
bp123bp123
697
697
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
LOAD DATA INFILE doesn't handle Microsoft's proprietary .xlsx format for spreadsheets. Save the file as a .csv file and try again.
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, andLOAD DATAwon't be able to parse a binary Excel file. There must be some other problem with your code.
– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
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
});
}
});
draft saved
draft discarded
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%2f53996285%2fno-results-found-when-loading-excel-file-mysql%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
2
LOAD DATA INFILE doesn't handle Microsoft's proprietary .xlsx format for spreadsheets. Save the file as a .csv file and try again.
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, andLOAD DATAwon't be able to parse a binary Excel file. There must be some other problem with your code.
– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
add a comment |
2
LOAD DATA INFILE doesn't handle Microsoft's proprietary .xlsx format for spreadsheets. Save the file as a .csv file and try again.
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, andLOAD DATAwon't be able to parse a binary Excel file. There must be some other problem with your code.
– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
add a comment |
2
2
2
LOAD DATA INFILE doesn't handle Microsoft's proprietary .xlsx format for spreadsheets. Save the file as a .csv file and try again.
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
LOAD DATA INFILE doesn't handle Microsoft's proprietary .xlsx format for spreadsheets. Save the file as a .csv file and try again.
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:33
O. JonesO. Jones
60.2k974107
60.2k974107
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, andLOAD DATAwon't be able to parse a binary Excel file. There must be some other problem with your code.
– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
add a comment |
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, andLOAD DATAwon't be able to parse a binary Excel file. There must be some other problem with your code.
– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@OJones I tried this still no difference, it shows success but nothing loaded. It is a bit strange because it works on my Mac mySQL, but not on my windows mySQL
– bp123
Jan 1 at 14:45
@user23456 This is the correct answer, and
LOAD DATA won't be able to parse a binary Excel file. There must be some other problem with your code.– Tim Biegeleisen
Jan 1 at 14:49
@user23456 This is the correct answer, and
LOAD DATA won't be able to parse a binary Excel file. There must be some other problem with your code.– Tim Biegeleisen
Jan 1 at 14:49
ok it is working now. thanks
– bp123
Jan 1 at 14:50
ok it is working now. thanks
– bp123
Jan 1 at 14:50
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
@TimBiegeleisen is there any way to load an Excel file directly ?
– bp123
Jan 1 at 14:56
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
Note directly via MySQL, at least known of which I know. The app languages such as Java and C# have libraries which can directly parse an Excel file, if you are open to that.
– Tim Biegeleisen
Jan 1 at 14:57
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53996285%2fno-results-found-when-loading-excel-file-mysql%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
