Import from Excel to MySQL [closed]-CodePudding

I am using "MySQL Query Browser". I want to know if I can import a column from Excel table to exists table. I know have the command LOAD DATA, but I don't know the syntax. Can anyone help me please?

CodePudding user response：

Follow below steps:

Your csv should look like:

adsh;tag;version;coreg;ddate;qtrs;uom;value
0001640334-21-000798;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20210131;0;USD;10010.0000
0001640334-21-000798;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20201031;0;USD;10913.0000
0001477932-21-002126;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20201231;0;USD;2372072.0000
0001477932-21-002126;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20191231;0;USD;1018145.0000
0001640334-21-000810;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20210228;0;USD;2974.0000
0001640334-21-000810;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20200531;0;USD;4225.0000
0001477932-21-002140;AccountsPayableAndAccruedLiabilitiesCurrent;us-gaap/2019;;20191231;0;USD;1039000.0000

And the load data command:

LOAD DATA INFILE '/var/lib/mysql/num.csv' 
INTO TABLE num 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(adsh,tag,version,coreg,ddate,qtrs,uom,value);

For detailed explanation you can check: how to import csv file which contain large set of data and have a column which has some null values

CodePudding user response：

Either use my SQL work benach to use UI.

Or Change the xls to CSV and then try with the command like below,

load data local infile "csv file name" into table <table name> fields
terminated by ',' 
lines terminated by '\n'
*(column names here by comma separated)*;

Reference URL