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

[Rudolf Bargholz]

I have a tab delimited text file (iso-8859-1 encoding) that I need to copy into an Excel file, from the second row, starting at the cell "A2". The QM code I have written, with the help of the QM halp and the forum, works, but it is a bit slow. Does anyone have an idea how I can speed up the code?

Macro Macro3
 

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)

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

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

;get cells
Excel.Range allcells=xlSheet.Cells

for r 0 nr ;;for each row
,for c 0 nc ;;for each column
,,s=v.Cell(r c)
,,Excel.Range cell=+allcells.Item(r+2 c+1)
,,cell.Value=s.ConvertEncoding("iso-8859-1" _unicode)
,,;cell.Value=s

;make visible
xlApp.Visible = 1
xlApp.UserControl=1

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

The current list will have 3500 to 5000 rows, and this list has 11 columns, but the idea is to make a macro that can take an existing Excel file, make a copy, and then paste any list from a tab delimited text file into a specific worksheet and a specific cell of the copied Excel. In this way I can retain the formatting of the original 

Perhaps there is something I can do here to optimize the code? In this case the macro above, for 4500 rows and 11 columns, takes 30 seconds to complete, and my PC is no slouch. This is a lot slower than I was expecting, but perhaps this is just an performance issue with the COM interface provided by Excel. My hope is that there is something I can to to speed up the processing.

Regards

Rudolf Bargholz

[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.