topic How to combine columns data file into full data file automatically and import it to qlikview? in QlikView

How to combine columns data file into full data file automatically and import it to qlikview?

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i am practicing to build different dashboards and i came across this data Office of Defects Investigation (ODI), Flat File Downloads | Safercar.gov | NHTSA

and the columns are in one text file and data is in another file, delimiter specified is 'tab'. so, i copied the columns from text file into note pad and gave tab delimiter to them and copied into the file where data exits and then used to import to qlikview and build data models.

But, my question is if i have like 50 tables and so. then should i copy the data like that manually for all the tables or is there any automatic way which does the job? are there any options in qlikview or something? can some one please help?

Regards,

Veer

Re: How to combine columns data file into full data file automatically and import it to qlikview?

Peter_Cammaert — Mon, 27 Feb 2017 11:13:40 GMT

Splitting the headers off from the data and then hiding them in a descriptive text file without fixed formatting is a highly irregular way of formatting a data source, and very software unfriendly at that too.

AFAIK QlikView wizards don't have builtin functionality to handle this kind of formatting, but you may be able to create a script to do it for you. It all starts with these steps:

Finding combinations of data files and descriptions. Like a FLAT_INV.txt (data) and INV.txt (description). The common part is INV. Put that identifier in a variable (for example vTableName)
First read the description file, and try to lift the series of column specifications from it. You may want to look for a line that starts with exactly 6 hyphen characters. Then you read rows as long as you encounter digits or whitespace at the start of every line.
When you have a series of column headers, create a text representation of the column aliassing instructions to be used in a LOAD statement and put the representation in a variable. For example, vColumnSpecs
Then perform this statement:

[$(vTableName)]:
LOAD $(vColumnSpecs) FROM [path\FLAT_$(vTableName).txt] (options);

and repeat this for all files in a specific subdirectory identified by path.

Just some ideas to help you get started.

Best,

Peter

Re: How to combine columns data file into full data file automatically and import it to qlikview?

adamdavi3s — Mon, 27 Feb 2017 11:44:40 GMT

Looking at the files it could be tricky as the header files have differing header lines etc...

I'm going to take a look at writing this script though as I like a challenge

Re: How to combine columns data file into full data file automatically and import it to qlikview?

adamdavi3s — Mon, 27 Feb 2017 13:36:37 GMT

That was fun, note that this is no means infallible and I only tested it with two of the files on the link you provided, but it should give you a good starting point (I hope)


//loop through and load our meta files
//assumes they are txt files without an _ in the name
//note have used fixed file import which isn't totally accurate
//but seems to be the closest we can get

FOR Each File in filelist ('*.txt');


IF wildmatch('$(File)','*_*') =0 THEN


Files: 
LOAD 
  replace(subfield('$(File)','\',SubStringCount('$(File)','\')+1),'.txt','') as filename,
  [@1:6], 
     [@7:30], 
     [@31:88], 
     [@89:n]
FROM
[$(File)](fix, codepage is 1252, no labels);


END IF


NEXT File
//purge anything which isn't a valid column


Columns:
LOAD filename,
     [@7:30] as columnno, 
     subfield([@31:88],'   ',1) as columndesc
RESIDENT Files
WHERE isnum([@7:30]);


DROP TABLE Files; //drop temp table






LET v_number_files = FieldValueCount('filename'); //find how many files to load


FOR f=1 to $(v_number_files) //start a loop for the files


  LET v_filename = FieldValue('filename',$(f)); //find the corresponding filename
  QUALIFY *; //qualify as we don't want to have issues with the same field names

  '$(v_filename)':
  LOAD *
  FROM
  [FLAT_$(v_filename).txt]
  (txt, codepage is 1252, no labels, delimiter is '\t', msq);

  UNQUALIFY *; //unqualify


  //load our column names
  tablecolumns:
  LOAD DISTINCT columnno as tblcolumno,columndesc as tblcolumndesc
  RESIDENT Columns
  WHERE filename='$(v_filename)';

  LET v_number_columns = FieldValueCount('tblcolumno'); //how many fields are there in the file

  FOR i=1 to  $(v_number_columns)  //for each of these figure out our rename
  LET v_fieldno = $(i);
  LET v_fielddesc = FieldValue('columndesc',$(i));


  rename field '$(v_filename).@$(v_fieldno)' to '$(v_filename).$(v_fielddesc)';
  NEXT i; //next field

  drop table tablecolumns;



NEXT f; //next file








Drop table Columns;