[aesteban@localhost BizEquityData]$ head -1 bizDataFile.tsv | tr '\t' '\n'
id
name
dbaname
address
city
state
postcode
latitude
longitude
phone
faxphone
contact
firstname
lastname
yearestablished
businessstatus
webaddress
corpemployees
localemployees
corpamount
localamount
localamount
stockexchangecode
stocktickersymbol
ceoname
cioname
cfoname
name
code
1a..- find out total lines in file:
[aesteban@localhost BizEquityData]$ cat bizDataFile.tsv | wc -l
1785338
2.- Create a table, rename any duplicate field names:
CREATE TABLE `bizDataTable` (
id varchar(255),
name varchar(255),
dbaname varchar(255),
address varchar(255),
city varchar(255),
state varchar(255),
postcode varchar(255),
latitude varchar(255),
longitude varchar(255),
phone varchar(255),
faxphone varchar(255),
contact varchar(255),
firstname varchar(255),
lastname varchar(255),
email varchar(255),
yearestablished varchar(255),
businessstatus varchar(255),
webaddress varchar(255),
corpemployees varchar(255),
localemployees varchar(255),
corpamount varchar(255),
localamount varchar(255),
localamount2 varchar(255),
stockexchangecode varchar(255),
stocktickersymbol varchar(255),
ceoname varchar(255),
cioname varchar(255),
cfoname varchar(255),
name2 varchar(255),
code varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.- Connect to the server and load the local file:
[aesteban@localhost BizEquityData]$ mysql -u USERNAME -h dev.example.com -p --local-infile bizDataDB
MySQL [bizDataDB]> load data local infile '/home/aesteban/Documents/BizEquityData/bizDataFile.tsv' into table bizDataTable fields terminated by '\t' lines terminated by '\n' ignore 1 lines;
MySQL [bizData]> select count(*) from bizDataTable;
+----------+
| count(*) |
+----------+
| 1785337 |
+----------+
1 row in set (0.00 sec)
Enjoy!!
BONUS: Dump select statement locally as a TSV file:
[aesteban@localhost FilteredReports]$ mysql -u USERNAME -h dev.example.com -p bizDataDB -e "select * from bizDataTable where email != 'NULL'" > bizData_email.tsv
If it's a CSV file, some fields may contain a comma, try something like the following:
MySQL [jupiter]> load data local infile 'e360Data.csv' into table e360MasterOnline fields optionally enclosed by '"' terminated by ',' lines terminated by '\n' ignore 1 lines;
[ view entry ] ( 1508 views ) | print article
<<First <Back | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | Next> Last>>