SQL-Basics(3A) (How to load bulk data into table In MYSQL Database)
------------- Table Creation--------------------
create table if not exists dress(
Dress_ID varchar(30),
Style varchar(30),
Price varchar(30),
Rating varchar(30),
Size varchar(30),
Season varchar(30),
NeckLine varchar(30),
SleeveLength varchar(30),
waiseline varchar(30),
Material varchar(30),
FabricType varchar(30),
Decoration varchar(30),
Pattern Type varchar(30),
Recommendation varchar(30))
Error:-
------
---Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'Type varchar(30), Recommendation
varchar(30))' at line 14
Reason:-
----------
----->Here (Type) is the default keyword because of that we are getting the above error to resolve this
we should use the backtick(`) symbol.
create table if not exists dress(
`Dress_ID` varchar(30),
`Style` varchar(30),
`Price` varchar(30),
`Rating` varchar(30),
`Size` varchar(30),
`Season` varchar(30),
`NeckLine` varchar(30),
`SleeveLength` varchar(30),
`waiseline` varchar(30),
`Material` varchar(30),
`FabricType` varchar(30),
`Decoration` varchar(30),
`Pattern Type` varchar(30),
`Recommendation` varchar(30))
-------------------Syntax for How to load the data-------------------------------
EX:-1
LOAD DATA INFILE
'D:\load1\AttributeDataSet.csv'
into table dress
FIELDS terminated by ','
ENCLOSED by '"'
lines terminated by '\n'
IGNORE 1 ROWS;
Error:-
------
Error Code: 29. File 'D:\load1AttributeDataSet.csv' not found (OS errno 2 - No such file or directory)
Reason:-
------
---->Here I mentioned file name with different symbols(\)
Solution
--------:-
LOAD DATA INFILE
'D:\load1/AttributeDataSet.csv'
into table dress
FIELDS terminated by ','
ENCLOSED by '"'
lines terminated by '\n'
IGNORE 1 ROWS;
EX:-2
LOAD DATA INFILE
'D:\load3/AttributeDataSet.csv'
into table dress1
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS
Error:-
--------
Error Code: 29. File 'D:\load3\AttributeDataSet.csv' not found (OS errno 2 - No such file or directory)
Reason:-
------
----->Reason behind the above error is actually the file name like this 'D:\load
3\AttributeDataSet.csv',Here I missed the space.
LOAD DATA INFILE
'D:\load 3/AttributeDataSet.csv'
into table dress1
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS
EX:-3
create table if not exists dress(
Dress_ID varchar(30),
Style varchar(30),
Price varchar(30),
Rating varchar(30),
Size varchar(30),
Season varchar(30),
NeckLine varchar(30),
SleeveLength varchar(30),
waiseline varchar(30),
Material varchar(30),
FabricType varchar(30),
Decoration varchar(30),
Pattern varchar(30),
Type varchar(30),
Recommendation varchar(30))
Syntax:-
-----------
LOAD DATA INFILE
'E:\DATA/AttributeDataSet.csv'
into table dress
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS
Error:-
-------
----->While loading data from the excel file with the respective location but am not able to load data am getting the below error.
Error Code: 1261. Row 1 doesn't contain data for all columns
Solution:-
---------
----->Then I checked the excel sheet properly and then I observed the columns exist in the table and the columns exist in the excel sheet both are different,BCZ of that is facing the issue.
-------How to load 500000 data into table-------
EX:-4
create table retail(
`InvoiceNo` varchar(50),
`StockCode` varchar(50),
`Description` varchar(50),
`Quantity` varchar(50),
`InvoiceDate` varchar(50),
`UnitPrice` varchar(50),
`CustomerID` varchar(50),
`Country` varchar(50))
LOAD DATA INFILE
'D:\DATA/Online Retail (3).xlsx - Online Retail.csv'
into table retail
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS;
----If it is not allowed then we need to increase the size of max_allowed_packet-----
show variables like '%max_allowed_packet%';
select * from retail
select * from retail limit 5
select count(*) from retail;
EX:-5
CREATE TABLE sales (
order_id VARCHAR(15) NOT NULL,
order_date VARCHAR(15) NOT NULL,
ship_date VARCHAR(15) NOT NULL,
ship_mode VARCHAR(14) NOT NULL,
customer_name VARCHAR(22) NOT NULL,
segment VARCHAR(11) NOT NULL,
state VARCHAR(36) NOT NULL,
country VARCHAR(32) NOT NULL,
market VARCHAR(6) NOT NULL,
region VARCHAR(14) NOT NULL,
product_id VARCHAR(16) NOT NULL,
category VARCHAR(15) NOT NULL,
sub_category VARCHAR(11) NOT NULL,
product_name VARCHAR(127) NOT NULL,
sales DECIMAL(38, 0) NOT NULL,
quantity DECIMAL(38, 0) NOT NULL,
discount DECIMAL(38, 3) NOT NULL,
profit DECIMAL(38, 8) NOT NULL,
shipping_cost DECIMAL(38, 2) NOT NULL,
order_priority VARCHAR(8) NOT NULL,
`year` DECIMAL(38, 0) NOT NULL)
LOAD DATA INFILE
'D:/sales_data_final'
into table sales
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS;
Error:-
-----
Error Code: 29. File 'D:\sales_data_final' not found (OS errno 2 - No such file or directory)
Reason:-
-------
----->Here am not mentioned the file format type bcz of that am getting the above error.
LOAD DATA INFILE
'D:/sales_data_final.csv'
into table sales
FIELDS terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS;
Error:-
-----
Error Code: 1292. Incorrect date value: '1/1/2011' for column 'order_date' at row 1.
Reason:-
-------
------>We need to set sql_mode parameter.
select @@sql_mode;
------------------------
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
About sql_mode:-
-------------------
https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/
update sales
set order_date_new=str_to_date(order_date,'%m/%d/%y');
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench
update sales set order_date_new=str_to_date(order_date,'%m/%d/%y')
Error:-
-----
Error Code: 1292. Truncated incorrect date value: '1/1/2011'
Reason:-
-------
--------->We need to change the check and change the below parameter.
show variables like '%sql_mode%';
set session sql_mode=''
alter table sales
add column shift_date_new date after shift_date
Error:-
-----
Error Code: 1054. Unknown column 'shift_date' in 'sales'
Reason:-
------
---->Shift_date column doesn't exist in sales table.
Note:-
-----
----->In the converion of str_to_date(order_date,'%m/%d/%Y') %Y(Capital) should always capital letters otherwise(y) it will take current year(means it shows the data from recent years like 2022)
Comments
Post a Comment