PDA

View Full Version : Read a Text file with delimiter as comma and import the infor into excel sheet



sk_uc04
10-31-2007, 04:35 PM
Hello everybody,

I am new to Excel VBA. I am trying to read a text file in which columns are separated by commas, for eg:

1,2,3
2,3,4
3,4,5
4,5,6

I want to write this to an Excel worksheet. Final Excel sheet should have 3 columns and 4 rows(number of rows depends on on how many entries we have in a text file). So the worksheet will look like:

1 | 2 | 3
2 | 3 | 4
3 | 4 | 5
4 | 5 | 6

I would really appreciate if one could help me with that. Also, I have another question. When we are reading from a Text file and writing to an excel sheet, can we specify programmatically which row or column should the entry go to. For eg... looking at my text file, can I specify that the first coulmn should populate from B9:Bx (where x is the number of rows in the text file).

Thanks for your help in advance.

Regards,
SK

XLGibbs
10-31-2007, 05:47 PM
Data>External Data>Import text file.

Folw the wizard. YOu would start in the column you want the data to appear, or at then when it asks you where you want to return the data, you point and click. It is pretty routine.

lucas
10-31-2007, 07:56 PM
Option Explicit
Sub test()
MsgBox "sucessfully imported: " & importFile("B9", "f:\Temp\myTest.txt") & " records"
End Sub
Function importFile(myRange As String, myFilepath As String) As Long

Dim myValue1 As String, myValue2 As String, myValue3 As String
Dim myValue4 As String, myValue5 As String

Dim myRecordCount As Long: myRecordCount = 0

Open myFilepath For Input As #1 ' Open file for input.

Range(myRange).Select

Do While Not EOF(1) ' Loop until end of file.
Input #1, myValue1, myValue2, myValue3 ', myValue4, myValue5 'variable for each data component.

Selection.Offset(myRecordCount, 0).Value = myValue1 'you can change the offset to suit the field order you want
Selection.Offset(myRecordCount, 1).Value = myValue2
Selection.Offset(myRecordCount, 2).Value = myValue3
' Selection.Offset(myRecordCount, 3).Value = myValue4
' Selection.Offset(myRecordCount, 4).Value = myValue5

myRecordCount = myRecordCount + 1
Loop

importFile = myRecordCount
Close #1 ' Close file

End Function

sk_uc04
11-01-2007, 10:47 AM
Thanks a lot Lucas, it worked great.

I have another question. Can I specify that the first column should populate in range B9:Bx((where x is the number of rows in the text file) and second coulmn populate in range G9:Gx(where x is the number of rows in the text file) and third column populates in range I9:Ix((where x is the number of rows in the text file).

Regards,
SK