Getting a TypePad Export into a MySQL Database

I have a typepad export file and sucessufully got it into a Foxpro 7 table through data conversion.

I need to get it into an MySQL table. The problem is I can't export the Fox table into a csv or something the MySQL table can import because it has a memo field and that won't export.

I could have also tried to do the data conversion in MS SQL or possibly MySQL, but I don't know how to program loops. The code below shows the conversion program I used in foxpro to make a table.

The source file from typepad can be found here.
http://www.braynardwebdev.com/Unnamed_Comet_Asset.txt


SET SAFETY OFF
 
 
SELECT as_export
ZAP
 
 
SELECT as2
 
GO top
m.body = ''
 
DO WHILE NOT EOF()
 
	DO case
		CASE LEFT(main1, 6) = 'AUTHOR'
 
			m.credit = substr(main1, 9, 50)
			SKIP
		CASE LEFT(main1, 5) = 'TITLE' 
			m.title = substr(main1, 8, 100) 
			SKIP
		CASE LEFT(main1, 4) = 'DATE'
			m.pubdate = SUBSTR(main1, 13,4)+SUBSTR(main1, 7, 2) + SUBSTR(main1, 10, 2) + ALLTRIM(STR(VAL(SUBSTR(main1, 18, 2)) + IIF(RIGHT(ALLTRIM(main1), 2) = 'PM', 12, 0))) + SUBSTR(main1, 21, 2) + SUBSTR(main1, 24, 2)
			skip
		CASE LEFT(main1, 4) = 'BODY'
			SKIP
			DO WHILE LEFT(main1, 7) != 'EXCERPT'
				IF NOT EMPTY(main1) AND ALLTRIM(main1) != '-----' AND LEFT(main1, 8) != 'EXTENDED'
					m.body = m.body + ALLTRIM(main1) + ALLTRIM(main2) + ALLTRIM(main3) + ALLTRIM(main4) + ALLTRIM(main5) + ALLTRIM(main6) + ALLTRIM(main7) + ALLTRIM(main8) + ALLTRIM(main9) + ALLTRIM(main10) 
				ENDIF
				skip
			ENDDO 
		CASE LEFT(main1, 8) = '--------'
			m.uniquename = ALLTRIM(STR(INT(RAND()* 100000000)))
			m.catagory = 'g'
			m.publish = 'y'
			INSERT INTO as_export FROM memvar
			RELEASE ALL
			m.body = ''
			SKIP
		OTHERWISE 
			skip
	ENDCASE
 
ENDDO
 
SET SAFETY ON

Open in new window

FrylockAsked:
Who is Participating?
 
pcelbaCommented:
If you are able to connect to MySQL via ODBC connection then it is easy. Try following in FoxPro Command Window:

? SQLCONNECT()

It should ask for ODBC data source and if you select MySQL and then provide user and password you should be connected to the server. (This step supposes the ODBC data source definition in Control panel - ODBC data sources.)

The value returned by SQLCONNECT() will be used later as "Connection handle"

Suppose the table is created on the server already, so you may use  following code to export FoxPro table to MySql (you may add transaction, existing data removal from SQL table etc., just ask):
*-- Connect to MySQL
lnHandle = SQLCONNECT()
IF lnHandle < 0
  AERROR(laErr)
  ? "Error", laErr(1), laErr(2)
  RETURN
ENDIF
 
*-- select the proper database
lnResult = SQLEXEC(lnHandle, "USE YourDatabase")
IF lnResult < 0
  *-- Error
  AERROR(laErr)
  ? "Error", laErr(1), laErr(2)
  SQLDISCONNECT(0)
  RETURN
ENDIF
 
 
USE as_export 
SCAN ALL
  *-- You have to provide proper column names in the following command (ColX is SQL column, foxColX is appropriate column from FoxPro table, the questionmark must remain)
  lnResult = SQLEXEC(lnHandle, "INSERT INTO YourSQLTable (Col1, Col2, Col3, ...) VALUES (?foxCol1, ?foxCol2, ?foxcol3, ... )")
  IF lnResult < 0
    *-- Error processing
  ENDIF
ENDSCAN
 
*-- And the final data overview:
= SQLEXEC(lnHandle, "SELECT * FROM YourSQLTable", "cFoxTemp")
BROWSE
 
= SQLDISCONNECT(0)
USE IN as_export

Open in new window

0
 
FrylockAuthor Commented:
I don't see the part of your script where I enter my login and PW for the dtabase.

Foxpro is on a local machine. MySQL is on a webserver somewhere.
0
 
pcelbaCommented:
It is here:

lnHandle = SQLCONNECT()

SQLCONNECT() should ask for it.

Alternatively, if you know the connection string values, you may use SQLSTRINGCONNECT("ConnectionString")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
I would rather you use SQLSTRINGCONNECT so you don't have to configure the SQL on each workstation.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Cyril, if that is a one time export of data to MySQL, there's nothing wrong in simply using SQLCONNECT() and connect interactively, but in fact you still might need to try often enough, that a connectionstrin to MYSQL is handy. If you don't know it, look up how it should be at www.connectionstrings.com

Bye, Olaf.
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
I prefer to use SQLSTRINGCONNECT so I don't bother with ODBC connections. This way they are centralized and portable.
0
 
pcelbaCommented:
The discussion if to use SQLCONNECT() or SQLSTRINGCONNECT() is not important unitil we are not sure ODBC drivers are installed on that PC :-)
0
 
Olaf DoschkeSoftware DeveloperCommented:
Well, shouldn't be a problem to install that to get data from VFP to MySQL. Surely enough it's the easiest way to do that.

You CAN of course also output Memos to txt yourself with simpler functions as STRTOFILE or FPUTS. AFAIK there's a LOAD DATA INFILE command in MySQL, and despite a seperator for field values you can also define an escape char, eg \, so that texts enclosed in " can a) include an escaped " in the text eg as \" and b) can be multiline and still not interpreted as new row, the value ends with the closing " for the MySQL LOAD command, the record ends with the following line end only.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

So as you you now have the texts in a VFP memo, generate a new txt file MySQL can then import by that command. Use the TERMINATED BY 'string' eg as TERMINATED BY ',' to seperate values by comma, use ENCLOSED BY 'char', eg ENCLOSED BY '"' to have strings (eg memos) enclosed by ".

From the VFP side, you'd then need to STRTRAN(memo,["],[\"]) to have all " within the texts escaped. Now you do STRTOFILE() the single field values, convert every non string filed to strings with TRANSFORM. Take care MySQL understands date velus correctly. output all strings fields after you escaped them with STRTRAN enclosed within ".

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
...plus escape each escape char by douling it, eg escape a \ with \\. That's using a STRTRAN(text,'\','\\')

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.