Import Text File Directly to an Array

[Guest]

Hello,

I have data in a text file (semicolon delimited) like this

0;Name1;0.001;0.002;0.003;;;;;and a lot lot more (>70k characters/digits)
1;Name2;0.005;0.006;0.007;;;;;and a lot lot more
2;Name3;0.005;0.006;0.007;;;;;and a lot lot more
and a lot lot more lines

What I used to do was that I initially imported the data into a cell wtih
the querytable function (one line per one cell). Then, I used the split
function to store the data into an array (one cell per one array) and the
program started from there. This worked perfectly fine when each line had
less than 30k characters. Now, I have more than 70k characters per one line
and therefore I can't physically import them into one cell. Excel limits the
number of characters in one cell to about 32k.

Is there a way that I can directly import the data and store it into an
array (one line per one array) without having to physically write the data
into a cell first? This way (if possible) I can bypass the limitation that a
cell can hold.


Please help and thanks in advance,
Adrian T

 

[Tim Williams]

Reading one line in at a time (into sLine), you can use

'*************
dim arr
arr=split(sLine,";")
debug.print ubound(arr)
'use arr
'*************

Tim

 

[Guest]

Hi Tim,

How do I read one line into sLine? When I used querytable function, one
parameter in that function is a destination which is a range. After writing
into a cell, I will then use the split function. This won't work anymore
because one cell can only read less than 32k characters. In other words, I
can't use the querytable function anymore because it requires writing into a
cell (right?).

So, it's still unclear to me how you will read the line and store it into
sLine.

Thanks so much,

Adrian T

 

[stevebriz]

Hello,

I have data in a text file (semicolon delimited) like this

0;Name1;0.001;0.002;0.003;;;;;and a lot lot more (>70k characters/digits)
1;Name2;0.005;0.006;0.007;;;;;and a lot lot more
2;Name3;0.005;0.006;0.007;;;;;and a lot lot more
and a lot lot more lines

What I used to do was that I initially imported the data into a cell wtih
the querytable function (one line per one cell). Then, I used the split
function to store the data into an array (one cell per one array) and the
program started from there. This worked perfectly fine when each line had
less than 30k characters. Now, I have more than 70k characters per one line
and therefore I can't physically import them into one cell. Excel limits the
number of characters in one cell to about 32k.

Is there a way that I can directly import the data and store it into an
array (one line per one array) without having to physically write the data
into a cell first? This way (if possible) I can bypass the limitation that a
cell can hold.

here is some thoughts
This came from MSDN re:VB ( not specifically VBA) but chances are it
applies.

Length Limits of array
The length of every dimension of an array is limited to the maximum
value of the Integer data type, which is (2 ^ 31) - 1. However, the
total size of an array is also limited by the memory available on your
system. If you attempt to initialize an array that exceeds the amount
of available RAM, the common language runtime throws an
OutOfMemoryException exception.

I just did a quick test and was able to put values in array 500 rows
, 40000 columns and that worked...but much bigger than that it came up
with out of memory.

This was using the testnote I did not read the values into a cell)

Dim Strarray (500,40000)
Erase Strarray
For i = 1 to 500
For j = 1 to 40000
strarray(i,j) = i+ j
Next j
Next i
Msgbox Strarray(500,39999)
End sub

Hopes this gives you some help

 

[Tim Williams]

Try this.

'****************************
dim lNum as long
dim sFile as string
dim sLine as string

sFile="C:\stuff\test.txt"

lNum=FreeFile
Open sFile For Input As lNum

While Not EOF(lNum)
Line Input lNum, sLine
'process sLine
Wend
Close lNum
'************************************

Tim

 

[NickHK]

If you use Data>Import Text File and follow the wizard, you can set the
delimiter to a " ; ", then no problem with number of characters in a cell
and no need for Text-To-Columns.
You will of be limited to 256 column in versions before 2007.

NickHK

 

[NickHK]

Otherwise, you can do it yourself :
http://www.cpearson.com/excel/imptext.htm

NickHK