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
            .Workbooks.Open(m.lcFile)

            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.
            Else
                  lProceed = .T.
                  For lnloop = .Sheets.Count To 1 Step -1
                        If m.lnloop <> m.lnSheet
                              .Sheets(m.lnloop).Delete
                        Endif
                  Next
                  .ActiveWorkbook.SaveAs(m.lcTemp,39)  && Place it in the temp folder
                  .ActiveWindow.Close(.T.)
                  .Quit
            Endif
      Endwith
      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)
            Next
            Delete
            Browse Normal
            Close Databases All
            * Clean it up, delete temp xl5 file used
            Delete File (lcTemp)
      Else
            Messagebox("Aborted by user!",0+64,"Oppppssss!")
      Endif
Else
      Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif


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.

Enjoy!

4 comments:

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

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

      Delete
  2. Sir only 16384 rows importing

    ReplyDelete
  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.

    ReplyDelete