How to handle Blanks (empty Rows)

  • Thread starter Thread starter Carlos Magalhaes
  • Start date Start date
C

Carlos Magalhaes

Good day all,

I have developed an Upload from excel to and ERP system. The way it
works is it will use OLEDB to run a select statement for fields that I
need, then i insert the results into a DataSet and form the dataset
its uploaded to the ERP system after a number of integ checks. This is
done for 100 diffrent xls file at a time.

There is how ever quite a big problem (isnt there always :P) when I
reach a file that starts with lest say more than TWO blank rows
(excluding HDR), the select statment exsits and no other data is
returned, for example:

Data EXAMPLE:>>>

F1 F2 F3 F4
BLANK BLANK BLANK BLANK
BLANK BLANK BLANK BLANK
BLANK BLANK BLANK BLANK
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA

And I run Select F1,F2 ,F3,F4 from [Upload ERP$] the returned dataset
is 0 records ..... EVEN though after the four blank rows there is data
that should be retrived!!

But if the data is formatted as

F1 F2 F3 F4
DATA DATA DATA DATA
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
DATA DATA DATA DATA
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA
BLANK BLANK BLANK BLANK
DATA DATA DATA DATA

Then the same select statement will work and just ignore the blank
rows, is there something I am missing here?

Thank you for your time.

Carlos Magalhaes
 
Carlos,
How about setting a range in the work book for your query. You can open
the workbook and see where the data starts and ends. Then set a range in
the workbook and save it. Then you can select from the range in the
workbook, when retrieving your rows. Or if you don't want to save a named
range you can adjust your query to select only the cells you know have
data. Here's a pseudo sample…

Specify a Named Range

To specify a named range of cells as your recordsource,
simply use the defined name. For example:

strQuery = "SELECT * FROM MyRange"


Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard
Excel row/column notation to the end of the sheet name in the square
brackets. For example:


strQuery = "SELECT * FROM [Sheet1$A1:B10]"


Here's a good KB Article that covers this information, and a lot more.
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/?id=257819


Hope this helps out!

Stephen Culhane
(e-mail address removed)
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
| From: Carlos Magalhaes <[email protected]>
| Subject: How to handle Blanks (empty Rows)
| Date: Fri, 05 Dec 2003 10:14:25 +0200
| Organization: http://groups.yahoo.com/group/ADSIANDDirectoryServices
| Reply-To: (e-mail address removed)
| Message-ID: <[email protected]>
| X-Newsreader: Forte Agent 1.93/32.576 English (American)
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.excel.programming
| NNTP-Posting-Host: mail.trencor.co.za 195.75.154.163
| Lines: 1
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.programming:439758
| X-Tomcat-NG: microsoft.public.excel.programming
|
| Good day all,
|
| I have developed an Upload from excel to and ERP system. The way it
| works is it will use OLEDB to run a select statement for fields that I
| need, then i insert the results into a DataSet and form the dataset
| its uploaded to the ERP system after a number of integ checks. This is
| done for 100 diffrent xls file at a time.
|
| There is how ever quite a big problem (isnt there always :P) when I
| reach a file that starts with lest say more than TWO blank rows
| (excluding HDR), the select statment exsits and no other data is
| returned, for example:
|
| Data EXAMPLE:>>>
|
| F1 F2 F3 F4
| BLANK BLANK BLANK BLANK
| BLANK BLANK BLANK BLANK
| BLANK BLANK BLANK BLANK
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
|
| And I run Select F1,F2 ,F3,F4 from [Upload ERP$] the returned dataset
| is 0 records ..... EVEN though after the four blank rows there is data
| that should be retrived!!
|
| But if the data is formatted as
|
| F1 F2 F3 F4
| DATA DATA DATA DATA
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| DATA DATA DATA DATA
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
| BLANK BLANK BLANK BLANK
| DATA DATA DATA DATA
|
| Then the same select statement will work and just ignore the blank
| rows, is there something I am missing here?
|
| Thank you for your time.
|
| Carlos Magalhaes
|
 
Back
Top