Import Text File Directly to an Array
 

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

Import Text File Directly to an Array
 

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

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

Tim

"Adrian T" wrote in message
...
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

Import Text File Directly to an Array
 

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


"Tim Williams" wrote:

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

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

Tim

"Adrian T" wrote in message
...
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

Import Text File Directly to an Array
 

Adrian T wrote:
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 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 test:(note 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

Import Text File Directly to an Array
 

If you use DataImport 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

"Adrian T" wrote in message
...
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

Import Text File Directly to an Array
 

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

NickHK

"NickHK" wrote in message
...
If you use DataImport 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

"Adrian T" wrote in message
...
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