Append from .xls - Blank rows not imported

[bs6600]

VFP6 - append from .... type xl8
Blank rows in the Excel sheet are not being imported.
Can anyone explain and tell me how to avoid this

PS to those of you replied when I posted this in the commands forum, I couldn't read your answers - please respond again.

Bill Spence,
Dunfermline, Scotland

 

[JimWWinter]

Presumably, the APPEND FROM command is designed to append data and blank rows are not considered data. If they were, you'd be appending 65,000 plus records with each append. Not sure why anyone would want to do that and I don't know of any way to change it. But, it should be simple enough to write some code to insert however many blank records you want after your append.

Regards,
Jim

 

[alvechurchdata]

PS to those of you replied when I posted this in the commands forum,

My reply seems to have gone walkabout altogther becasue I can't see it either.

One way around it is to save your Excel file as text. It will preserve the blank lines and these will show up as blank records when you APPEND the text file to your table.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[bs6600]

Thanks for the reply but this is a common technique Jim (since xbase and Excel were babies). It imports the rows of the .xls file or sheet 1 of a workbook without the remaining ?,000 of lines that have never been used.

Bill Spence,
Dunfermline, Scotland

 

[bs6600]

Jim - Forgive me if I misunderstood your reply. I should have made it clearer that the blank rows I am losing are within the area of the data rows, for example line1 has data and is imported; line 2 is blank and is not imported; line 3 has data and is imported.

A blank row in this context is one which has never had anything keyed in. Typing a space in A2 would make row 2 import ok.



Bill Spence,
Dunfermline, Scotland

 

[bs6600]

Geoff,

thanks, I could also ask the users to save as Excel2 thru 5 or as I have done ask them to type something in the blank lines.
Kind of defeats the purpose though so I'm still hoping for an explanation.

Bill Spence,
Dunfermline, Scotland

 

[JimWWinter]

Hi Bill,

I understood what you were talking about - just not the reason for wanting to include the blank rows. You probably have a very valid reason, but I don't know what it is.

Regards,
Jim

 

[bs6600]

Jim,

The client prepares input for say, a payroll system, in Excel, nice appearance, headings etc. The real data starts on say line 10 and might have each employee double spaced. I need to ignore the headings and find the first employee line. GO 10 in the dbf and ignore blank lines from there on is simplest and has always worked in the past.

Bill




Bill Spence,
Dunfermline, Scotland

 

[Mike Lewis]

Hi Bill,

Have you considered doing this via Automation? It means more programming, but it will give you much better control.

What I envisage is that you would get the "used range" of the worksheet, that is, the rectangle bounded by the last row and the last column that contains data. You can do that with something like:

oRange = oExcel.ActiveSheet.UsedRange

(I got that from Tamar's piece in the current AGMVFP.)

You could then loop through all the rows and column in that range, picking up each cell value in turn and doing whatever you like with it.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bs6600]

Mike,

Thanks - truth is I was trying for a quick fix in one app and in the other there are two .xls, one is the data ( maybe a new one every day) and the other has a 'rule' in the corresponding cells. e.g. G5 in the data contains £100 and G5 in the rules contains drA100 meaning debit account A100 with the £100. The user can change the format at will or make new rule sheets frequently with blank lines for appearance sake. Even a space character in a cell changes the behaviour.

So position is everything and I can't navigate by looking for keywords. Users can't be trusted to save as something else.

Even if I don't get a fix, I do hope someone tells me "WHY DOES IT DO IT?"

Will somemone please stop me if I get too long winded about this?

Bill Spence,
Dunfermline, Scotland

 

[alvechurchdata]

"WHY DOES IT DO IT?"

I suspect that if you could find an answer in MSDN it would be:

STATUS
This behavior is by design.


Geoff Franklin

http://www.alvechurchdata.co.uk

 

[baltman]

Here's your quick fix.

Brian

loExcel=CREATEOBJECT([Excel.Application])
loExcel.DisplayAlerts=.f.
loExcel.Workbooks.Open([c:\data\test.xls])
lnUsedRows = loExcel.ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
loExcel.ActiveWorkbook.Save()

FOR lnRows = 1 TO lnUsedRows 
	IF ISNULL(loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value)
		loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value=CHR(32)
	ENDIF 
ENDFOR 
loExcel.Quit

 

[baltman]

Move the save to after the fix and it'll work even better

You can even toggle this behavior if you like...

FOR lnRows = 1 TO lnUsedRows 
	DO CASE
		CASE ISNULL(loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value)
			loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value=CHR(32)

		CASE EMPTY(loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value)
			loExcel.ActiveWorkbook.ActiveSheet.range([A]+TRANSFORM(lnRows)).value=.null.
		
		OTHERWISE
			*nothing
	ENDCASE 
ENDFOR