Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
If it is tab separated, then the column widths make no difference. Which is
it? fixed width, or tab delimited. -- Regards, Tom Ogilvy "uriel78" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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 y240 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
sorry, fixed widht...
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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...?) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
How does querytables solve the 256 column problem?
-- Regards, Tom Ogilvy "uriel78" wrote in message ... 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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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....:-) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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...:-// |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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" wrote in message ... 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...?) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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 ... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
import txt files with more than 256 col and other tips
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...o&userid=28089 View this thread: http://www.excelforum.com/showthread...hreadid=351496 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Several .txt Files | Excel Discussion (Misc queries) | |||
Import files on a mac | Excel Programming | |||
import files | Excel Programming | |||
Import and Rename Files | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming |