Loading a non well formed CSV file

Tue, 01 Dec 2009 04:46:46 GMT

I have an extract of call records from my cellphone provider in the following format. How would I go about loading this data in a table, I would ideally like to have the Local/National or SMS repeated per applicable data line as a category. Any help would be appreciated.

Local/National
Date,Time,Category,Name,Units,Duration,Inv Cost,Base Cost
22/04/2008,15:42:53,,'5555555555',234.0,"00:03:54",0.00,5.81
25/04/2008,15:38:30,,'5555555555',79.0,"00:01:19",0.00,1.96
30/04/2008,02:19:13,,'5555555555',57.0,"00:00:57",0.00,0.83
09/05/2008,10:11:34,,'5555555555',281.0,"00:04:41",0.00,6.98
09/05/2008,10:29:09,,'5555555555',27.0,"00:00:27",0.00,0.67
09/05/2008,11:23:59,,'5555555555',259.0,"00:04:19",0.00,6.43
09/05/2008,12:13:10,,'5555555555',11.0,"00:00:11",0.00,0.27
09/05/2008,12:19:58,,'5555555555',107.0,"00:01:47",0.00,2.66
10/05/2008,11:10:16,,'5555555555',107.0,"00:01:47",0.00,1.55
,,,Subtotals: ,1162.0,0:19:22,0.00,27.16

SMS
Date,Time,Category,Name,Units,Duration,Inv Cost,Base Cost
21/04/2008,07:11:13,,'5555555555',.0,"00:00:00",0.66,0.66
22/04/2008,16:28:05,,'5555555555',.0,"00:00:00",0.66,0.66
27/04/2008,21:49:58,,'5555555555',.0,"00:00:00",0.66,0.66
09/05/2008,12:19:21,,'5555555555',.0,"00:00:00",0.66,0.66
09/05/2008,16:30:45,,'5555555555',.0,"00:00:00",0.66,0.66
09/05/2008,18:36:46,,'5555555555',.0,"00:00:00",0.66,0.66
,,,Subtotals: ,0.0,0:00:00,3.96,3.96

Loading a non well formed CSV file

prieper — Tue, 01 Dec 2009 18:03:00 GMT

Give it a try with a script like the below

xPhoneBill:
 LOAD
 IF(Mode = 'AS ABOVE',
 PEEK('Mode'), Mode) AS Mode,
 SUBFIELD(Line, ',', 1) AS Date,
 SUBFIELD(Line, ',', 2) AS Time,
 SUBFIELD(Line, ',', 3) AS Category,
 REPLACE(SUBFIELD(Line, ',', 4), CHR(39), '') AS Name,
 SUBFIELD(Line, ',', 5) AS Units,
 REPLACE(SUBFIELD(Line, ',', 6), '"', '') AS Duration,
 SUBFIELD(Line, ',', 7) AS InvCost,
 SUBFIELD(Line, ',', 😎 AS BaseCost
 WHERE
 NOT WILDMATCH(Line, '*SUBTOTAL*', 'DATE*', '');
 LOAD
 IF(WILDMATCH(Line, 'Local*', 'SMS*'),
 Line, 'AS ABOVE') AS Mode,
 Line;
 LOAD
 @1:n AS Line
 FROM
 [.\PhoneBill.txt] (ansi, fix, embedded labels, no labels, record is line);

PhoneBill:
 NOCONCATENATE LOAD * RESIDENT xPhoneBill WHERE LEN(Time) > 0;
 DROP TABLE PhoneBill;

Have not managed in the same loading to eliminate the blank rows (i.e. where Time is not shown), therefore the changing of the tables.

HTH
Peter

topic Loading a non well formed CSV file in QlikView

Loading a non well formed CSV file

Loading a non well formed CSV file