import txt files with more than 256 col and other tips

[uriel78]

I need to import a txt file, that contains a large number of columns
(separated by TAB) ,as follows:
1)jump the first two lines of *.txt
2)columns widths are [10 10 10 10 10 19 15....(from this
point =15 for every column)
3)import columns from 1 to 240 in sheet1
import columns from 241 to 276 in sheet2
...and so on

I search throgh google and NG reading a lot of suggestion, but still I can't
do this (important) thing...:-(

hoping this could help, here are the 3rd line of txt files (from whic I need
to do import)

0,000028 0,000032 0,000047 0,000095 0 0,00000838 0,00000052 0,00000319

 

[Tom Ogilvy]

If it is tab separated, then the column widths make no difference. Which is
it? fixed width, or tab delimited.

 

[Tim Williams]

Open the file using either excel's intrinsic file handling or use the
FileSystemObject if you prefer.

Reading the file one line at a time, split each line into an array
using Split()

dim v, x as integer, y as integer,s as integer, r as long

r=1

'open file

s=1
y=1
'read a line from the file
v=Split(theLine,vbTab)
for x=lbound(v) to ubound(v)
thisworkbook.sheets("Sheet" & s).cells(r,y).value=v(x)

if y>240 then
y=0
s=s+1
end if
y=y+1
next x

r=r+1

'loop and read next line


totally untested.

Tim

I need to import a txt file, that contains a large number of
columns
(separated by TAB) ,as follows:
1)jump the first two lines of *.txt
2)columns widths are [10 10 10 10 10 19
15....(from this
point =15 for every column)
3)import columns from 1 to 240 in sheet1
import columns from 241 to 276 in sheet2
...and so on

I search throgh google and NG reading a lot of suggestion, but
still I can't
do this (important) thing...:-(

hoping this could help, here are the 3rd line of txt files (from
whic I need
to do import)

0,000028 0,000032 0,000047 0,000095 0 0,00000838 0,00000052
0,00000319

 

[uriel78]

sorry, fixed widht...

 

[uriel78]

It seems to be a very good idea...but I'm totally a newbie with VBA and
programming...and so I can't complete the routine with the import and the
looping...maybe I could send you a sample of my txt file....(about 3Mb...?)

 

[uriel78]

well, maybe I've found a way to bypass my trouble by using querytables.add
macro four times (each time for different intervals)
....but still have some questions about the possibility to use a dialog
window to browse for the input file instead of specifying it in the macro...
just open another 3ad for this question...

 

[Tom Ogilvy]

How does querytables solve the 256 column problem?

 

[uriel78]

It doesn't solve the problem itself in general, but in my case it works
fine...
My file text is subidived into columns that I can consider forming 4 groups.
I turn on Macro REcorder and then import the text file (Data->import)
choosing the first group of columns.
Then just take a look to the macro recorded and coy it three times.

So I run 4 times the query everytime changing TextFileColumnDataTypes
property by defining which columns to take...


ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\elab1.txt _
, Destination:=Range("A1"))
......
....
....
..TextFileColumnDataTypes =Array(...)


I think it's rather a newbie trick, but if I consider I'm working with VBA
only for 3days in my life...it should be more than nothing....

 

[uriel78]

ehm...I hope you're an excel guru ....can you take a look to my other
post "Definition of a statistical function..."

I'm struggling with it...:-//

 

[Tim Williams]

uriel,

If you still need help you can send a file (make the obvious changes
to my e-mail address). If it's 3MB then you should zip it first.

Tim.

PS. It helps when posting if you include at least part of the text of
the message you're replying to.

 

[uriel78]

Thanks for your courtesy, I think I've found a little trick (See post below
in reply to Tom ) that solve my specific problem...)

If you want for your interest/research/curiosity I can send you the txt
file and my relative solution (a veryveryvery newbie solution)

Now I go and put my head into ice, 'cause it is burning...

 

[joerg1004]

This works great.
What would I need to do to import one text file into the current
workbook?
Or better yet, is there a way to import multiple long text files (500
columns) into the current workbook?

Joerg