Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109509

Re: Huge data set Find-Replace

Show all headers | View raw

> Okay, Garry.
>
> I'll poke around and see what I can come up with.
>
> I never realized how very very slow the built-in Find/Replace was for 
> large data sets.
>
> It actually crashed/froze my sheet after a few minutes on a 650,000 
> cell test sheet.
>
> Thanks,
> Howard

Importing a delimited file into a worksheet is a bit more complex than 
the export process because the data needs to be converted to a 1D array 
of arrays OR a 2D array. In either case, the target position needs to 
be resized to fit the data. So for a tab-delimited file the import 
'concept' is...

Step1
  - import the data to a string var
  - Split(str, vbCrLf) into a variant

  Dim vData, sFile$
  sFile = GetOpenFilename: If sFile = "" Then Exit Sub
  vData = Split(ReadTextFile(sFile), vbCrLf)
    '(ReadTextFile() below returns a string and so this is a 1-liner)

Step2
  - split each element into an array

  Dim n&
  For n = LBound(vData) To UBound(vData)
    vdata(n) = Split(vData(n), vbTab)
  Next 'n

Step3
  - dump file data into worksheet

3a - 1D array of arrays:
  Dim lRow&
  For n = LBound(vData) To UBound(vData)
    lRow = lRow + 1
    Cells(lRow, 1).Resize(1, UBound(vData(n) + 1) = vData(n)
  Next 'n


3b - 1D array to 2D:
  Dim lMaxCols&, lMaxRows&, vOut()
  For n = LBound(vData) To UBound(vData)
    lMaxCols = Iif(UBound(vData(n)) > lMaxCols, _
               UBound(vData(n)), lMaxCols)
  Next 'n
  lMaxRows = UBound(vData)

  Redim vOut(lMaxRows, lMaxCols)
  For n = LBound(vData) To UBound(vData)
    Application.Index(vOut, n, 0)) = vData(n) '// (**NOT tested)
  Next 'n
  Cells(1, 1).Resize(lMaxRows +1, lMaxCols + 1) = vOut

**
I have more complex pure VB processes for converting arrays but think 
you should be able to take advantage of the Index() function here for 
code brevity. Both approaches handle zero-based dynamic array 
structures where the 2nd dim can also vary in the #cols.


Function ReadTextFile$(Filename$)
' Reads large amounts of data from a text file in one single step.
  Dim iNum%
  On Error GoTo ErrHandler
  iNum = FreeFile(): Open Filename For Input As #iNum
  ReadTextFile = Space$(LOF(iNum))
  ReadTextFile = Input(LOF(iNum), iNum)

ErrHandler:
  Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function  'ReadTextFile()

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-10-31 19:17 -0700
  Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 00:21 -0400
    Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 00:27 -0400
      Re: Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-11-01 01:11 -0700
        Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 15:19 -0400
          Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 19:12 -0400
            Re: Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-11-01 18:01 -0700
              Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-02 00:47 -0400

csiph-web