Reading Excel file like a table

[ryupb]

Sorry about this one, I am a bit ignorant with regards to manipulating Excel objects and its automation...

what I am trying to achieve is read from the excel file and then terminate read after reading the first blank row...

assuming that I have test.xls as the text file, the excel file will have 5 columns and assuming 65 rows ( the rows can vary) I want to read all the contents of this file and save it in a dumping.dbf terminating only if the read encounters a blank row...

is this possible??

 

[StewartUK]

ryupb,

Yes!

I'll assume you know how to open and Excel spreadsheet with automation.

You could use something like...

RowCounter = 1
DO WHILE .T.
  IF NOT EMPTY(oSheet.Cell(RowCounter, 1)) ;
   AND NOT EMPTY(oSheet.Cell(RowCounter, 2)) ;
    AND NOT EMPTY(oSheet.Cell(RowCounter, 3)) ;
     AND NOT EMPTY(oSheet.Cell(RowCounter, 4)) ;
      AND NOT EMPTY(oSheet.Cell(RowCounter, 5)) 
* here you would have code to copy the values of cells 1 to 5 into your table or cursor
    RowCounter = RowCounter + 1 && increment the row counter to get the next row down
  ELSE
    EXIT && exit the DO loop
  ENDIF
ENDDO

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRB-Bldr]

Some VFP Excel Automation commands which might be of help to you could be:

* --- Open the Excel Object ---
tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION

mcWorkSheet = "Sheet1" && Worksheet Name
oExcel.Sheets(mcWorkSheet).SELECT
xlSheet = oExcel.activesheet

* --- Determine Last Cell At This Point ---
mnLastRow = oExcel.ActiveSheet.UsedRange.ROWS.COUNT
mnLastCol = oExcel.ActiveSheet.UsedRange.COLUMNS.COUNT

* --- Check All Existing Rows ---
FOR mnRowCntr = 1 TO mnLastRow
* --- Check All Existing Columns Within Rows ---
FOR mnColCntr = 1 TO mnLastCol
< Do Whatever >
ENDFOR
ENDFOR


Good Luck,
JRB-Bldr

 

[Mike Lewis]

Ryupb,

If you really want to avoid Automation, you could do something like this:

IMPORT FROM MyFile TYPE XL8  && imports the XLS into a DBF
SELECT MyFile
COPY TO NewFile FOR .F.  && create a new file with same cols as XLS
USE NewFile IN 0
SELECT MyFile
SCAN
  IF EMPTY(A) AND EMPTY(B) AND EMPTY(C) AND EMPTY(D) AND EMPTY (E)
     EXIT   && stop copying when an empty row encountered
  ENDIF
  SCATTER MEMVAR
  SELECT NewFile
  APPEND BLANK
  GATHER MEMVAR   && copy from old to new
  SELECT MyFile
ENDSCAN

I wrote the above in a bit of a hurry and haven't tested it, but it should put you on the right track.

MIke



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[ryupb]

thanks all.....

will have to try every one of them and see which one would be better fit for what i have in mind.

 

[baltman]

IMPORT FROM xxx.xls TYPE XL8 could also work for you. it does OK much of the time in determining the data row/col start/end automatically.

You have the added benefit of having data preloaded into a dbf (even it you need to fix the datatypes). And it would be faster than automation if it can get you to where you need to be.

Brian

 

[danceman]

obj.Application.Workbooks(1).SaveAs('tresults.dbf',xlDBF4)

this will save the file as a dbf file

Attitude is Everything

 

[jcbirch]

This might work for you too:

CREATE TABLE MyTable ( ;
Column1 c(25) NULL ;
Column2 n(15,4) NULL ;
Column3 d(8) NULL ;
Column4 y NULL)

SELECT MyTable
APPE FROM "MyFile.xls" TYPE xl8 SHEET "Sheet1"

Make sure you unfreeze any frozen panes first, and that the file is in Excel 97 format. Otherwise, FoxPro will fatal error and shut down on you.

I found that this was helpful when I needed to import several specific tabs from an Excel workbook into FoxPro. I just included these lines in a FOR...ENDFOR loop and named my tables, Excel files, and tabs with memory variables.

 

[wgcs]

This seems like the job for a CursorAdapter ... once someone writes one, we can all simply manipulate excel sheets.

- Bill

Get the best answers to your questions -- See FAQ481-4875.