Formatting of data from text file into Excel File.

[Arun]

Hello Gurus -I need to read the data from the test file.
By using the following code i can read the data from .txt file

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

But using the above mentioned code, data gets copied buti want that tab delimited file data goes to Cell by Cell. 1-That is row1 & coulmn1 data of file goes to A1 of xls sheet ,row1 & coulmn2 data of file goes to A2 etc etc.
2-In the xle file data is wrraped in every cell and outside and inside border also gets created for all of the rows which have the data.
Kindly help me in this.

[Krishna Kumar]

Hi Arun,

Welcome to board !!!

Try

[vba]Sub kTest()
Dim txt, i As Long, n As Long, w(), x, y
Dim fs As Object, c As Long

Set fs = CreateObject("scripting.filesystemobject")
txt = fs.OpenTextFile("C:\Test.txt").readall
x = Split(txt, vbCrLf)
r = UBound(x)
c = UBound(Split(x(0), vbTab)) + 1
If r > 1 Then
ReDim w(1 To r, 1 To c)
For i = 1 To r
y = Split(x(i), vbTab)
If UBound(y) > 0 Then
n = n + 1
For c = 0 To UBound(y)
w(n, c + 1) = y(c)
Next
End If
Next
End If
If n > 0 Then
With Sheets(1).Range("a1")
.Offset(1).Resize(n, UBound(w, 2)).Value = w
End With
End If
End Sub[/vba]

untested though.

HTH

[Arun]

Hi krishna,

Not working and data is not getting imported, xls sheet ........

thanks!
arun

[mdmackillop]

Can you post a sample of your Text file? Use Manage Attachments in the Go Advanced Reply section. You may need to Zip it first.

[Arun]

Hi ,

Attached is the sample text file, i have imported this file .
I want tab delimited file data goes to Cell by Cell.

1-That is row1 & coulmn1 data of file goes to A1 of xls sheet ,row1 & coulmn2 data of file goes to A2 etc etc.

2-In the xle file data is wrraped in every cell and outside and inside border also gets created for all of the rows which have the data.

Kindly help me in this.

Note:- Attached is the sample data for the text file.

[Krishna Kumar]

Arun,

Try

[vba]Sub kTest()
Dim txt, i As Long, n As Long, w(), x, y
Dim fs As Object, c As Long

Set fs = CreateObject("scripting.filesystemobject")
txt = fs.OpenTextFile("C:\Test.txt").readall
x = Split(txt, vbCrLf)
r = UBound(x)
c = UBound(Split(x(0), ",")) + 1
If r > 1 Then
ReDim w(1 To r, 1 To c)
For i = 0 To r
y = Split(x(i), ",")
If UBound(y) > 0 Then
n = n + 1
For c = 0 To UBound(y)
Select Case c
Case 0 To 5: w(n, c + 1) = Trim(y(c))
Case 6
If Len(Trim(y(c))) > 7 And InStr(1, y(c), ".") > 2 Then
w(n, c) = w(n, c) & " " & Split(y(c), "_")(0)
w(n, c + 1) = Trim(Split(y(c), "_")(1))
ElseIf Len(Trim(y(c))) > 7 Then
w(n, c) = w(n, c) & " " & y(c)
Else: w(n, c + 1) = Trim(y(c))
End If
Case 7
w(n, c) = Left(y(c), 7)
End Select
Next
End If
Next
End If
If n > 0 Then
With Sheets(1).Range("a1")
.Offset(1).Resize(n, UBound(w, 2)).Value = w
End With
End If
End Sub[/vba]
HTH

[Arun]

Hi Krishna,

With new code when run the same then it is throwing the following error -

---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '1004':
Application-defined or object-defined error
---------------------------
OK Help
---------------------------
.

[Krishna Kumar]

in which line?

[Arun]

Krishna

On following lines -

With Sheets(1).Range("a1")
.Offset(1).Resize(n, UBound(w, 2)).Value = w

Few of the data get imported to the xls sheet but few of the data from text file is still not imported. And all of the time i get the following error -

---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '1004':
Application-defined or object-defined error
---------------------------
OK Help
---------------------------

[Krishna Kumar]

I works fine for me with the attachment you posted.

Have a look.

[BrianMH]

Sorry if I misunderstand what your wanting but wouldn't it be easiest to open the file as a workbook do a text to column on it then saveas and choose whatever file type you want?