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

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

Hi KC,

Thanks for the reply.
Could you point me in the direction of an example please.
Thanks
Bill

"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

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

Instead of using Line input statement use Input statement which will read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Jacob, i think you need to Dim one more variable, right.
Dim intFile, inttemp As Integer

That worked for me.
Ryan--

"Jacob Skaria" wrote:

Instead of using Line input statement use Input statement which will read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Thanks To all,
Best Regards
Bill


"RyGuy" wrote:

Jacob, i think you need to Dim one more variable, right.
Dim intFile, inttemp As Integer

That worked for me.
Ryan--

"Jacob Skaria" wrote:

Instead of using Line input statement use Input statement which will read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Yes Ryan; missed one (intTemp)...I havent used an IDE. Thanks for pointing
that out.

If this post helps click Yes
---------------
Jacob Skaria


"RyGuy" wrote:

Jacob, i think you need to Dim one more variable, right.
Dim intFile, inttemp As Integer

That worked for me.
Ryan--

"Jacob Skaria" wrote:

Instead of using Line input statement use Input statement which will read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will read
one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro
and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will read
one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro
and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

You can remove the X As Long data declaration (this code was cannibalized
from an older posting of mine which had used the X variable).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro
and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

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

Another version.
select new worksheet and run the macro, then data will be put into this
sheet.

Sub readtest()
Dim srow As Long, scolumn As Long, k As Long
Dim OneChr
Dim fileNum
Dim filename As String

filename = "C:\test.txt" '<<==Change to your data file
fileNum = FreeFile
Open filename For Input As fileNum
srow = 1 '<<==Change starting row number if you want
scolumn = 1 '<<==Change starting column number if you want
k = 0
Do While Not EOF(fileNum)
OneChr = Input(1, fileNum)
If OneChr = "," Then
k = k + 1
Cells(srow, scolumn) = WorksheetFunction.Clean(tmp)
scolumn = scolumn + 1
tmp = ""
Else
tmp = tmp & OneChr
End If

If k = 8 Then
srow = srow + 1
scolumn = 1
k = 0
End If
Loop
Close fileNum
End Sub

Keiji

Billp wrote:
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

Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the macro
and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

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

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

There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

And if those ",," characters only showed up where line breaks should have been,
I'd use my favorite text editor (I like UltraEdit) to change them vblfcr's.

Then I could open them normally.

Or even use code like this within excel:

Option Explicit
Sub testme02()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String
Dim myString As String

myInFileName = "C:\a.csv"
myOutFileName = "C:\a.txt"

myString = ",,"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = True
.Pattern = myString
myContents = Replace(myContents, ",," & vbCrLf, ",,")
myContents = Replace(myContents, ",,", vbCrLf)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

And then import the .txt file (recording a macro to get the fields correct).


Rick Rothstein wrote:

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

--

Dave Peterson

