Mysql – import into thesql data from report exported by infobip

loadMySQL

I want to import this csv sample data:

��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;

This is an example from a report exported by http://www.infobip.com/.
Why infobip calls this a csv I don't know.

I am having problems with the following 90,0000 currency values.

How can I convert this to a FLOAD / DECIMAL as part of the import.

Best Answer

In order to solve this I ran the following to pre-process file to remove problem chars, ��, = as well as remove all null bytes.

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

I created the following table in mysql database:

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;

I used the following to load the data into the database:

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