Tuesday, November 22, 2011

Importing from an xlsx file via automation Part II

Last time I created an entry in weblogs about the automation approach of importing an xlsx file because with the change of format on Excel 2007 onwards, VFP is generating an error now on importing it.  Since importing has no problem on lower versions of Excel files, the solution I have proposed is to save the sheet to a lower version, preferably XL5 format for backward compatibility reason.

Now, a new question is raised.  Though that approach works fast and is easy, how can we import from a specific worksheet inside the excel workbook?

If you use the approach that I have shown, it will always get only the first sheet.  Here is another trick though so we can do a selective import of an existing sheet inside that workbook.  And as a bonus due to another question raised inside Foxite Forum, here is also a way on how to replace the column names with the contents of the first row so Field Names will be better and proper instead of Field Name A, B, C.... and so on:

Local lcFile, lcTemp, loExcel As Excel.Application, lnSheet, lnloop, lcOldName, lcNewName, lProceed
lcFile = Getfile("xlsx")
If !Empty(m.lcFile)   && check if not cancelled
      lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"
      loExcel = Createobject("excel.application")
      With loExcel
            .DisplayAlerts=.F.  && somewhat similar to SET SAFETY OFF of VFP

            lnSheet = Int(Val(Inputbox("Please select the Target Sheet: 1-"+Transform(.Sheets.Count),"Select Sheet","1")))

            If Empty(m.lnSheet)   && check if not cancelled
                  lProceed = .F.
                  lProceed = .T.
                  For lnloop = .Sheets.Count To 1 Step -1
                        If m.lnloop <> m.lnSheet
                  .ActiveWorkbook.SaveAs(m.lcTemp,39)  && Place it in the temp folder
      If m.lProceed   && not Cancelled by user?
            Import From (m.lcTemp) Type Xls  && or XL5

            * Rename Field Names to better ones
            For lnloop = 1 To Fldcount()
                  lcOldName = Field(m.lnloop)
                  lcNewName = Strtran(Alltrim(Transform(&lcOldName)),' ','')
                  Alter Table Dbf() Rename Column &lcOldName To (m.lcNewName)
            Browse Normal
            Close Databases All
            * Clean it up, delete temp xl5 file used
            Delete File (lcTemp)
            Messagebox("Aborted by user!",0+64,"Oppppssss!")
      Messagebox("Aborted by user!",0+64,"Oppppssss!")

The trick I have used above follows:

  • Determine the number of sheets of the source excel file for importing
  • Choose which sheet you wanted to import
  • Delete the rest of the sheets not subject for importing
  • Save it on a temporary file in the local OS user's temporary folde
  • Import it to a table
  • Get the content of the first record and change the field names with those
  • Then finally delete the temporary file.
Of course, I am presuming that the first row of your excel sheet will contain possible valid Field Names later.  Since this is an immediate post (like most of my postings), I simply used STRTRAN() here for this tutorial but you can enhance that to really remove invalid characters.



  1. Sir where did the dbf go? the conversion is still in excel format not the dbf?

    1. The end result is in dbf and the location is dependent on your SET DEFAULT TO settings.

  2. Sir only 16384 rows importing

  3. If you are working with xlsx files and you need to translate them, I suggest to have a look at POEditor which is an online localization tool with lots of useful features (API, WordPress plugin, translation memory, GitHub and Bitbucket integration) that will make things easier for you.