Serial comma delimited text - Import to XL evry 8th comma nuRow

[Billp]

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]

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.

 

[Billp]

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]

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.

 

[Billp]

Hi Rick,

One more question and request.
In the line
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

 

[keiji kounoike]

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

 

[KC]

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

 

[KC]

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]

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 Rothstein]

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

 

[Billp]

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