read text file add line by line

[fraser5002]

Hi im reading lines of data from a text file how do i add each line as a new row in excel as im going along

i.e

read first line of text file
add data as first row in excel
read second line of text file
add data as second row in excel
...

i know how to read form the text file its just making that data a new row im not sure of

thanks

[Dr.K]

I'm not sure if you want to put the whole line into one cell, or if you are planning on parsing the text into cells, so here is a generic version.

I always use the FileSystemObject to handle Text Files, sorry if this is different from what you are currently doing.

[vba]Sub ReadLineByLine()

Dim strLine As String
Dim oFS As Object
Dim oTS As Object


Set oFS = CreateObject("Scripting.FileSystemObject")

Set oTS = oFS.OpenTextFile("C:\TextFile.txt")

Do While oTS.AtEndOfStream = False

strLine = oTS.Readline


'**DO SOMETHING WITH THE TEXT LINE STORED IN "strLine"


Loop


oTS.Close
Set oTS = Nothing
Set oFS = Nothing

End Sub[/vba]

[fraser5002]

thanks for that but how do i add that line then to a new row in excel , your right the line is separted by tab delimiter so if i could get each of those 4 elements to appear on different cols that would be brilliant
cheers

[JKwan]

[vba]
Sub ReadLineByLine()

Dim strLine As String
Dim oFS As Object
Dim oTS As Object
Dim strLineElements As Variant
Dim Index As Long
Dim i As Long

Set oFS = CreateObject("Scripting.FileSystemObject")

Set oTS = oFS.OpenTextFile("C:\Test.txt")
Index = 1
Do While oTS.AtEndOfStream = False

strLine = oTS.Readline
strLineElements = Split(strLine, vbTab)
For i = LBound(strLineElements) To UBound(strLineElements)
Cells(Index, i + 1).Value = strLineElements(i)

Next i
Index = Index + 1


Loop


oTS.Close
Set oTS = Nothing
Set oFS = Nothing

End Sub

[/vba]

[fraser5002]

Thanks for that it works.

One more question. My data im importing consists of 255 cols and 62,000 rows and it loads it very slow is there anyway i can speed up this code? . If im import the file directly using excel (manually) it loads quick . What does excel do when loading a tab delimted file that makes it so quick?

Cheers
Fraser

[Krishna Kumar]

Hi,

Does this make fast ?

[vba]Sub kTest()
Dim txt As String, fn As String, fs As Object
Dim w(), i As Long, c As Long, n As Long, x
fn = "C:\Test\" 'adjust the path
Set fs = CreateObject("Scripting.FileSystemObject")
txt = fs.opentextfile(fn & "test.txt").readall 'adjust the file name
x = Split(txt, vbCrLf)
ReDim w(1 To UBound(x) + 1, 1 To UBound(Split(x(0), vbTab)) + 1)
For i = 0 To UBound(x)
n = n + 1
For c = 0 To UBound(Split(x(i), vbTab))
w(n, c + 1) = Split(x(i), vbTab)(c)
Next
Next
With Range("a1")
.Resize(n, UBound(w, 2)).Value = w
End With
End Sub[/vba]

[Dr.K]

I don't see how thats any faster...

Look, if you just want to use the Excel Workbook Open Method, then use it!!!

[vba]Workbooks.Open "C:\TextFile.txt", 0, True, 1[/vba]

Will open a text file as a read-only spreadsheet, using Tabs as delimeters.



CHECK OUT THE HELP FILE FOR "Open Method":

Format Optional Variant. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

[fraser5002]

Yeh but my files are over 65K lines so i cannot open them directly through excel. My plan was to read in line by line then create a new page on the line 65000 or whatever so i need to use the previosu method , but is going a bit slow