Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting a TypePad Export into a MySQL Database

Posted on 2009-04-30
9
Medium Priority
?
610 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:Frylock
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 1800 total points
ID: 24271513
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
 

Author Comment

by:Frylock
ID: 24273259
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24273963
It is here:

lnHandle = SQLCONNECT()

SQLCONNECT() should ask for it.

Alternatively, if you know the connection string values, you may use SQLSTRINGCONNECT("ConnectionString")
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 27

Assisted Solution

by:CaptainCyril
CaptainCyril earned 200 total points
ID: 24277291
I would rather you use SQLSTRINGCONNECT so you don't have to configure the SQL on each workstation.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 24277518
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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 24277585
I prefer to use SQLSTRINGCONNECT so I don't bother with ODBC connections. This way they are centralized and portable.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24277610
The discussion if to use SQLCONNECT() or SQLSTRINGCONNECT() is not important unitil we are not sure ODBC drivers are installed on that PC :-)
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 24278231
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 24278270
...plus escape each escape char by douling it, eg escape a \ with \\. That's using a STRTRAN(text,'\','\\')

Bye, Olaf.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question