LOAD DATA INFILE '/path-to/all.csv'
INTO TABLE tmp_calendar
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
修正
UPDATE tmp_calendar SET lunarMonth=REPLACE(lunarMonth, '閏', '闰');
UPDATE tmp_calendar SET solarTerm=REPLACE(solarTerm, '驚蟄', '惊蛰');
UPDATE tmp_calendar SET solarTerm=REPLACE(solarTerm, '穀雨', '谷雨');
UPDATE tmp_calendar SET solarTerm=REPLACE(solarTerm, '小滿', '小满');
UPDATE tmp_calendar SET solarTerm=REPLACE(solarTerm, '芒種', '芒种');
UPDATE tmp_calendar SET solarTerm=REPLACE(solarTerm, '處暑', '处暑');
UPDATE tmp_calendar SET zodiac=REPLACE(zodiac, '龍', '龙');
UPDATE tmp_calendar SET zodiac=REPLACE(zodiac, '馬', '马');
UPDATE tmp_calendar SET zodiac=REPLACE(zodiac, '雞', '鸡');
UPDATE tmp_calendar SET zodiac=REPLACE(zodiac, '豬', '猪');
UPDATE tmp_calendar SET zodiac=REPLACE(zodiac, '犬', '狗');
UPDATE tmp_calendar SET zodiac='鸡' WHERE zodiac='' AND lunarYear LIKE '%酉';
重新导出
SELECT 'day','gregorianYear','gregorianMonth','gregorianDate','lunarYear','lunarMonth','lunarDate','lunarLeapMonth','solarTerm','zodiac' FROM DUAL
UNION
SELECT day,gregorianYear,gregorianMonth,gregorianDate,lunarYear,lunarMonth,lunarDate,lunarLeapMonth,solarTerm,zodiac FROM tmp_calendar
INTO OUTFILE '/path-to/new.csv'
FIELDS
TERMINATED BY ','
LINES
TERMINATED BY '\n';
要满足真实场景使用的话,有小部分数据是需要修正的(如繁简转换,属相不准确等)。我自己通过数据库方式做了修正,语句如下:
CREATE TABLE
tmp_calendar(
dayvarchar(255) DEFAULT NULL,
gregorianYearvarchar(255) DEFAULT NULL,
gregorianMonthvarchar(255) DEFAULT NULL,
gregorianDatevarchar(255) DEFAULT NULL,
lunarYearvarchar(255) DEFAULT NULL,
lunarMonthvarchar(255) DEFAULT NULL,
lunarDatevarchar(255) DEFAULT NULL,
lunarLeapMonthvarchar(255) DEFAULT NULL,
solarTermvarchar(255) DEFAULT NULL,
zodiac` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;导入
LOAD DATA INFILE '/path-to/all.csv' INTO TABLE
tmp_calendar
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';修正
UPDATE
tmp_calendar
SET lunarMonth=REPLACE(lunarMonth, '閏', '闰'); UPDATEtmp_calendar
SET solarTerm=REPLACE(solarTerm, '驚蟄', '惊蛰'); UPDATEtmp_calendar
SET solarTerm=REPLACE(solarTerm, '穀雨', '谷雨'); UPDATEtmp_calendar
SET solarTerm=REPLACE(solarTerm, '小滿', '小满'); UPDATEtmp_calendar
SET solarTerm=REPLACE(solarTerm, '芒種', '芒种'); UPDATEtmp_calendar
SET solarTerm=REPLACE(solarTerm, '處暑', '处暑'); UPDATEtmp_calendar
SET zodiac=REPLACE(zodiac, '龍', '龙'); UPDATEtmp_calendar
SET zodiac=REPLACE(zodiac, '馬', '马'); UPDATEtmp_calendar
SET zodiac=REPLACE(zodiac, '雞', '鸡'); UPDATEtmp_calendar
SET zodiac=REPLACE(zodiac, '豬', '猪'); UPDATEtmp_calendar
SET zodiac=REPLACE(zodiac, '犬', '狗'); UPDATEtmp_calendar
SET zodiac='鸡' WHERE zodiac='' AND lunarYear LIKE '%酉';重新导出
SELECT 'day','gregorianYear','gregorianMonth','gregorianDate','lunarYear','lunarMonth','lunarDate','lunarLeapMonth','solarTerm','zodiac' FROM DUAL UNION SELECT day,gregorianYear,gregorianMonth,gregorianDate,lunarYear,lunarMonth,lunarDate,lunarLeapMonth,solarTerm,zodiac FROM
tmp_calendar
INTO OUTFILE '/path-to/new.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';DROP TABLE
tmp_calendar
; `