Tab delimited text file to Excel - how to speed up code

[Rudolf Bargholz]

Ok, I have been able to reduce the speed of Excel generation dramatically, from 30 seconds, with the method above, to about 0.8 seconds, using the following:

Macro Macro4
 

Code:

Copy      Help

str s

SYSTEMTIME st
GetLocalTime &st
out s.format("%02i:%02i:%02i.%03i" st.wHour st.wMinute st.wSecond st.wMilliseconds)

ICsv v._create
v.Separator="[9]"
v.FromFile("C:\Users\rb\AppData\Local\Temp\olt\tmp\167014016675436\export.txt")
v.ToString(s)
s.ConvertEncoding("iso-8859-1" _unicode)
v.FromString(s)

;str fs = "Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]01.11.2023[9]0[9]2[9]Doppelzimmer Meersichtäöüé"
;fs + "[]Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]02.11.2023[9]0[9]2[9]Doppelzimmer Meersicht"
;fs + "[]Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]03.11.2023[9]0[9]2[9]Doppelzimmer Meersicht"
;v.FromString(fs)

int nr=v.RowCount
int nc=v.ColumnCount
int r c

ARRAY(str) a
v.ToArray(a)

;start Excel and create worksheet
Excel.Application xlApp._create
Excel.Worksheet xlSheet=xlApp.Workbooks.Add(Excel.xlWBATWorksheet).ActiveSheet ;;add workbook and get worksheet

ExcelSheet e.Init
e.CellsFromArray(a "A2")
e.Activate(4) 

GetLocalTime &st
out s.format("%02i:%02i:%02i.%03i" st.wHour st.wMinute st.wSecond st.wMilliseconds)

The code takes an tab delimited file with 4500 rows, 11 columns, reads it into an array in Quick Macros, converts it from ISO-8859-1 to UTF-8, and then pastes the content from the array into the Excel file starting from a cell specified in the code.

Still have quite a bit to do to be able to call this from our code, but the heavy loading part seems to work well.