Tab Delimited text file problem

[nanban4u]

Hi,
I am downloading a tab delimited txt file when i am trying to parse the file using open function

Open Destination & "csn.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, NextLine              
    If NextLine <> "" Then
        arraydata =  split(NextLine)
        sSql = "insert into table"
        RunExecSQL sSql
    End If
Loop

it takes entire file as a single line I have attached the sample file with this and also when i use recordset its working file but for large number of data it says script out of range please help me

[Phil Weber]

Line Input looks for a carriage return/line feed pair to indicate the end of each line; your file has only line feeds (Chr(10)), so you won't be able to use Line Input.

If the file is small enough to read entirely into memory, you can do this:

Dim Lines() As String
Dim vLine As Variant

Open Destination & "csn.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, NextLine    
    If NextLine <> "" Then
        Lines = Split(NextLine, Chr(10))
        For Each vLine In Lines
            ArrayData =  Split(vLine)
            sSql = "insert into table"
            RunExecSQL sSql
        Next
    End If
Loop

[TrevorG]

If the file is relatively small you can also try this code

Dim strDataBuffer As String, strDataArray() As String
Dim lngRow As Long

' Create buffer large enough to hold entire contents of file
strDataBuffer = Space(FileLen(Destination & "csn.txt"))
Open Destination & "csn.txt" For Binary As #1
' Load complete file into buffer
Get #1, , strDataBuffer
Close 1

' Remove any carriage return characters (if they exist)
strDataBuffer = Replace(strDataBuffer, vbCr, "")

' Convert buffer contents into array
strDataArray = Split(strDataBuffer, vbLf)

' Process array
For lngRow = 0 To UBound(strDataArray)
   ' Process individual array items
   
Next

It will allow you to read in carriage return + linefeed formatted files (DOS/Windows) as well as linefeed only (UNIX) without needing to check beforehand.