0

I have a (large) column of data stored in a txt file. I need to copy the column vector in an Excel sheet. Here is my code:

Dim t As Single
t = Timer

Dim sFile As String
inputFile = "C:\Temp\vector.txt"

Dim rowNum As Long
rowNum = 1
Dim dest As Range
Set dest = Sheet1.Cells(rowNum, 1)

Open inputFile For Input As #1
Do Until EOF(1)
    Input #1, ReadData
    If Not IsEmpty(ReadData) Then
        dest.Cells = ReadData
        rowNum = rowNum + 1
        Set dest = Sheet1.Cells(rowNum, 1)
    End If
Loop
Close #1 'close the opened file

Sheet1.[C2].Value = Timer - t

I wonder whether there is a more efficient/fast way to accomplish the same task. To this aim, does it make sense to convert the txt file into another format (say .csv, .xlsx or any other file type) instead of reading lines from the .txt file? Any help is highly appreciated. S

Community
  • 1
  • 1
Stefano Lombardi
  • 1,581
  • 2
  • 22
  • 48
  • This might be more suitable for [Code Review](http://codereview.stackexchange.com/) rather than Stack Overflow? – RossC Sep 15 '14 at 08:55
  • 1
    Does [THIS](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) help? – Siddharth Rout Sep 15 '14 at 08:56
  • Many thanks @RossC, I wasn't aware of it. Shall I delete the post, or can I double post and put the reference to the other one? – Stefano Lombardi Sep 15 '14 at 08:56
  • AFAIK the mods can move it (if they deem it appropriate). Double posting tends to be frowned on. The code is pretty much how I would have done it, but the answer linked by Siddharth above should take care of it really. – RossC Sep 15 '14 at 09:22
  • Thanks to both of you! I am doing some trials with, say 500,000 random entries. I will post results soon. – Stefano Lombardi Sep 15 '14 at 09:24

2 Answers2

0

Following this link I have tried different solutions. The following code provides a much faster solution to the problem (importing in Excel a column of 500,000 random numbers) as compared to the code proposed in the initial question.

Dim t As Single
t = Timer

Dim inputFile As String
inputFile = "C:\Temp\vector.txt"

Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Sheet1")

Set wbO = Workbooks.Open(inputFile)

wbO.Sheets(1).Columns(1).Copy wsI.Columns(1)
wbO.Close SaveChanges:=False

Sheet1.[C2].Value = Timer - t

In particular, after 20 trials, the average computational time was 1.50 seconds, while con the first code it was 10.2 seconds. Hope this helps!

Community
  • 1
  • 1
Stefano Lombardi
  • 1,581
  • 2
  • 22
  • 48
0

If you want to use the first approach (which I'd recommend as it doesn't involve opening the files through Excel) then you can reduce the run time by batching the prints. Also you may want to consider using scripting.filesystemobject rather than the older IO interface.

See example below (Note this code hasn't been tested)

const path as string = ""
const max_print_rows as integer = 10000

dim print_start_cell as range
dim print_arr () as string
dim i as integer,j as long
dim fso as scripting.filesystemobject
dim in_file as scripting.textstream

set print_start_cell=thisworkbook.names("Start_Cell").referstorange
set fso=new scripting.filesystemobject
set in_file=fso.opentextfile(path,forreading)

redim print_arr(1 to max_print_rows,1 to 1)

do until in_file.atendofstream
  i=i+1

  print_arr(i)=in_file.readline

  if I=max_print_rows then
    print_start_cell.offset(j).resize(max_print_rows).value=print_arr
    j=j+i
    erase print_arr
    redim print_arr(1 to max_print_rows)
    i=1
  end if

loop

print_start_cell.offset(j).resize(max_print_rows).value=print_arr
erase print_arr

in_file.close
set in_file=nothing

set print_start_cell=nothing
set fso=nothing
Chris Tatum
  • 1
  • 1