none
Converting DBF to XLS

    Question

  • Hi everyone,

     

    I am totally new to databases and foxpro, and I have the following question: I need to convert a txt file (tab delimited) into a xls file. I was able to this however, the resulting xls file contains a header I don't want. The txt file looks like:

     

    ***   RESULTS   ***

    Columname_1   Columname_2   Columname_3

    2.3   4.5   7.5

    7.1   45.6   863.34

     

    The resulting xls file now looks like:

     

    col_1   col_2   col_3

    ***   RESULTS   ***

    Columname_1   Columname_2   Columname_3

    2.3   4.5   7.5

    7.1   45.6   863.34

     

     

    I would like to get rid off the first (and possibly the second) line in the xls file. How do I do this? Thanks for your help.

     

    The piece of code I'm using looks like the following:

     

    USE ddf_dir + '\results_reserve_recon_structure.dbf'
    dbf_output_file = output_dir + 'reserve_recon.dbf'

    excel_output_file = 'reserve_recon.xls'

    excel_output_file_ext = excel_output_file + '.xls'

    excel_sheet_name = 'results_reserve_recon'
    excel_sheet_name_ext = 'results_reserve_recon.xls'

     

    COPY STRUC TO &dbf_output_file

    CLOSE ALL

    USE &dbf_output_file
    APPEND FROM 'output.txt' DELIMITED WITH CHAR TAB

    ON ERROR RESUME NEXT
    DELETE FILE &excel_output_file_ext

    COPY TO &excel_sheet_name TYPE XL5
    RENAME &excel_sheet_name_ext TO &excel_output_file_ext

    CLOSE ALL

    DELETE FILE &dbf_output_file

    Tuesday, December 16, 2008 7:55 AM

Answers

  • You don't need to get the text into a dbf to put into excel. You can directly put in excel:

     

    _Cliptext = Filetostr('output.txt')

     

    oExcel = Createobject("Excel.application")

    With oExcel

     .workbooks.Add && Add a new workbook

     .DisplayAlerts = .F.

      With .ActiveWorkBook.Activesheet

         .Name = 'results_reserve_recon'

         .Range("A1").PasteSpecial()

         .Rows(1).Delete

      ENDWITH

      .Activeworkbook.SaveAs( FULLPATH( 'results_reserve_recon.xls' ) )

      .Quit

    Endwith

     

    Tuesday, December 16, 2008 5:23 PM

All replies

  • o=CREATEOBJECT("excel.application")

    x=o.Workbooks.Open(excel_output_file_ext) && put your file name here

    x.ActiveSheet.Rows(1).Delete && delete first row

    x.ActiveSheet.Rows(1).Delete && delete second row

    o.ActiveWorkbook.Save() && check the correct syntax here.

     

    Tuesday, December 16, 2008 11:01 AM
  • Hi,

     

    You can use the following code

    exlobj=CREATEOBJECT("excel.application")

    =excl.Workbooks.Open(GETFILE("xls"))

    wrkobj.ActiveSheet.Rows(1).Delete && will delete the first row

    wrkobj.ActiveSheet.Rows(1).Delete && will delete the second row

     

    exlobj.Visible=.t.

     

     

    You can write the following code to save and close the Excel file programatically

     

    wrkobj.Saveas("xx.xls") && to give a particular name

    or

    wrkobj.Saveas()&& to ask for file name

     

    wrk.Close

     

    Tuesday, December 16, 2008 11:18 AM
  • You don't need to get the text into a dbf to put into excel. You can directly put in excel:

     

    _Cliptext = Filetostr('output.txt')

     

    oExcel = Createobject("Excel.application")

    With oExcel

     .workbooks.Add && Add a new workbook

     .DisplayAlerts = .F.

      With .ActiveWorkBook.Activesheet

         .Name = 'results_reserve_recon'

         .Range("A1").PasteSpecial()

         .Rows(1).Delete

      ENDWITH

      .Activeworkbook.SaveAs( FULLPATH( 'results_reserve_recon.xls' ) )

      .Quit

    Endwith

     

    Tuesday, December 16, 2008 5:23 PM
  •  

         DEAR :

                    BARBADOS

                             

                                           INSTEAD OF USING  " COPY  TO "  REPLACE WITH  " EXPORT  TO"

     

     

                                           EXAMPLE: FOR CONVERSION FROM DBF TO EXCEL

                                        

                                             USE ZAZA.DBF

                                              EXPORT TO  ZAZA.XLS   TYPE XLS

     

                                               FOR ALL VERSIONS  OF FOXPRO   FROM  2  -  VFP8

      

                                                                                                          EGYPT: AZIZ  SALLAM
    Thursday, December 18, 2008 4:26 AM
  •      

                          BARBADOS:

                                            CODE SAMPLE    EXAMPLE:

                                                         

                                                                  USE ZAZA.DBF

                                                                   DELETE RECORD 1

                                                                   PACK

     

                                                                  EXPORT  TO BARBADOS.XLS TYPE XLS

     

    Thursday, December 18, 2008 4:31 AM
  •  abd el aziz sallam wrote:

     

         DEAR :

                    BARBADOS

                             

                                           INSTEAD OF USING  " COPY  TO "  REPLACE WITH  " EXPORT  TO"

     

     

                                           EXAMPLE: FOR CONVERSION FROM DBF TO EXCEL

                                        

                                             USE ZAZA.DBF

                                              EXPORT TO  ZAZA.XLS   TYPE XLS

     

                                               FOR ALL VERSIONS  OF FOXPRO   FROM  2  -  VFP8

      

                                                                                                          EGYPT: AZIZ  SALLAM
    Thursday, December 18, 2008 7:18 PM