MySQL: LOAD DATA INFILE producing incorrect table results
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am trying to use LOAD DATA INFILE to load my data into tables.
My table:
Alcohol name varchar(45)
Type varchar(45)
ABV FLOAT
Brewery INT(11)
Average Price DOUBLE
Description VARCHAR(1000)
My CSV file looks like:
Alcohol name,Type,ABV,Brewery,Average Price,Description
Hocus Pocus,Beer,4.5,812,0,"Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange."
Grimbergen Blonde,Beer,6.699999809,264,0,None
Widdershins Barleywine,Beer,9.100000381,779,0,None
Lucifer,Beer,8.5,287,0,None
Bitter,Beer,4,1056,0,None
Winter Warmer,Beer,5.199999809,1385,0,None
Winter Welcome 2007-2008,Beer,6,1099,0,None
etc....
My LOAD Command looks like:
LOAD DATA LOCAL INFILE '/home/leo/CS336/Tables/beers.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' IGNORE 1 LINES;
The garbage table I'm getting looks like:
Alcohol name Type ABV Brewery Average Price Description
Golden Ale NULL 0 0 NULL NULL
* 10.5% (2008) NULL 0 0 NULL NULL
* 15.5 gallon keg NULL 0 0 NULL NULL
* 22 oz. bottles NULL 0 0 NULL NULL
* 5.17 gallon keg NULL 0 0 NULL NULL
* 9.78% (2007)" NULL 0 0 NULL NULL
* Available March through August NULL 0 0 NULL NULL
etc...
mysql sql csv mysql-workbench innodb
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
|
show 4 more comments
0
I am trying to use LOAD DATA INFILE to load my data into tables.
My table:
Alcohol name varchar(45)
Type varchar(45)
ABV FLOAT
Brewery INT(11)
Average Price DOUBLE
Description VARCHAR(1000)
My CSV file looks like:
Alcohol name,Type,ABV,Brewery,Average Price,Description
Hocus Pocus,Beer,4.5,812,0,"Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange."
Grimbergen Blonde,Beer,6.699999809,264,0,None
Widdershins Barleywine,Beer,9.100000381,779,0,None
Lucifer,Beer,8.5,287,0,None
Bitter,Beer,4,1056,0,None
Winter Warmer,Beer,5.199999809,1385,0,None
Winter Welcome 2007-2008,Beer,6,1099,0,None
etc....
My LOAD Command looks like:
LOAD DATA LOCAL INFILE '/home/leo/CS336/Tables/beers.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' IGNORE 1 LINES;
The garbage table I'm getting looks like:
Alcohol name Type ABV Brewery Average Price Description
Golden Ale NULL 0 0 NULL NULL
* 10.5% (2008) NULL 0 0 NULL NULL
* 15.5 gallon keg NULL 0 0 NULL NULL
* 22 oz. bottles NULL 0 0 NULL NULL
* 5.17 gallon keg NULL 0 0 NULL NULL
* 9.78% (2007)" NULL 0 0 NULL NULL
* Available March through August NULL 0 0 NULL NULL
etc...
mysql sql csv mysql-workbench innodb
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
You have commas in the description field. Add alsoOPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additionalINSERT/SELECTto put it in the correct format.
– petrch
Nov 17 '18 at 9:38
|
show 4 more comments
0
0
0
I am trying to use LOAD DATA INFILE to load my data into tables.
My table:
Alcohol name varchar(45)
Type varchar(45)
ABV FLOAT
Brewery INT(11)
Average Price DOUBLE
Description VARCHAR(1000)
My CSV file looks like:
Alcohol name,Type,ABV,Brewery,Average Price,Description
Hocus Pocus,Beer,4.5,812,0,"Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange."
Grimbergen Blonde,Beer,6.699999809,264,0,None
Widdershins Barleywine,Beer,9.100000381,779,0,None
Lucifer,Beer,8.5,287,0,None
Bitter,Beer,4,1056,0,None
Winter Warmer,Beer,5.199999809,1385,0,None
Winter Welcome 2007-2008,Beer,6,1099,0,None
etc....
My LOAD Command looks like:
LOAD DATA LOCAL INFILE '/home/leo/CS336/Tables/beers.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' IGNORE 1 LINES;
The garbage table I'm getting looks like:
Alcohol name Type ABV Brewery Average Price Description
Golden Ale NULL 0 0 NULL NULL
* 10.5% (2008) NULL 0 0 NULL NULL
* 15.5 gallon keg NULL 0 0 NULL NULL
* 22 oz. bottles NULL 0 0 NULL NULL
* 5.17 gallon keg NULL 0 0 NULL NULL
* 9.78% (2007)" NULL 0 0 NULL NULL
* Available March through August NULL 0 0 NULL NULL
etc...
mysql sql csv mysql-workbench innodb
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
I am trying to use LOAD DATA INFILE to load my data into tables.
My table:
Alcohol name varchar(45)
Type varchar(45)
ABV FLOAT
Brewery INT(11)
Average Price DOUBLE
Description VARCHAR(1000)
My CSV file looks like:
Alcohol name,Type,ABV,Brewery,Average Price,Description
Hocus Pocus,Beer,4.5,812,0,"Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange."
Grimbergen Blonde,Beer,6.699999809,264,0,None
Widdershins Barleywine,Beer,9.100000381,779,0,None
Lucifer,Beer,8.5,287,0,None
Bitter,Beer,4,1056,0,None
Winter Warmer,Beer,5.199999809,1385,0,None
Winter Welcome 2007-2008,Beer,6,1099,0,None
etc....
My LOAD Command looks like:
LOAD DATA LOCAL INFILE '/home/leo/CS336/Tables/beers.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' IGNORE 1 LINES;
The garbage table I'm getting looks like:
Alcohol name Type ABV Brewery Average Price Description
Golden Ale NULL 0 0 NULL NULL
* 10.5% (2008) NULL 0 0 NULL NULL
* 15.5 gallon keg NULL 0 0 NULL NULL
* 22 oz. bottles NULL 0 0 NULL NULL
* 5.17 gallon keg NULL 0 0 NULL NULL
* 9.78% (2007)" NULL 0 0 NULL NULL
* Available March through August NULL 0 0 NULL NULL
etc...
mysql sql csv mysql-workbench innodb
mysql sql csv mysql-workbench innodb
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
edited Nov 17 '18 at 9:03
Leo Scarano
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
asked Nov 17 '18 at 4:39
Leo ScaranoLeo Scarano
418
418
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
You have commas in the description field. Add alsoOPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additionalINSERT/SELECTto put it in the correct format.
– petrch
Nov 17 '18 at 9:38
|
show 4 more comments
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
You have commas in the description field. Add alsoOPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additionalINSERT/SELECTto put it in the correct format.
– petrch
Nov 17 '18 at 9:38
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
You have commas in the description field. Add also
OPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additional INSERT/SELECT to put it in the correct format.– petrch
Nov 17 '18 at 9:38
You have commas in the description field. Add also
OPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additional INSERT/SELECT to put it in the correct format.– petrch
Nov 17 '18 at 9:38
|
show 4 more comments
1 Answer
1
active
oldest
votes
1
I created a table based on your description (Average_Price I added an underscore)
create table Alcohol (
name varchar(45),
Type varchar(45),
ABV FLOAT,
Brewery INT(11),
Average_Price DOUBLE,
Description VARCHAR(1000)
);
I loaded your example file like this
LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
And I think I got reasonable results:
`SELECT * from Alcohol;
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | Type | ABV | Brewery | Average_Price | Description |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus | Beer | 4.5 | 812 | 0 | Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde | Beer | 6.7 | 264 | 0 | None |
| Widdershins Barleywine | Beer | 9.1 | 779 | 0 | None |
| Lucifer | Beer | 8.5 | 287 | 0 | None |
| Bitter | Beer | 4 | 1056 | 0 | None |
| Winter Warmer | Beer | 5.2 | 1385 | 0 | None |
| Winter Welcome 2007-2008 | Beer | 6 | 1099 | 0 | None |
| | NULL | NULL | NULL | NULL | NULL |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.sin the client shows that). TheOPTIONALLY ENCLOSED BYsays that if the column is in quotes it will ignore commas as column separators.
– petrch
Nov 17 '18 at 11:22
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%2f53348270%2fmysql-load-data-infile-producing-incorrect-table-results%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
1
I created a table based on your description (Average_Price I added an underscore)
create table Alcohol (
name varchar(45),
Type varchar(45),
ABV FLOAT,
Brewery INT(11),
Average_Price DOUBLE,
Description VARCHAR(1000)
);
I loaded your example file like this
LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
And I think I got reasonable results:
`SELECT * from Alcohol;
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | Type | ABV | Brewery | Average_Price | Description |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus | Beer | 4.5 | 812 | 0 | Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde | Beer | 6.7 | 264 | 0 | None |
| Widdershins Barleywine | Beer | 9.1 | 779 | 0 | None |
| Lucifer | Beer | 8.5 | 287 | 0 | None |
| Bitter | Beer | 4 | 1056 | 0 | None |
| Winter Warmer | Beer | 5.2 | 1385 | 0 | None |
| Winter Welcome 2007-2008 | Beer | 6 | 1099 | 0 | None |
| | NULL | NULL | NULL | NULL | NULL |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.sin the client shows that). TheOPTIONALLY ENCLOSED BYsays that if the column is in quotes it will ignore commas as column separators.
– petrch
Nov 17 '18 at 11:22
add a comment |
1
I created a table based on your description (Average_Price I added an underscore)
create table Alcohol (
name varchar(45),
Type varchar(45),
ABV FLOAT,
Brewery INT(11),
Average_Price DOUBLE,
Description VARCHAR(1000)
);
I loaded your example file like this
LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
And I think I got reasonable results:
`SELECT * from Alcohol;
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | Type | ABV | Brewery | Average_Price | Description |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus | Beer | 4.5 | 812 | 0 | Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde | Beer | 6.7 | 264 | 0 | None |
| Widdershins Barleywine | Beer | 9.1 | 779 | 0 | None |
| Lucifer | Beer | 8.5 | 287 | 0 | None |
| Bitter | Beer | 4 | 1056 | 0 | None |
| Winter Warmer | Beer | 5.2 | 1385 | 0 | None |
| Winter Welcome 2007-2008 | Beer | 6 | 1099 | 0 | None |
| | NULL | NULL | NULL | NULL | NULL |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.sin the client shows that). TheOPTIONALLY ENCLOSED BYsays that if the column is in quotes it will ignore commas as column separators.
– petrch
Nov 17 '18 at 11:22
add a comment |
1
1
1
I created a table based on your description (Average_Price I added an underscore)
create table Alcohol (
name varchar(45),
Type varchar(45),
ABV FLOAT,
Brewery INT(11),
Average_Price DOUBLE,
Description VARCHAR(1000)
);
I loaded your example file like this
LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
And I think I got reasonable results:
`SELECT * from Alcohol;
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | Type | ABV | Brewery | Average_Price | Description |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus | Beer | 4.5 | 812 | 0 | Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde | Beer | 6.7 | 264 | 0 | None |
| Widdershins Barleywine | Beer | 9.1 | 779 | 0 | None |
| Lucifer | Beer | 8.5 | 287 | 0 | None |
| Bitter | Beer | 4 | 1056 | 0 | None |
| Winter Warmer | Beer | 5.2 | 1385 | 0 | None |
| Winter Welcome 2007-2008 | Beer | 6 | 1099 | 0 | None |
| | NULL | NULL | NULL | NULL | NULL |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
I created a table based on your description (Average_Price I added an underscore)
create table Alcohol (
name varchar(45),
Type varchar(45),
ABV FLOAT,
Brewery INT(11),
Average_Price DOUBLE,
Description VARCHAR(1000)
);
I loaded your example file like this
LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
And I think I got reasonable results:
`SELECT * from Alcohol;
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | Type | ABV | Brewery | Average_Price | Description |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus | Beer | 4.5 | 812 | 0 | Our take on a classic summer ale. A toast to weeds, rays, and summer haze. A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde | Beer | 6.7 | 264 | 0 | None |
| Widdershins Barleywine | Beer | 9.1 | 779 | 0 | None |
| Lucifer | Beer | 8.5 | 287 | 0 | None |
| Bitter | Beer | 4 | 1056 | 0 | None |
| Winter Warmer | Beer | 5.2 | 1385 | 0 | None |
| Winter Welcome 2007-2008 | Beer | 6 | 1099 | 0 | None |
| | NULL | NULL | NULL | NULL | NULL |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
edited Nov 17 '18 at 11:14
P.Salmon
8,2382515
8,2382515
answered Nov 17 '18 at 11:09
petrchpetrch
32627
answered Nov 17 '18 at 11:09
petrchpetrch
32627
answered Nov 17 '18 at 11:09
petrchpetrch
32627
32627
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.sin the client shows that). TheOPTIONALLY ENCLOSED BYsays that if the column is in quotes it will ignore commas as column separators.
– petrch
Nov 17 '18 at 11:22
add a comment |
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.sin the client shows that). TheOPTIONALLY ENCLOSED BYsays that if the column is in quotes it will ignore commas as column separators.
– petrch
Nov 17 '18 at 11:22
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I don't get this result without the inclusion of a line terminator 'rn' on my windows box.
– P.Salmon
Nov 17 '18 at 11:16
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
I removed all commas from my description line replaced them with a special char. Then I used this command, and replaced the special char with a ',' in MySQL when the tables were loaded. This was because some of my description fields werent enclosed by quotations. Thank y'all for your help!
– Leo Scarano
Nov 17 '18 at 11:20
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.
s in the client shows that). The OPTIONALLY ENCLOSED BY says that if the column is in quotes it will ignore commas as column separators.– petrch
Nov 17 '18 at 11:22
Well, windows makes things sometimes more difficult. I did it on linux in my virtualbox. In that case I would suggest also to provide the CHARACTER SET as explained here dev.mysql.com/doc/refman/8.0/en/load-data.html. Also, make sure that youre server/table and client character sets do match (e.g.
s in the client shows that). The OPTIONALLY ENCLOSED BY says that if the column is in quotes it will ignore commas as column separators.– petrch
Nov 17 '18 at 11:22
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%2f53348270%2fmysql-load-data-infile-producing-incorrect-table-results%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
Your CSV file isn't a csv file (it looks like excel) - can you add the first 4 rows of your actual csv file (the one with the commas) as text to the question. You might also try adding the lines terminated by option to your load statement 'n' or possibly 'rn'
– P.Salmon
Nov 17 '18 at 8:02
It is a csv, excel supports csv types. I could post the txt file but it would be the same effect. I'll try 'n' and 'rn'.
– Leo Scarano
Nov 17 '18 at 8:21
If you post it as a csv text file then we can play with it and come up with a solution but to be honest I cannot be bothered to retype the data.
– P.Salmon
Nov 17 '18 at 8:52
@P.Salmon done. sorry about that
– Leo Scarano
Nov 17 '18 at 9:03
You have commas in the description field. Add also
OPTIONALLY ENCLOSED BY '"'. Other trick I did was that I had a temporary table with all varchars and I made sure I load correctly into that one and then I used additionalINSERT/SELECTto put it in the correct format.– petrch
Nov 17 '18 at 9:38