Easiest way to continually import data to MySQL from a dbf file on my local computer

Question

I have a problem that has been annoying me for quite some time now and a few days ago I started googling for a solution, but I haven't really gotten anything to work. I've read a little about something called SSIS, but I'm not sure it does what I'm looking for or if there is something else I should research in order to accomplish my goal. This is my problem:

My accounting program produces and updates a .dbf file with information about all vouchers and places it in a folder on my local computer. Our MySQL must continually be updated with this information. So this is what I do twice a day:

• I open up the .dbf file in excel
• Save it as a .csv.
• Close Excel
• Open the file in notepad++
• Convert the formating to utf8
• Save
• log in to MySQL
• Go to the right table
• Upload the .csv
• Replace the old data with the new

As this takes quite a bit of time, I feel that there must be better ways to do this. It would be great if I could have this scheduled to be done automatically or if there is some kind of an SQL query that could do this, because then I could use PHP to make a website that I could enter and have the query run when I press a button or something.

So my question is: What is the most simple way to continually get the info from the .dbf file into my SQL server?

Answer 1

There is a way to do your job by shedule with DBF Commander Pro's command-line interface. Use the following command in a *.BAT file:

dbfcommander.exe -edb <dbf_file_name> <server_table_name> <connection_string>

After that, create a shedule for this BAT file using Windows Sheduler. The only issue remains, that you need to clear the destination table on MySQL database before the export process.

In order to try the export process in app GUI, click 'File -> Export to DBMS'. In the window appears click Build button in order to build the connection string: select MS OLEDB Provider for MySQL Server, then choose your server from the list, provide login and password, select a database, click OK:

In the Export to DBMS window select the destination table you want to import source DBF file to, then click Export. The command line you need you can find at the bottom part of the window.

More info on import and export DBF to a database you can find here. Detailed using of command-line is here.

Answer 2

As you mention of doing in PHP. What is stopping you from doing it there.

You could create one connection handle using a VFPOleDB provider to open the path location of the table, open and read the table. Then have a SECOND connection to your MySQL database open and ready to push the data there.

Then, for each row read from the VFP OleDB connection result set, do whatever special cleansing you need to.

Then, query from the MySQL connection if its an existing entry or not and if an add or update is necessary, then send the data respectively.

Continue for the rest of the records from the VFP result set.

No need to open in Excel, save to CSV format, load yet another tool, etc...