Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 7th 18, 12:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 3
Default 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 ?

  #2   Report Post  
Old June 7th 18, 07:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 944
Default 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
  #3   Report Post  
Old June 8th 18, 01:38 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 3
Default 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.
  #4   Report Post  
Old June 9th 18, 09:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 944
Default 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
  #5   Report Post  
Old June 9th 18, 09:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 944
Default 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


  #6   Report Post  
Old June 9th 18, 09:43 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 944
Default 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
  #7   Report Post  
Old June 9th 18, 10:33 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 3
Default 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.)
  #8   Report Post  
Old June 9th 18, 02:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 944
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum, Average, and Count from imported .txt file sdhi Excel Worksheet Functions 2 October 18th 09 03:44 PM
logic that returns a count d_kight Excel Discussion (Misc queries) 8 May 18th 06 12:30 AM
Some worksheet returns NULL for CodeName property. Why? Shigeo Morita Excel Programming 4 December 3rd 04 11:09 PM
Count Property jacqui Excel Programming 2 April 6th 04 11:40 AM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017