我需要从infobip导出的报表中导入mysql数据。

Question

我想导入这个csv示例数据：

��SendDateTime;ExternalMessageId;SMSCount;Account Name;Country;Country Prefix;Network;Destination Address;Sender;Price Per Message;Credits Per Message;Parent AccountId;Parent Credits Per Message;Client Metadata;Status;Reason;MessageText;DR Arrival Time
21.2.2016. 0:01:17;="126022022011749133";1;some-account;some coutry;27;somenetwork;="27123456789";="27123456789";90,0000;90,0000;;;="";Delivered;;Confirmation: some sms text value;21.2.2016. 0:01:19;

这是http://www.infobip.com/导出的报表中的一个示例。为什么infobip称这为csv我不知道。

下面的90,0000货币值有问题。

如何将其转换为FLOAD / DECIMAL作为导入的一部分。

Answer 1

为了解决这个问题，我运行了以下的预处理文件来删除问题字符、��、=以及删除所有空字节。

tail -n+2 report.csv | tr -d '\000' | tr -d "=" > ./report.fix.csv

我在mysql数据库中创建了以下表：

CREATE TABLE `2016_feb_msg` (
  `SendDateTime` datetime DEFAULT '2016-01-31 23:59:00',
  `ExternalMessageId` varchar(20) DEFAULT NULL,
  `SMSCount` int(11) DEFAULT NULL,
  `AccountName` varchar(65) DEFAULT NULL,
  `Country` varchar(65) DEFAULT 'South Africa',
  `CountryPrefix` varchar(45) DEFAULT NULL,
  `Network` varchar(60) DEFAULT NULL,
  `DestinationAddress` varchar(45) DEFAULT NULL,
  `Sender` varchar(45) DEFAULT NULL,
  `PricePerMessage` DECIMAL(10,4) DEFAULT '0',
  `CreditsPerMessage` DECIMAL(10,4) DEFAULT '0',
  `ParentAccountId` varchar(45) DEFAULT NULL,
  `ParentCreditsPerMessage` varchar(9) DEFAULT '0',
  `ClientMetadata` varchar(25) DEFAULT NULL,
  `Status` varchar(45) DEFAULT NULL,
  `Reason` varchar(45) DEFAULT NULL,
  `MessageText` varchar(900) DEFAULT NULL,
  `DRArrivalTime` datetime DEFAULT '2016-01-31 23:59:00',
  `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=1277493 DEFAULT CHARSET=utf16;

我使用以下方法将数据加载到数据库中：

LOAD DATA INFILE '/tmp/report.fix.csv'
INTO TABLE infobip_reports.2016_feb_msg
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@SendDateTime
,ExternalMessageId
,SMSCount
,AccountName
,Country
,CountryPrefix
,Network
,DestinationAddress
,Sender
,@PricePerMessage
,@CreditsPerMessage
,ParentAccountId
,ParentCreditsPerMessage
,ClientMetadata
,Status
,Reason
,MessageText
,@DRArrivalTime
,idx)
SET SendDateTime = STR_TO_DATE(@SendDateTime, '%e.%c.%Y. %k:%i:%S'),
DRArrivalTime = STR_TO_DATE(@DRArrivalTime, '%e.%c.%Y. %k:%i:%S'),
PricePerMessage = CONVERT(REPLACE(@PricePerMessage,",","."),DECIMAL(10,4)),
CreditsPerMessage = CONVERT(REPLACE(@CreditsPerMessage,",","."),DECIMAL(10,4)),
idx = null
; -- format this date-time variable