Speeding up export to CSV

Wed, 01 Oct 2014 12:19:02 GMT

I have a macro that picks data from certain columns and writes that data into a CSV file, but its really really slow.

Taking nearly 1.5 hours to create a CSV with 64000 records.

I think the reason it is so slow is because I am using a for loop, but I don't know any other way, is there anyone who knows of a faster more efficient way of doing this?

set tb = ActiveDocument.GetSheetObject("TB01")

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks. Add

set objExcelSheet = XLDoc.Worksheets(1)

set campaignNotes1 = ActiveDocument.GetVariable("VarA")

set tb = ActiveDocument.GetSheetObject("TB01")

tb.AddField("ExternalId")

columnCount = tb.GetColumnCount

rowCount = tb.GetRowCount - 1

for currentRow = 1 to rowCount

'Does this customer have TPS?

tps = tb.GetCell(currentRow,17).Text

if tps = "Y" then

tpsDataIncluded = true

'msgbox ("Exported Cancelled - The data selected includes phone numbers that are TPS")

'exit For

end if

leadId1 = tb.GetCell(currentRow,1).Text

externalId = tb.GetCell(currentRow,columnCount-1).Text

businessName = tb.GetCell(currentRow,0).Text

title = tb.GetCell(currentRow,3).Text

fname = tb.GetCell(currentRow,4).Text

lname = tb.GetCell(currentRow,5).Text

postcode = tb.GetCell(currentRow,11).Text

companyEmail = tb.GetCell(currentRow,15).Text

otherEmail = tb.GetCell(currentRow,16).Text

telephone = tb.GetCell(currentRow,13).Text

'msgbox tb.GetCell(currentRow,14).Text

position1 = tb.GetCell(currentRow,14).Text

leadId = tb.GetCell(currentRow,1).Text

add1 = tb.GetCell(currentRow,6).Text

add2 = tb.GetCell(currentRow,7).Text

locality = tb.GetCell(currentRow,8).Text

town = tb.GetCell(currentRow,9).Text

county = tb.GetCell(currentRow,10).Text

heading = tb.GetCell(currentRow,19).Text

url = tb.GetCell(currentRow,36).Text

'Create CSV FILE

row = currentRow + 1

customerId = "LeadId_" & leadId

address = add1 & " " & add2 & " " & locality

email = ""

if otherEmail = "" then

if not companyEmail = "" then

email = companyEmail

else

email = ""

end if

else

email = otherEmail

end if

if not telno = "" then

telephone = Right(telno,Len(telno)-1)

telephone = Replace(telephone, " ", "", 1, -1)

end if

if typeOfFile = "ELocation" then

objExcelSheet.Cells(row,1) = customerId

objExcelSheet.Cells(row,2) = businessName

objExcelSheet.Cells(row,3) = title

objExcelSheet.Cells(row,4) = fname

objExcelSheet.Cells(row,5) = lname

objExcelSheet.Cells(row,6) = telephone

objExcelSheet.Cells(row,9) = email

objExcelSheet.Cells(row,10) = address

objExcelSheet.Cells(row,11) = town

objExcelSheet.Cells(row,12) = postcode

objExcelSheet.Cells(row,13) = heading

else 'Dialler

contactName = title & " " & fname & " " & lname

objExcelSheet.Cells(row,1) = customerId

objExcelSheet.Cells(row,2) = businessName

objExcelSheet.Cells(row,3) = contactName

objExcelSheet.Cells(row,4) = telephone

objExcelSheet.Cells(row,6) = url

objExcelSheet.Cells(row,7) = email

objExcelSheet.Cells(row,8) = address

objExcelSheet.Cells(row,9) = town

objExcelSheet.Cells(row,10) = postcode

objExcelSheet.Cells(row,11) = heading

end if

CSVFilname = var & ".csv"

if typeOfFile = "ELocation" then

strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname

else 'Dialler

strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname

if tpsDataIncluded = true then

msgbox ("Some data has been excluded from the export as it is TPS")

end if

XLDoc.SaveAs(strFilename)

XLDoc.Close()

XLApp.Quit

Re: Speeding up export to CSV

Gysbert_Wassenaar — Wed, 01 Oct 2014 12:42:54 GMT

Create the required table in the script and store it to a csv file. Or create another table box with the right columns and export that by right clicking it and choosing Export... and select Comma Delimited (*.csv, *.txt) as output format.

Re: Speeding up export to CSV

Thu, 02 Oct 2014 06:48:47 GMT

Thanks

I created a hidden table and exported that, works a lot faster

Now just to work out how to speed up the write to the database.

Thank you

topic Re: Speeding up export to CSV in QlikView

Speeding up export to CSV

Re: Speeding up export to CSV

Re: Speeding up export to CSV