Convert Part of EXCEL spreadsheet to DBF 1

[WIREMESH]

I have an excel spreadsheet that contains data I want to convert to DBF. The 1st 9 rows of the spreadsheet contain header information I want to ignore. Starting with row 10 are the column headings. I want to convert the remaining rows to a dbf file. I have several files and each file contains a different # of rows.

How can I tell excell to select rows starting with row 10, col1, thur row_variable, col12 and convert these rows to a DBF?

 

[robsuttonjr]

It would be very simple to import the entire spreadsheet then use foxpro to do something like:
Select * from myexceldbf where RECNO() > 9 INTO CURSOR Ctemp

Regards,

Rob

 

[WIREMESH]

The top of the spreadsheet contains a lot of data that does not fit into the columns I need to import. Importing the entire spreadsheet will not work. First of all, excel will probably name the columns something other than the column headings I want starting at line 10.

 

[JRB-Bldr]

Are you considering doing this only once or repeatedly via VFP automation?

With automation you would do something like the following:
- Create the Excel Object
- Open the appropriate Excel table into the Excel object
- Select the desired rows/columns
- Save AS your NewDBF using the DBF4 format
(only the Selected rows/columns will be saved/converted)
- Close the Excel file
- Quit the Excel object
- Release the Excel object

If you include the row with the column names, the SaveAs to DBF4 will attempt to use the same names as field names (up to 10 characters and with no spaces).

The field type of the resultant DBF file might need to be changed depending on what you start with and what you want to end up with in your DBF table.

Also when you first open the DBF table you might have to use the following command to prevent getting a Code Page dialogue window.

SET CPDIALOG OFF

Good Luck,
JRB-Bldr

 

[WIREMESH]

This is exactly what I want to do. How do I implement:

- Select the desired rows/columns
- Save AS your NewDBF using the DBF4 format

 

[JRB-Bldr]

Here is some code which was cut and pieced from various modules of working code, so I know that individually it works, but as a whole it has not been tested.

Regardless it should give you some guidelines.

* Begin By Opening Excel
  tmpsheet = CREATEOBJECT('excel.application')
  oExcel = tmpsheet.APPLICATION

  * --- Set Excel to only have one worksheet ---
  oExcel.SheetsInNewWorkbook = 1

  * --- Delete the Default Workbook that has 3 worksheets ---
  oExcel.Workbooks.CLOSE

  * --- For DEBUG Purposes - If Not Needed, Set .F. ---
  oExcel.VISIBLE = .T.  && Might be useful to see operation first time or two

  * --- Now Open Desired Excel File ---
  oExcel.Workbooks.OPEN(mcExcelFile)  && Fully Pathed Excel File Name
  oExcel.WINDOWS(mcExcelFName).ACTIVATE  && JUSTFNAME portion of mcExcelFile

  xlBook = JUSTFNAME(oExcel.ActiveWorkbook.FULLNAME)  && oExcel.ActiveWorkbook.FULLNAME
  xlSheet = oExcel.activesheet
  oExcel.ReferenceStyle = 1  && Ensure Columns in A-B Reference Format

* Select Range of Rows & Columns:
  * --- Go To Desired Worksheet within Workbook ---
  oExcel.Sheets("Summary").SELECT  && In This Case Worksheet Named 'Summary'
  xlSheet = oExcel.activesheet

  * --- Determine Last Cell At This Point ---
  mnLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
  mcLastRow = ALLTRIM(STR(mnLastRow))
  mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT
  mcLastCol = ALLTRIM(STR(mnLastCol))

  * --- Select Desired Range Example: Range("B6:J15").Select ---
  mcFirstCol = "C"
  mcFirstRow = "5"
  xlSheet.RANGE(mcFirstCol + mcFirstRow + ":" + mcLastCol + mcLastRow).SELECT

* To do the SaveAs:
  * --- Save Excel Results ---
  oExcel.CutCopyMode = .F. && Clear the clipboard
  oExcel.DisplayAlerts = .F.

  oExcel.activesheet.SAVEAS(mcOutputFile,11,0) && Fully Pathed DBF File Name

  * --- Close the Worksheet ---
  oExcel.Workbooks.CLOSE

  * --- Quit Excel ---
  oExcel.QUIT
  RELEASE oExcel, xlSheet

Good Luck,
JRB-Bldr

 

[Nifrabar]

From the top of my head:
Be aware of the fact that excel-columns must be wide enough to display all contents of the cells. Otherwise data may get truncated according the column-width of excel-sheet once saved as dbf.
-Bart