And for a non-FSO, non-RegEx macro to do the same thing, I would probably do
it this way (which, I'm thinking, might be faster)...

Sub ConvertCommaCommaToNewLine()
Dim FileNum As Long
Dim TotalFile As String
FileNum = FreeFile
Open "d:\temp\ExcelTest.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
FileNum = FreeFile
Open "d:\temp\ExcelTest.txt" For Output As #FileNum
Print #FileNum, Replace(TotalFile, ",,", vbNewLine)
Close #FileNum
End Sub

However, if you were going to use code to convert the double commas to
newlines just so you could import the file, then I would probably just use
the code I posted earlier and let it do the "import" for you.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
And if those ",," characters only showed up where line breaks should have
been,
I'd use my favorite text editor (I like UltraEdit) to change them
vblfcr's.

Then I could open them normally.

Or even use code like this within excel:

Option Explicit
Sub testme02()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String
Dim myString As String

myInFileName = "C:\a.csv"
myOutFileName = "C:\a.txt"

myString = ",,"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = True
.Pattern = myString
myContents = Replace(myContents, ",," & vbCrLf, ",,")
myContents = Replace(myContents, ",,", vbCrLf)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

And then import the .txt file (recording a macro to get the fields
correct).


Rick Rothstein wrote:

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

--

Dave Peterson

To one and all I thank you again.

To change
Open "c:\comma.txt" For Input As #intFile

To one that asks the user to search, and input the text file via explorer.

I am really appreciative and humbled for all the help.
I cannot tick all for thank you for all deserve it.
Best and Kind Regards
Bill

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

Another version.
select new worksheet and run the macro, then data will be put into this
sheet.

Sub readtest()
Dim srow As Long, scolumn As Long, k As Long
Dim OneChr
Dim fileNum
Dim filename As String

filename = "C:\test.txt" '<<==Change to your data file
fileNum = FreeFile
Open filename For Input As fileNum
srow = 1 '<<==Change starting row number if you want
scolumn = 1 '<<==Change starting column number if you want
k = 0
Do While Not EOF(fileNum)
OneChr = Input(1, fileNum)
If OneChr = "," Then
k = k + 1
Cells(srow, scolumn) = WorksheetFunction.Clean(tmp)
scolumn = scolumn + 1
tmp = ""
Else
tmp = tmp & OneChr
End If

If k = 8 Then
srow = srow + 1
scolumn = 1
k = 0
End If
Loop
Close fileNum
End Sub

Keiji

Billp wrote:
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

Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have. The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Thanks Rick,

I had an extra gap between the coma's.
I had some typos - I cut and pasted this time and works exceptionally well.
Rows 1 to 10 align correctly. rows 11 to 20 are stepped one cell to the right.
Rows 21 to 70 are back in line.
Rows 71 to 80 step right 1 cell
Rows 81 to 130are in line
131 to 136 step right

looks like every 50 rows are in line, with 10 rows stepped right 1 cell then
repeats - weird?

Thanks Rick so much indebted to you.

Best Regards
Bill



"Rick Rothstein" wrote:

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have. The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

I'd have to actually see the file to be able to trace the problem with the
"stepping" that you are reporting. If you want, you can send the file to me
directly and I'll take a look at it... just remove the NO.SPAM stuff from my
return email address.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Thanks Rick,

I had an extra gap between the coma's.
I had some typos - I cut and pasted this time and works exceptionally
well.
Rows 1 to 10 align correctly. rows 11 to 20 are stepped one cell to the
right.
Rows 21 to 70 are back in line.
Rows 71 to 80 step right 1 cell
Rows 81 to 130are in line
131 to 136 step right

looks like every 50 rows are in line, with 10 rows stepped right 1 cell
then
repeats - weird?

Thanks Rick so much indebted to you.

Best Regards
Bill



"Rick Rothstein" wrote:

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have.
The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's
latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in
message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which
will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste
the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Hi Rick,

One more question and request.
In the line
Open "d:\temp\ExcelTest.txt" For Binary As #FileNum

Can this be made so that it is not a one off - is it possible to prompt for
the location of the file like
getfile?

Thanks
Best regards
Bill

"Rick Rothstein" wrote:

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have. The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

You already have had many alternatives. you don't need this, but i
modified a little to select file.

Sub readtest()
Dim srow As Long, scolumn As Long, k As Long
Dim OneChr
Dim fileNum
Dim filename

filename = Application.GetOpenFilename _
("Text File (*.txt;*.cvs;*.prn), *.txt;*.cvs;*.prn")

If VarType(filename) = vbBoolean Then Exit Sub

fileNum = FreeFile
Open filename For Input As fileNum
srow = 1 '<<==Change starting row number if you want
scolumn = 1 '<<==Change starting column number if you want
k = 0
Do While Not EOF(fileNum)
OneChr = Input(1, fileNum)
If OneChr = "," Then
k = k + 1
Cells(srow, scolumn) = WorksheetFunction.Clean(tmp)
scolumn = scolumn + 1
tmp = ""
Else
tmp = tmp & OneChr
End If

If k = 8 Then
srow = srow + 1
scolumn = 1
k = 0
End If
Loop
Close fileNum
End Sub

P.S.
I wonder # before file number is mandatory.

Keiji

Billp wrote:
To one and all I thank you again.

To change
Open "c:\comma.txt" For Input As #intFile

To one that asks the user to search, and input the text file via explorer.

I am really appreciative and humbled for all the help.
I cannot tick all for thank you for all deserve it.
Best and Kind Regards
Bill

"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

Another version.
select new worksheet and run the macro, then data will be put into this
sheet.

Sub readtest()
Dim srow As Long, scolumn As Long, k As Long
Dim OneChr
Dim fileNum
Dim filename As String

filename = "C:\test.txt" '<<==Change to your data file
fileNum = FreeFile
Open filename For Input As fileNum
srow = 1 '<<==Change starting row number if you want
scolumn = 1 '<<==Change starting column number if you want
k = 0
Do While Not EOF(fileNum)
OneChr = Input(1, fileNum)
If OneChr = "," Then
k = k + 1
Cells(srow, scolumn) = WorksheetFunction.Clean(tmp)
scolumn = scolumn + 1
tmp = ""
Else
tmp = tmp & OneChr
End If

If k = 8 Then
srow = srow + 1
scolumn = 1
k = 0
End If
Loop
Close fileNum
End Sub

Keiji

Billp wrote:
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

No worries.
I only always work out details on my own.
I never used Binary input before, Space is also new to me.

"Rick Rothstein" wrote in message
...
There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

We are obsessed with speed.

Using this thread as example, line input and get #filenum, which one is
faster please?

Is there any gain if we split the string by ","
loop and write into an array in memory,
then dump the array out in a worksheet range please?

"Rick Rothstein" wrote in message
...
There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in message
...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

I don't have a speed testing routines that I use, so I wouldn't be able to
comment on the speed with any certainty. My gut feeling is that on a large
file, the method I posted will probably be faster as it loads the entire
file into memory all at once rather than going back to the hard drive
line-by-line... of course, there is a time loss built into my method that is
not in the Line Input code because after loading the entire file, it then
must be split into an array for processing and VB's Split function is not
the fastest of functions in the VB arsenal.

As to your question about using Split to break each field apart... I would
think the call to the TextToColumn property that my code uses would be
faster.

--
Rick (MVP - Excel)


"KC" wrote in message
...
We are obsessed with speed.

Using this thread as example, line input and get #filenum, which one is
faster please?

Is there any gain if we split the string by ","
loop and write into an array in memory,
then dump the array out in a worksheet range please?

"Rick Rothstein" wrote in message
...
There is one caveat that comes with my code, though... see Dave's latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message ...
Instead of using Line input statement use Input statement which will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

I sent you an off-line response (in response to your email to me) about the
"one off" problem. For those following this thread, the problem was that
some of the records were separated from the rest by 3 commas whereas others
were separated from the rest by 2 commas. The solution was to use VB's
Replace function to change the triple commas to double commas before the
Split function line of code is executed.

To make the file ask for the filename rather than hard coding it in, first
declare a variable (I'm using FileName for my example)...

Dim FileName As String

Then put these two lines of code immediately before the Open statement in my
code...

FileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FileName = "False" Then Exit Sub

And then change the Open statement itself to this...

Open FileName For Binary As #FileNum

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Hi Rick,

One more question and request.
In the line
Open "d:\temp\ExcelTest.txt" For Binary As #FileNum

Can this be made so that it is not a one off - is it possible to prompt
for
the location of the file like
getfile?

Thanks
Best regards
Bill

"Rick Rothstein" wrote:

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have.
The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's
latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in
message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which
will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste
the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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

Thank you so much Rick,
I am thankful and very appreciative of your help.
Best Regards
Bill

"Rick Rothstein" wrote:

I sent you an off-line response (in response to your email to me) about the
"one off" problem. For those following this thread, the problem was that
some of the records were separated from the rest by 3 commas whereas others
were separated from the rest by 2 commas. The solution was to use VB's
Replace function to change the triple commas to double commas before the
Split function line of code is executed.

To make the file ask for the filename rather than hard coding it in, first
declare a variable (I'm using FileName for my example)...

Dim FileName As String

Then put these two lines of code immediately before the Open statement in my
code...

FileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FileName = "False" Then Exit Sub

And then change the Open statement itself to this...

Open FileName For Binary As #FileNum

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Hi Rick,

One more question and request.
In the line
Open "d:\temp\ExcelTest.txt" For Binary As #FileNum

Can this be made so that it is not a one off - is it possible to prompt
for
the location of the file like
getfile?

Thanks
Best regards
Bill

"Rick Rothstein" wrote:

Did you use the code **exactly** as I posted it, or did you modify it in
some way? If you modified it, then you will need to post what you have.
The
error you are getting sounds like you didn't run this line...

Lines = Split(TotalFile, ",,")

which appears immediately in front of the line of code you are saying
errored out.

--
Rick (MVP - Excel)


"Billp" wrote in message
...
Greetings,

Thank you

In the line
Set R = ActiveSheet.Cells(2, "A").Resize(UBound(Lines) + 1)

The code traps at UBound " Expecting Array" ?

Is there any way not to dump everything into column A and one cell?
I have tried most if not all of the very welcome code examples.
The one mac() worked well and I am thankful - it would be nice if each
group
of 8 was on a new row - is this possible?

Kind Regards
Bill

"Rick Rothstein" wrote:

There is one caveat that comes with my code, though... see Dave's
latest
response to me and my response back to him.

--
Rick (MVP - Excel)


"KC" wrote in message
...
Even better.
I am imagining their speed.
Vooom

"Rick Rothstein" wrote in
message
...
How about this non-looping solution then?

Sub ImportText()
Dim R As Range
Dim X As Long, 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

--
Rick (MVP - Excel)


"KC" wrote in message
...
I like this line input + split

"Jacob Skaria" wrote in
message
...
Instead of using Line input statement use Input statement which
will
read one
field at a time.
Input #intFile, F1, F2, F3, F4, F5, F6, F7, F8

OR split that to an array as below. Open a new workbook. Paste
the
macro and
try..


Sub Mac()
Dim intFile As Integer
Dim strData As String
Dim arrData As Variant
Dim lngRow As Long, lngCol As Long
intFile = FreeFile

On Error Resume Next

Open "c:\comma.txt" For Input As #intFile
Line Input #intFile, strData
arrData = Split(strData, ",")
For intTemp = 0 To UBound(arrData) Step 8
lngRow = lngRow + 1
For lngCol = 1 To 8
Cells(lngRow, lngCol) = arrData(intTemp + lngCol - 1)
Next
Next
Close #intFile

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Billp" wrote:

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