Frylock
asked on
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
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 ONASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is here:
lnHandle = SQLCONNECT()
SQLCONNECT() should ask for it.
Alternatively, if you know the connection string values, you may use SQLSTRINGCONNECT("Connecti
lnHandle = SQLCONNECT()
SQLCONNECT() should ask for it.
Alternatively, if you know the connection string values, you may use SQLSTRINGCONNECT("Connecti
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Bye, Olaf.
I prefer to use SQLSTRINGCONNECT so I don't bother with ODBC connections. This way they are centralized and portable.
The discussion if to use SQLCONNECT() or SQLSTRINGCONNECT() is not important unitil we are not sure ODBC drivers are installed on that PC :-)
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.
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.
...plus escape each escape char by douling it, eg escape a \ with \\. That's using a STRTRAN(text,'\','\\')
Bye, Olaf.
Bye, Olaf.
ASKER
Foxpro is on a local machine. MySQL is on a webserver somewhere.