0

I would like to read data from 831 MB text file. Unfortunately I got Run-time error 14. Out of string space. How can I change the code? I think is should be possible to read the whole data into an array on my notebook (W10 64 bit, 24 GB RAM, CPU 7500U, Excel 2016).

You can get data from The Global Drifter Program (buoydata_15001_jun16.dat). I need to process data from first 7 columns separated by spaces not by semicolon only.

Sub DelimitedTextFileToArray()
'PURPOSE: Load an Array variable with data from a delimited text file
'SOURCE: www.TheSpreadsheetGuru.com

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long

'Inputs
  Delimiter = ";"
  FilePath = "C:\buoydata_15001_jun16.txt"
  rw = 0

'Open the text file in a Read State
  TextFile = FreeFile
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile

'Separate Out lines of data
  LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable
  For x = LBound(LineArray) To UBound(LineArray)
    If Len(Trim(LineArray(x))) <> 0 Then
      'Split up line of text by delimiter
        TempArray = Split(LineArray(x), Delimiter)

      'Determine how many columns are needed
        col = UBound(TempArray)

      'Re-Adjust Array boundaries
        ReDim Preserve DataArray(col, rw)

      'Load line of data into Array variable
        For y = LBound(TempArray) To UBound(TempArray)
          DataArray(y, rw) = TempArray(y)
        Next y
    End If

    'Next line
      rw = rw + 1

  Next x

End Sub
John Deere
  • 29
  • 1
  • 4
  • What version of Excel? – Joe Mar 19 '17 at 17:33
  • And do you get the error reading it in, or doing the Split? – Joe Mar 19 '17 at 17:35
  • 2
    From reading your code I failed to see the reason why you want to read it all in one shot. Since you are processing it line by line, why not read it line by line? – A.S.H Mar 19 '17 at 18:08
  • I got the error while reading it i.e. **FileContent = Input(LOF(TextFile), TextFile)**. Basically I just need to store first 6 columns with data into an array to be able to process it by means of indirect addressing. I use Excel 2016. Sample data row where I replaced " " by "_": ___72615____9_30,750_2010____35,167___129,550____22,475___999,999___999,999___999,999__0,50850E-05__0,98110E-05__0,21694E-02 – John Deere Mar 19 '17 at 18:32
  • 1
    You probably use Excel 32-bit, then your 24 GB RAM does not help because Excel itself is limited in use of ram (2GB or 4GB). Maybe a better idea is to use PowerQuery which is included in Excel 2016. Your file seems to have more than 6 millions line. And maybe even better you take Access. – Storax Mar 19 '17 at 19:13
  • To be precise, your file has 6.810.951 lines – Storax Mar 19 '17 at 19:29
  • 1
    Try to use [`ADODB.Stream` or `SAPI.spFileStream`](http://stackoverflow.com/questions/33704692/vba-calculate-md5-hash-on-file-contents) to read the content of the file. `FSO` with `.ReadLine` method might help also. – omegastripes Mar 19 '17 at 23:25

1 Answers1

0

I don't think Excel is going to handle a file that is almost 1GB in size. Why not use a tool designed for larger data sets like R, Python, SQL Server, or even MS Access. Although, even Access may choke on this as it get fragmented very quickly when importing larger data sets. I suppose you can try it and see what happens!!

ASH
  • 20,759
  • 19
  • 87
  • 200