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.

--
Regards,
Tom Ogilvy

"uriel78" <uri...@interfree.it> wrote in message
news:RooWd.1011057$35.37...@news4.tin.it...

[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

[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?

--
Regards,
Tom Ogilvy

"uriel78" <uri...@interfree.it> wrote in message

news:hqzWd.623972$b5.28...@news3.tin.it...

[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" <uri...@interfree.it> wrote in message

news:XjxWd.623853$b5.28...@news3.tin.it...

[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...:-)

"Tim Williams" <saxifrax@pacbell*dot*net> ha scritto nel messaggio
news:%23gBzEPo...@TK2MSFTNGP15.phx.gbl...

[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

Hellboy Wrote:
> Sub Auto_open()
> Dim szFile As String
> Dim szLine As String
> Dim tabl() As String
> Dim szR As String
> Dim iCols As Integer
> Dim iA As Integer
> Dim iFileNo As Integer
> Dim iLines As Integer
> Dim strInstring As String
> Dim intInstring As Integer
> ' szDefaultDir = Cells(2, 3)
> ' ChDir szDefaultDir
>
>
> vrtFiles = Application.GetOpenFilename("*.*, *.*", , "Fichier de Plus
> de 255 Column", , True)
> Application.ScreenUpdating = False
> For Each fileToOpen In vrtFiles
> If fileToOpen <> False Then
> bolStopAddSheet = True
> szShortName = fileToOpen
> szXLSfile = fileToOpen & ".XLS"
> Workbooks.Add
> Rem ActiveWorkbook.SaveAs szXLSfile
> iFileNo = FreeFile
> Open fileToOpen For Input As #iFileNo
> iLines = 1
> While Not EOF(iFileNo)
> Line Input #iFileNo, szLine
> szLine = Trim(szLine)
> While Left(szLine, 1) = Chr(9) Or Left(szLine, 1) = ","
> szLine = Mid(szLine, 2, Len(szLine))
> Wend
> While Right(szLine, 1) = Chr(9) Or Right(szLine, 1) = ","
> szLine = Mid(szLine, 1, Len(szLine) - 1)
> Wend
> For intChar = 1 To 4
> Select Case intChar
> Case 1
> intInstring = InStr(1, szLine, Chr(9))
> 'Tabulation
> Case 2
> intInstring = InStr(1, szLine, Chr(32)) 'Space
> Case 3
> intInstring = InStr(1, szLine, ",") 'Comma
> Case 4
> intInstring = InStr(1, szLine, ";") '
> End Select
> If intInstring > 1 Then
> strInstring = Mid(szLine, intInstring, 1)
> Exit For
> End If
> Next intChar
> szR = SplitFullCabane(tabl, szLine, strInstring, iLines)
> iLines = iLines + 1
> Wend
> Close #iFileNo
> End If
> Sheets(1).Select
> Next fileToOpen
> End Sub
>
> Function SplitFullCabane(tabstrTableau() As String, strLigne As String,
> strSeparateur As String, intLines As Integer)
>
> Dim nLoop As Integer
> ReDim tabstrTableau(0, 254)
> iSheet = 1
> nLoop = 0
> While InStr(strLigne, strSeparateur) > 0
> tabstrTableau(0, nLoop) = Trim(Left(strLigne, InStr(strLigne,
> strSeparateur) - 1))
> strLigne = Mid(strLigne, InStr(strLigne, strSeparateur) + 1)
>
> While Left(strLigne, 1) = strSeparateur
> strLigne = Mid(strLigne, 2)
> Wend
> nLoop = nLoop + 1
> If nLoop = 255 Then
> Rem iSheet = iSheet + 1
> Sheets(iSheet).Range(Sheets(iSheet).Cells(intLines, 1),
> Sheets(iSheet).Cells(intLines, 255)) = tabstrTableau
> iSheet = iSheet + 1
> If bolStopAddSheet = True Then
> Sheets.Add after:=Sheets(iSheet - 1)
> End If
> ReDim tabstrTableau(0, 0)
> ReDim tabstrTableau(0, 254)
> nLoop = 0
> End If
> Wend
> tabstrTableau(0, nLoop) = strLigne
> Sheets(iSheet).Range(Sheets(iSheet).Cells(intLines, 1),
> Sheets(iSheet).Cells(intLines, 255)) = tabstrTableau
> ReDim tabstrTableau(0, 0)
> ReDim tabstrTableau(0, 254)
> If iSheet > 1 Then bolStopAddSheet = False
> End Function

--
joerg1004
------------------------------------------------------------------------
joerg1004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28089
View this thread: http://www.excelforum.com/showthread.php?threadid=351496