Good point!

Bill, if you come back to this thread, Dave's comment is about missing
data... my code will *only* work if there is *always* a value in each of the
eight fields of each record; that is, if any of those fields could be empty,
then this would allow two commas to be next to each other at a position
other than the location between records... if this could happen, then my
code would fail to work.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
But do watch out for those back to back commas that aren't the 7th and
8th.

Rick Rothstein wrote:

Here is a shorter (non-looping) routine that does the same thing as your
code (dumps each "record" into Column A)...

Sub ImportText()
Dim R As Range
Dim FileNum As Long
Dim TotalFile As String, Lines() As String
FileNum = FreeFile
Open "d:\temp\ExcelTest.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Lines = Split(TotalFile, ",,")
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)
R = WorksheetFunction.Transpose(Lines)
' R.TextToColumns R(1), xlDelimited, xlTextQualifierNone, Comma:=True
End Sub

Note that if you uncomment the last line, then the macro will distribute
the
8 fields in each record into 8 individual columns.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I _think_ that this does what you want.

But it dumps each of the "records" into column A.

After you've verified that it's working ok, you could record a macro
when
you
did the data|text to columns (specifying each of your fields the way
you
need)
and include it in the macro (or run that separately if you want).

Option Explicit
Sub testme01()

Dim TextLine As String
Dim lCtr As Long
Dim CommaCtr As Long
Dim MaxCommasPerRec As Long
Dim StartPos As Long
Dim myStr As String
Dim oRow As Long
Dim wks As Worksheet

Close #1
Open "c:\a.csv" For Input As #1

MaxCommasPerRec = 8

Set wks = Workbooks.Add(1).Worksheets(1)
wks.Range("A1").EntireColumn.NumberFormat = "@" 'text

oRow = 0
Do While Not EOF(1)
Line Input #1, TextLine
CommaCtr = 0
StartPos = 1
For lCtr = 1 To Len(TextLine)
If Mid(TextLine, lCtr, 1) = "," Then
CommaCtr = CommaCtr + 1
If CommaCtr = MaxCommasPerRec Then
myStr = Mid(TextLine, StartPos, lCtr - StartPos + 1)
StartPos = lCtr + 1
oRow = oRow + 1
wks.Cells(oRow, "A").Value = myStr
CommaCtr = 0
End If
End If
Next lCtr
If StartPos < Len(TextLine) Then
'still something left in that text line
wks.Cells(oRow, "A").Value = Mid(TextLine, StartPos)
End If
Loop

Close #1

End Sub


KC wrote:

Sequential read, counting commas I think.

"Billp" wrote in message
...
Hi,
I have a print out of a weighing scale.
It has outputted in serial text - comma delimited.
Bag Weight, Month, Day, Year, Hours, Minutes, Seconds, extra comma
then
repeats.
How can I import into xl such a text file so that every 8th comma
denotes
a
new row?
Example:
14.1230001,6,21,2009,19,46,51,,14.1230001,6,21,200 9,19,46,53,,14.1230001,6,21,2009,19,46,54,,

Every 8th coma denotes a new row.

Help.
Regards
Bill

--

Dave Peterson

--

Dave Peterson