Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Zurich on Sept 24th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
avantime
Creator II
Creator II

Macro to concatenate multiple charts into one CSV

Hi,

I want to concatenate three charts into one csv file using macro. All the charts have the same headers and number of columns.

Unfortunately I cannot combine them into one chart in Qlikview, all of them being Pareto charts using column sorting to get the final result.

So what I have now looks like:

Header 1Header 2
A1
B2

Header 1Header 2
C3
D4

Header 1Header 2
E5
F6

The output should look like:

Header1;Header2

A;1

B;2

C;3

D;4

E;5

F;6

Thank you!

1 Solution

Accepted Solutions
avantime
Creator II
Creator II
Author

Managed to figure it by myself after all... Post approval by the moderators takes a lot of time..

Maybe someone else needs this so here was my approach:

- Found this post: VB Macro to Export Table to CSV with No headers !

- Created a script that exported each chart to a separate CSV;

- First CSV created is left unmodified, the rest have their headers deleted in order to concatenate them to the first CSV.

Here is the script:

Sub ExportChart1

set sObject = ActiveDocument.GetSheetObject("CH01")

sObject.Export "E:\Data1.csv", ";"

ExportChart2

End Sub

Sub ExportChart2

set sObject = ActiveDocument.GetSheetObject("CH02")

sObject.Export "E:\Data2.csv", ";"

ExportChart3

End Sub

Sub ExportChart3

set sObject = ActiveDocument.GetSheetObject("CH03")

sObject.Export "E:\Data3.csv", ";"

Dim obj

Set obj = CreateObject("WScript.Shell")

obj.Run "E:\DeleteFirstLine.bat", , TRUE

Combinefiles

End Sub

Sub Combinefiles

Const ForReading = 1

Const ForWriting = 2

Const ForAppending = 8

' define csv files that will be read

Dim arrCSVFiles : arrCSVFiles = Array("E:\Data1.csv", _

                                      "E:\Data2.csv", _

                                      "E:\Data3.csv")

' define combined csv file

Dim outCSVFile : outCSVFile = "E:\Export.csv"

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

' open output csv file

Dim objOutFile : Set objOutFile = objFSO.OpenTextFile(outCSVFile, ForWriting, True)

Dim csvFile, objFile, strTemp

' loop through the list of csv files to read

For Each csvFile In arrCSVFiles

    ' open file for reading

    Set objFile = objFSO.OpenTextFile(csvFile, ForReading)

    Do Until objFile.AtEndOfStream

        strTemp = objFile.ReadLine

        If Not strTemp = "" Then

            ' write line to combined csv file

            objOutFile.WriteLine strTemp

        End If

    Loop

    ' close current opened csv file

    objFile.Close

Next

' close combined csv file

objOutFile.Close

End Sub

Regards,

Catalin

View solution in original post

1 Reply
avantime
Creator II
Creator II
Author

Managed to figure it by myself after all... Post approval by the moderators takes a lot of time..

Maybe someone else needs this so here was my approach:

- Found this post: VB Macro to Export Table to CSV with No headers !

- Created a script that exported each chart to a separate CSV;

- First CSV created is left unmodified, the rest have their headers deleted in order to concatenate them to the first CSV.

Here is the script:

Sub ExportChart1

set sObject = ActiveDocument.GetSheetObject("CH01")

sObject.Export "E:\Data1.csv", ";"

ExportChart2

End Sub

Sub ExportChart2

set sObject = ActiveDocument.GetSheetObject("CH02")

sObject.Export "E:\Data2.csv", ";"

ExportChart3

End Sub

Sub ExportChart3

set sObject = ActiveDocument.GetSheetObject("CH03")

sObject.Export "E:\Data3.csv", ";"

Dim obj

Set obj = CreateObject("WScript.Shell")

obj.Run "E:\DeleteFirstLine.bat", , TRUE

Combinefiles

End Sub

Sub Combinefiles

Const ForReading = 1

Const ForWriting = 2

Const ForAppending = 8

' define csv files that will be read

Dim arrCSVFiles : arrCSVFiles = Array("E:\Data1.csv", _

                                      "E:\Data2.csv", _

                                      "E:\Data3.csv")

' define combined csv file

Dim outCSVFile : outCSVFile = "E:\Export.csv"

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

' open output csv file

Dim objOutFile : Set objOutFile = objFSO.OpenTextFile(outCSVFile, ForWriting, True)

Dim csvFile, objFile, strTemp

' loop through the list of csv files to read

For Each csvFile In arrCSVFiles

    ' open file for reading

    Set objFile = objFSO.OpenTextFile(csvFile, ForReading)

    Do Until objFile.AtEndOfStream

        strTemp = objFile.ReadLine

        If Not strTemp = "" Then

            ' write line to combined csv file

            objOutFile.WriteLine strTemp

        End If

    Loop

    ' close current opened csv file

    objFile.Close

Next

' close combined csv file

objOutFile.Close

End Sub

Regards,

Catalin