Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum, Average, and Count from imported .txt file | Excel Worksheet Functions | |||
logic that returns a count | Excel Discussion (Misc queries) | |||
Some worksheet returns NULL for CodeName property. Why? | Excel Programming | |||
Count Property | Excel Programming |