topic Re: Removing headers from csv in QlikView

Removing headers from csv

markp201 — Tue, 25 Sep 2018 11:51:51 GMT

I've seen a few various ideas but we required one which removed the headers from a table in the load script.

Normally, this would be an ETL process but Qlikview sufficed

We loaded the table, saved to a csv and called the macro

Table:

SELECT

FROM

table

;

STORE Table INTO [...].csv (txt, delimiter is '|');

DROP TABLE Table;

LET vx = RemoveHeaders();

Below is the macro.

Function RemoveHeaders()

SET objFSO = CreateObject("Scripting.FileSystemObject")

text = ""

Lines = 0

SET objCSVFile = objFSO.OpenTextFile("C:\users\....csv",1)

objCSVFile.ReadLine

DO WHILE NOT objCSVFile.AtEndOfStream

text = text & objCSVFile.ReadLine & vbCrLf

Lines = Lines + 1

LOOP

objCSVFile.Close

SET objResultFile = objFSO.CreateTextFile("c:\users\...csv",true)

objResultFile.WriteLine "Report name,Date (need function)"

objResultFile.Write text

objResultFile.Write Lines

objResultFile.Close

End Function

Nice feature is this will work for any number of columns.

Re: Removing headers from csv

markp201 — Fri, 28 Sep 2018 12:14:28 GMT

Qlikview 11 let us down. Using macros on the server requires the IE Plugin.

We're investigating a windows scheduled task to open the document, reload and run the macro.

Re: Removing headers from csv

miikkaqlick — Fri, 28 Sep 2018 12:33:39 GMT

Hi!

I had almost same problem but with xml-files. Here's part of my script:

EXECUTE cmd.exe /c more +1 "$(vStore)test.xml" > "$(vStore)$(vCopiedFile)";

I have loop that checks one folder and removes some lines from all files that haven't been handled.

More command with +1 drops one line from file start away. You could make this part of some transform layer and end application would load only modified files.

You have to allow execute statement for file.

Br,

Miikka

Climber Finland