topic Macro Export to CSV issue in QlikView

Macro Export to CSV issue

vinesh_panchal — Wed, 22 Sep 2021 11:05:09 GMT

Hi,

I've got a button that I want to export a table to CSV, I've got it but the issue is that it puts speech marks around any lines with the names with apostrophes. The code I have is:

set obj = ActiveDocument.GetSheetObject("CH17")

Obj.Export vUserPath &"\Latest - HR Import Users - " & vToday & ".csv", ", "

vUserPath is the filepath and vToday is the date.

So someone with the name of O'Leary will appear as "O'Leary" within the exported CSV

Help please?

Thanks

Re: Macro Export to CSV issue

marcus_sommer — Thu, 23 Sep 2021 07:40:59 GMT

AFAIK it's not possible because it's a not customizable feature from the underlying windows library. An alternatively would be not to export the object else to write the content like you want. See here how it could be done:

Solved: Export to CSV with quotation marks - Qlik Community - 1138389

- Marcus

Re: Macro Export to CSV issue

vinesh_panchal — Thu, 23 Sep 2021 08:51:59 GMT

Thanks Marcus, I did try the export function in the sense of the code opening up excel and pasting the table then saving it as a CSV file, but for some reason it appears as an excel file although the details state its a CSV, then when i go to open the file it states there could be an error as the file does not match the format. - this is what I've tried:

Set XLSheets = XLDoc.Worksheets("Master")
ActiveDocument.GetSheetObject("CH17").CopyTableToClipBoard TRUE
XLDoc.Sheets("Master").Activate
XLSheets.Paste XLSheets.Range("A1")

XLDoc.SaveAs vUserPath &"\Latest - HR Import Users - " & vToday & ".csv"
oApp.Application.Quit
SET XLDoc = NOTHING
SET oApp = NOTHING

Thanks

Re: Macro Export to CSV issue

marcus_sommer — Thu, 23 Sep 2021 09:00:58 GMT

Looks like you opened an Excel and pasted the content there - the result will be an Excel file unless you also changes the file-format before saving the file. I think a quick google research will give the hints how it might be done.

But this wasn't meant as I referred to write the content else this part from the link:

sub CSVEportWithQuotes

set table = ActiveDocument.GetSheetObject( "TB14" )

for RowIter = 0 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

if ColIter < table.GetColumnCount-1 then vDelimiter = "," else: vDelimiter = ""

vContent = vContent & """" & cell.Text & """" & vDelimiter

vContent = vContent & chr(10)

msgbox vContent

set fso = CreateObject("Scripting.FileSystemObject")

set File = fso.OpenTextFile("\\YourServer\d\Test.csv", 2, true)

File.Writeline vContent

File.Close

end sub

- Marcus

Re: Macro Export to CSV issue

vinesh_panchal — Fri, 24 Sep 2021 09:15:52 GMT

Thank you so much for the help, I've tried it with a chart (straight table), and its taking a long time and then QlikView freezes - i've even tried doing the file to the C drive but it just freezes and keeps going in circles.

Re: Macro Export to CSV issue

marcus_sommer — Fri, 24 Sep 2021 09:38:06 GMT

Put some more msgbox into the script to see until the code runs like expected. Your description sounds a bit as if the loop didn't end. Therefore tracing the loop-variables with the msgbox should show this. Further helpful may be to comment the origin for-calls with something:

for RowIter = 0 to 1
...

just to check that's in general worked.

- Marcus