topic Import a txt file in difficult format in QlikView

Import a txt file in difficult format

Mon, 04 Oct 2010 10:38:59 GMT

Hi,

I have an Oracle report that we run daily that I want to load into Qlikview.

The format of this file makes it rather difficult to import. I have attached an example of the Oracle file and the resulting table I'm looking for.

The data in the resulting table need to extracted from three lines in the original file (line 1, 9, 10, 11 and 12).
Line 1 is used in both lines in the resulting table
Line 9 and 10 is used in the first line in the resulting table
Line 10 and 11 is used in the second line in the resulting table

It's not an option to simplify the format before exporting it from Oracle.

How do I write the code for this?

Best Regards,
Jonas

Import a txt file in difficult format

nathanfurby — Mon, 04 Oct 2010 14:26:20 GMT


jonahed79 wrote:The format of this file makes it rather difficult to import

No kidding

I have never had to worry about something like this so thought I'd give it a go out of interest. I used the Transformation Steps in the Load wizard (again, something I've never had to do) and managed to produce the output you required. See the attached qvw.

But the solution is unworkable in real world as the only way it can make sense of the file is by using 'space' as the delimiter. This means that the load would just fall over unless all the fields (Vendor, Item, Buyer etc.) were strictly enforced to have no spaces or a set number of spaces in the text.

I am no expert when it comes to this kind of load but I imagine that handling this kind of file it just not practical. If it was me then I would output the Oracle query/report to a csv file or, better yet, get the SQL code used for the query and run it directly via ODBC.

Import a txt file in difficult format

prieper — Mon, 04 Oct 2010 15:20:14 GMT

Good day Jonas,

you may give it a try with a code similar to this:

Report:
 LOAD
 *
 WHERE
 WILDMATCH(Data, '??-???-*'); // only rows containing Date
 LOAD
 IF(ODD(LineNo), PREVIOUS(ReportRow) & ReportRow) AS Data; // concatenate 2 lines
 LOAD
 @1:n AS ReportRow,
 RecNo() AS LineNo
 FROM
 [.\Report.txt]
 (fix, codepage is 1252);

Vendor:
 LOAD
 MID(@1:n, 8, 69) AS Vendor
 FROM
 [.\Report.txt]
 (fix, codepage is 1252)
 WHERE
 WILDMATCH(@1:n, 'Vendor:*');

This should result in a table containing only one line per Purchase-Order in fixed format, which need to be cutted into fields (and fieldnames) etc. The other table may contain the vendor (or other site-information as in the header of the report) and can be added with JOIN to the datatable.

HTH
Peter

Import a txt file in difficult format

Mon, 04 Oct 2010 15:27:25 GMT

Hi Nathan,

Thanks for your reply! I just found an alternate solution so I haven't had a chance to test yours. I used the 'peek' function to get the correct result.

I'll try yours soon to see if it's simpler.

Best Regards,
Jonas

---

tmpPastDue:
LOAD if(@6:12='Vendor:',@14:79,peek('tmpVendor')) as tmpVendor,
if(@3:3='-',makedate('20' & num#(right(@1:9,2)),num#(applymap('Months_cap',mid(@1:9,4,3))), if(mid(@1:9,2,1)='-',num#(left(@1:9,1)),num#(left(@1:9,2)))),peek('tmpDueDate')) as tmpDueDate,
if(@11:34 like '*,*',@11:34,peek('tmpBuyer')) as tmpBuyer,
@11:32 as tmpItem
FROM Pastdue.txt (fix, codepage is 1252);

PastDue:
LOAD tmpVendor as [Past Due Vendor],
tmpDueDate as [Past Due Due Date],
tmpBuyer as [Past Due Buyer],
tmpItem as [Past Due Item Number]
RESIDENT tmpPastDue
WHERE isnum(mid(tmpItem,7,2));

drop table tmpPastDue;

Import a txt file in difficult format

nathanfurby — Mon, 04 Oct 2010 15:36:54 GMT

Hi Jonas - I doubt my solution will be any better - not very robust as I mentioned.

Can you attach the text file you used with the example you posted. I tried it with the first file you attached and it didn't work. Thanks

Import a txt file in difficult format

Mon, 04 Oct 2010 16:02:31 GMT

Sorry about that Nathan,

I simplified the text file a bit last time I sent it over. I've attached a new text file and a *.qvw file so you can see the solution.

Best Regards,
Jonas

Import a txt file in difficult format

nathanfurby — Mon, 04 Oct 2010 16:57:03 GMT

Interesting use of the fixed record file type (I've never looked at this before) and the peek function. A few presumptions about the data that you'd have to keep an eye on - but necessary I suppose when you are dealing with such a messy data source

Many thanks Jonas.