Convert SQLite database to VFP9 tables

[jonblack]

I have several large Sqlite3 databases that need to be converted into VFP tables and finally to SQLServer tables.

My problem is that the Sqlite tables do not want to just carry over to VFP with a simple append or scatter gather. The data was not very well designed and it is truncating or garbling when brought over.

I tried several conversion tools but they all reported errors with multiple records.

I was able to export the data as Sql IMPORT commands but now I need to import them into the VFP tables. The one Import set is 157meg and cannot be run as a simple program.

--- INSERT INTO table VALUES(1679576722,'Wiebe','David','A','','','M.D.')

Is there a tool or utility that will take an SQL commands file and run each command from within a VFP program? Is there a better way that I am not seeing?

Any assistance is appreciated.

Jon Black

 

[Olaf Doschke]

What version of VFP are you using? There was a 64K limit for single PROCEDURES/FUNCTIONS or scripts like this with only INSERT statements. VFP9 should be able to handle this.

On the other side, this is not a good approach.

If you need to go through VFP you use either remote views, SPT or CA query SQLite into VFP cursors and append them to DBFs, perhaas after some preprocessing, as you say the table design isn't that well.

You say values are truncated, then what fields make problems? VFP does for example only support up to 254 chars in char() fields, but you can select larger char fields to VFP memo fields and later in SQL Server have longer char fields again or Varchar(MAX) of course.

And why not import from SQLite directly into SQL Server?

Bye, Olaf.

 

[Mike Lewis]

Jon,

Rather than trying to find "a utility that will take an SQL commands file and run each command from within a VFP program", it would be better to read the data directly into VFP.

What you need is an ODBC driver for SQLLite. I don't know off hand where to get one, but it is certain that such drivers exist and are probably easily available for download. Just do a search.

Once you have the driver, it is an easy matter to use VFP to create a remote view, which will bring the data directly into your VFP aplication. Alternatively, you can use VFP's SQL pass-through functions, that is, SQLCONNECT(), SQLEXEC() and SQLDISCONNECT() to bring the data into a local VFP cursor, from where you can manipulate it to your heart's content.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

You have two options.

1. Convert the data to VFP data tables and then do all future work in those tables.

2. Set up your VFP application to use the existing SQLLite tables as the 'back-end' and continue to use them for future work. This would be much the same as those of us who regularly use MS SQL Server as a 'back-end' for our data.

Based on a Google search for SQLLite odbc driver, it appears that you could find a SQLLIte ODBC driver at

http://www.ch-werner.de/

If you decided that your choice needed to be #1, then you could do as Mike suggests above and use VFP to write your own 'converter' utilizing the SQLLite ODBC driver to access the desired data and then save it into VFP data tables.

Alternatively you could possibly get the data through an intermediary application such as Excel (from the same Google search above - see

http://help.lockergnome.com/office/SQLite-Excel-ODBC--ftopict704125.html)

Good Luck,
JRB-Bldr

 

[jonblack]

Update: I decided to dump the data out and import it directly to new tables in VFP9. I had to use the ~ as a seperator but it did the job.

I setup a script that will refresh the VFP files in about 15 minutes. There are a few processing steps that are needed to cleanse the data but it works alright.

I looked at ODBC but the drivers were too slow. Good for smaller tables but not for larger (5mil records) ones.

Jon