topic Import files containing multiple tables in QlikView

Import files containing multiple tables

Thu, 05 Apr 2012 12:21:46 GMT

Hi,I'm quite new to QV and have been assigned a task where I must import multiple tables residing in one file (it's large tab-sep. textfile often containing several hundred thousand lines). Every table starts with a new line containing the table name.

I have searched the forum and the help-section but haven't been able to figure out how to do this.

A little help please?

Import files containing multiple tables

Jason_Michaelides — Thu, 05 Apr 2012 12:27:22 GMT

I'd probably look for an external ltext-file-splitting program first. If you get no joy there then maybe something combining RecNo() to count records loaded and Peek() to get the last one, with While...Field1<>NewTableName or something...

Import files containing multiple tables

Thu, 05 Apr 2012 13:25:32 GMT

Hi,

you can use the option Table Files

Select your file and configure how do you want

If you can not extract the data, could provide an example of the format of your file with little information so we can better evaluate.

Import files containing multiple tables

rwunderlich — Thu, 05 Apr 2012 17:53:26 GMT

What's the pattern that identifies a new table? And does each table have the same or differenent fieldlist? Cna you provide a small example?

-Rob

Import files containing multiple tables

Thu, 05 Apr 2012 18:16:24 GMT

Each table is preceded by a line with pattern "newRecord\t". Each table is different. Different number of fields. What I would like is soemthing like below (written in c#). Can this be done? string tableSplit = "newRecord\t" string[] tableNames = { "transactions", "items", etc } foreach (string table in tableNames) { bool[] doImport = { false, true } var reader = new streamReader(file) int i = 0 for (String line = reader.ReadLine(); line != null && doImport[0] && !doImport[1]; line = reader.ReadLine()) { if (doImport[0] && doImport[1]) ImportTableData(line) // here is where data is loaded.. if (line.startsWith(tableSplit)) { doImport = line.startsWith(tableSplit+tableName) if (doImport) i++ } } }

Import files containing multiple tables

rwunderlich — Thu, 05 Apr 2012 18:24:52 GMT

Can you post a few sample rows of the input data?

Re: Import files containing multiple tables

Thu, 05 Apr 2012 18:44:24 GMT

Yes, of course! Here is a small sample file.

Re: Import files containing multiple tables

rwunderlich — Sun, 08 Apr 2012 22:45:16 GMT

This was a pretty interesting and challenging problem. I found a few ways to tackle the problem and learned about some qlikview quirks along the way.

Your eventual solution may depend on how you want to load the fields of each subfile. Attached in the most straightforward solution I found.

-Rob

http://robwunderlich.com

Re: Import files containing multiple tables

Tue, 10 Apr 2012 07:16:51 GMT

Thanks, very much appreciated! Glad that I could give you a little challenge

You really gave my work a needed boost here because I didn't really know how to approach the problem. I will go with this solution and see how it turns out when I test it on production files later today.

Again, thanks!

-Mikael