Import Data From Excel Into MySQL in 2 Step

Filling the initial data in a MySQL table would be difficult if the data to be inputted lot and entered manually. But will ease if the data are already available in the form of Excel and with the help of MS Visual FoxPro course. The following will be shown how a simple way so that the data in Excel format can be imported into MySQL table format.

Step 1 : Retrieve data from Excel then enter into cursor

Prepare the data to Excel and save the file. Create a table in MySQL named it brg (id_ n(9), name_ c(100)).

Create a command button (IMPORT) and enter the following code :

lcNamaFile = GETFILE([XLS],[Excel File],[Open],0,[Import File Excel])
lcTempFile = Getenv("TEMP")+'\'+Sys(2015)+'.xls'
IF  EMPTY(lcNamaFile)
    RETURN
    ELSE
    #DEFINE Mess1 "Processing ..."
    WAIT WINDOW Mess1 NOWAIT TIMEOUT 0
    loExcel = Createobject("excel.application")

    loExcel.Workbooks.Open(m.lcNamaFile)
    loExcel.ActiveWorkbook.SaveAs(m.lcTempFile,6)
    loExcel.ActiveWindow.Close(.T.)


    create CURSOR tb_imp(id_ n(9),name_ c(100))


    Select tb_imp
    DELETE ALL 


    Append From (m.lcTempFile) Delimited

    loExcel.Quit


    SELECT tb_imp
    DELETE FROM tb_imp WHERE id_=0
    #DEFINE Mess2 "Process Complete"   
    WAIT WINDOW Mess2 TIMEOUT 1

    WAIT clear

    SET SAFETY ON
    MESSAGEBOX("Select Done to finish",0+64,"Information")
    ENDIF 

Step 2 : Entering data cursor into a MySQL table

Create a Command button (DONE) and enter the following code :

SELECT tb_imp
GO TOP 
   DO WHILE NOT EOF()
      SCATTER MEMVAR
      

      msql = "SET AUTOCOMMIT = 0;"
      IF SQLEXEC(Koneksi,msql) <= 0
         =MESSAGEBOX("Connection Failed", 0+16, "Information")
         RETURN
         ENDIF

      msql = "BEGIN;"
      IF SQLEXEC(Koneksi,msql) <= 0
         =MESSAGEBOX("Connection Failed", 0+16, "Information")
         RETURN
         ENDIF       

      msql='INSERT INTO brg (id_,name_)'+;

           'VALUES (?m.id_,?m.name_);' 
      IF SQLEXEC(Koneksi,msql) <= 0
         =MESSAGEBOX("Connection Failed", 0+16, "Information")
         RETURN
         ENDIF  

      
      msql = "COMMIT;"      
      IF SQLEXEC(Koneksi,msql) <= 0
         =MESSAGEBOX("Connection Failed", 0+16, "Information")
         RETURN
         ENDIF   
     

      SELECT tb_imp
      SKIP
      ENDDO 

You can see more details in the following examples

Tweet