PDA

View Full Version : Formatting of data from text file into Excel File.



Arun
03-09-2009, 04:10 AM
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
03-09-2009, 04:38 AM
Hi Arun,

Welcome to board !!!

Try

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

untested though.

HTH

Arun
03-09-2009, 05:20 AM
Hi krishna,

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

thanks!
arun

mdmackillop
03-09-2009, 06:38 AM
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
03-09-2009, 08:32 AM
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
03-10-2009, 09:30 AM
Arun,

Try

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
HTH

Arun
03-12-2009, 12:10 AM
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
03-12-2009, 06:13 AM
in which line?

Arun
03-12-2009, 06:55 AM
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
03-12-2009, 07:26 AM
I works fine for me with the attachment you posted.

Have a look.

BrianMH
03-12-2009, 10:44 AM
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?