rows.count property returns 2 even though only 1 row was imported by querytables.add
 

Environment:
Win7 Home Premium SP1
MS Office Home 2010
Excel 14
VBA for Applications 7.0

Importing a single-line text file with the following

Set qrytable = ActiveSheet.QueryTables.ADD(Connection:="TEXT;" + csvFile, ...)

With qrytable
.Refresh BackgroundQuery:=False
nValues = .ResultRange.Rows.Count
End With

produces nValues=2 from the Rows.Count property. If there's more than one line in the file, Rows.Count is indeed correct.

Anyone else seen this behaviour ?

rows.count property returns 2 even though only 1 row was imported by querytables.add
 

Environment:
Win7 Home Premium SP1
MS Office Home 2010
Excel 14
VBA for Applications 7.0

Importing a single-line text file with the following

Set qrytable = ActiveSheet.QueryTables.ADD(Connection:="TEXT;" + csvFile,
...)

With qrytable
.Refresh BackgroundQuery:=False
nValues = .ResultRange.Rows.Count
End With

produces nValues=2 from the Rows.Count property. If there's more than one
line in the file, Rows.Count is indeed correct.

Anyone else seen this behaviour ?

This is typical when the process writing the data to the text file doesn't
deliberately exclude the last carriage-return/linefeed. So importing as you've
done will include a blank record causing the record count to inflate by 1!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

rows.count property returns 2 even though only 1 row was importedby querytables.add
 

This is typical when the process writing the data to the text file doesn't
deliberately exclude the last carriage-return/linefeed. So importing as you've
done will include a blank record causing the record count to inflate by 1!

Garry,

Thanks but the inflation-by-one only occurs if the file has just one line, with or without the CR/LF (as created by venerable Notepad); with 2 or more lines, Rows.count returns the expected result.

Strangely, for a zero-length file it throws error 7 "out of memory" when zero is surely the expected result.

rows.count property returns 2 even though only 1 row was imported by querytables.add
 

This is typical when the process writing the data to the text file doesn't
deliberately exclude the last carriage-return/linefeed. So importing as
you've done will include a blank record causing the record count to inflate
by 1!

Garry,

Thanks but the inflation-by-one only occurs if the file has just one line,
with or without the CR/LF (as created by venerable Notepad); with 2 or more
lines, Rows.count returns the expected result.

Strangely, for a zero-length file it throws error 7 "out of memory" when zero
is surely the expected result.

Actually, Notepad itself doesn't add anything past your last line; - cr/lf's
are created by the user of Notepad via the keyboard or by pasting other text
containing a trailing 'newline'. When copying, any space after the last text
character is the culprit since that space is likely the non-printing cr/lf!

Writing to a plain text file via code (in most programming language's file I/O
processes) typically includes a trailing cr/lf. To avoid this happening must be
deliberately coded for.

Also, using Query is ominous at best; - via code you'd be better off using VBA
file I/O (IMO)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

rows.count property returns 2 even though only 1 row was imported by querytables.add
 

This is typical when the process writing the data to the text file doesn't
deliberately exclude the last carriage-return/linefeed. So importing as
you've done will include a blank record causing the record count to inflate
by 1!

Garry,

Thanks but the inflation-by-one only occurs if the file has just one line,
with or without the CR/LF (as created by venerable Notepad); with 2 or more
lines, Rows.count returns the expected result.

Using VBA file I/O:
I get 1 when importing a 1 line text file.

I get 2 when importing a 2 line text file containing:
fieldnames in line1;
data record in line2;
and I get 1 for RecordCount.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

rows.count property returns 2 even though only 1 row was imported by querytables.add
 

Does your query table include a headers row?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

rows.count property returns 2 even though only 1 row was importedby querytables.add
 

On Saturday, 9 June 2018 18:44:04 UTC+10, GS wrote:
Does your query table include a headers row?


Thanks for your follow ups.

This example is trimmed down to the barest of the bare essentials - the destination worksheet is bare (no headers) and the file is pure text (not even CSV), so .Add is just plonking the file contents straight into $A.

The cut-and-paste-ready VBA fragment is:

csvFike = "c:\temp\x.txt'
Set qrytable = ActiveSheet.QueryTables.ADD(Connection:="TEXT;" + csvFile, Destination:=Cells(1,1))

With qrytable
.Refresh BackgroundQuery:=False
nValues = .ResultRange.Rows.Count
End With

While I certainly see what you're sayin', that's not what I'm seein' - if the file contains only a string _without_ a CR/LF, .Count nonetheless returns 2: it's as though it's silently assumed the trailing CR/LF anyway.

Yes I suppose bypassing .Add in favor of direct VBA I/O is the way to go but it's a simple interface that works as expected save for this pestiferous corner case. As it is I've since used the following Truly Excruciating Workaround (quite acceptable for the application):

If (nValues = 2) Then
(WorksheetFunction.CountA(Rows(startrow + 1)) = 0) Then nValues = 1
End If

(Moreover, .Add also throws error 7 "Out of memory" if the file consists of two or less (not just zero) characters with no trailing CR/LF.)

rows.count property returns 2 even though only 1 row was imported by querytables.add
 

On Saturday, 9 June 2018 18:44:04 UTC+10, GS wrote:
Does your query table include a headers row?


Thanks for your follow ups.

This example is trimmed down to the barest of the bare essentials - the
destination worksheet is bare (no headers) and the file is pure text (not
even CSV), so .Add is just plonking the file contents straight into $A.

The cut-and-paste-ready VBA fragment is:

csvFike = "c:\temp\x.txt'
Set qrytable = ActiveSheet.QueryTables.ADD(Connection:="TEXT;" + csvFile,
Destination:=Cells(1,1))

With qrytable
.Refresh BackgroundQuery:=False
nValues = .ResultRange.Rows.Count
End With

While I certainly see what you're sayin', that's not what I'm seein' - if the
file contains only a string _without_ a CR/LF, .Count nonetheless returns 2:
it's as though it's silently assumed the trailing CR/LF anyway.

Yes I suppose bypassing .Add in favor of direct VBA I/O is the way to go but
it's a simple interface that works as expected save for this pestiferous
corner case. As it is I've since used the following Truly Excruciating
Workaround (quite acceptable for the application):

If (nValues = 2) Then
(WorksheetFunction.CountA(Rows(startrow + 1)) = 0) Then nValues = 1
End If

(Moreover, .Add also throws error 7 "Out of memory" if the file consists of
two or less (not just zero) characters with no trailing CR/LF.)

Ok! IMO, choosing an approach that doesn't work consistently and coorectly all
the time just isn't a viable option regardless of its simplicity!

Using VBA file I/O doesn't throw errors unless the file doesn't exist (using a
file dialog obviates that); AND you'll always get the correct record count (and
row count if no headers in the file).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion