Import & Parsing a small 400 line CSV file

[tarns]

Could someone please direct me to a better script or modify the below so
i can import a csv file with each line on a seperate row with all values
on that line seperated by ',' to be placed in seperate columns?

Here is the format of the CSV file:
"1","Medium","Fixed","","ABC","CCB-A","17/06/2004 9:39:19 AM","Several
codeline issues"

This subroutine takes over 40 secs to complete on a P4 machine, when i
used 'EDIT TEXT IMPORT' it only took 3 secs.


Code:
--------------------

MsgBox ("Select a StarTeam CSV file to import")
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
ImportTextFile1 CStr(FName), ","

Sub ImportTextFile1(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Application.StatusBar = "IMPORTING TEXT FILE........"

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Replace(Mid(WholeLine, Pos, NextPos - Pos), """", "")
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

 

[Nigel]

Excel will import / parse a CSV file directly - just open it as a type CSV,
you will need to change the file name extension to CSV or Excel will run the
text import wizard.

The file format you show will parse correctly.

 

[tarns]

Yep , i have it running and it does parse correctly but it takes 15x
longer than using the Microsoft produced 'Edit Text Import' function.
Is there a more efficent way of writing this subroutine or maninpuating
the Microsoft supplied one?

 

[Tom Ogilvy]

MsgBox ("Select a StarTeam CSV file to import")
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Workbooks.OpenText fName, Comma:=True

Since you are using dd/mm/yyyy format, turn on the macro recorder and walk
through the text import wizard. The modify the recorded code to pass in the
filename.