I have a csv file with around 2800 lines"
Format:
"#########";"ABCD";"#ABC";ABCDEFG";"ABCDEFGH";"2010/11/18 12:12:12"
This csv report gets replaced every hour and I wish to make it into an msql sql. Each time I need wipe current table and upload the new csv file as the old data is invalid (in the mysql table).
The MySQL table is designed: "ID","Column1", "Column2", "Column3", "Column4", "Column5", "Column6"
My test code:
Code: Select all
global gConnectionID
on mouseUp
local tFileName, tFileContents
answer file "Please choose a file to import" with type "Comma Separated Values|csv|CSV"
if the result is not "cancel" then
put it into tFileName
## Get the contents of the file
put URL ("file:" & tFileName) into tFileContents
connectToDB
set the itemDelimiter to ";"
put "TestTable" into tTableName
put "Column1, Column2, Column3, Column4, Column5, Column6" into tFields
revexecutesql gConnectionID,"BEGIN IMMEDIATE TRANSACTION" -- not using a separate function for this, just using the native stuff.
put the seconds into tStart -- track time
repeat for each line tLine in tFileContents
put item 1 of tLine into Column1
put item 2 of tLine into Column2
put item 3 of tLine into Column3
put item 4 of tLine into Column4
put item 5 of tLine into Column5
put item 6 of tLine into Column6
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4, :5, :6)" into tSQL
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, "Column1", "Column2", "Column3", "Column4", "Column5", "Column6"
end repeat
revexecutesql gConnectionID, "COMMIT"
closeDB gConnectionID
put the seconds - tStart -- show the time in milliseconds
answer tStart
end if
end mouseUp
1. I setup colum2 as an interger but it doesn't come over, same with the date. Any help would be great.
2. I noticed when I ran this code it took 4.30 min to successfully complete. That is pretty long I think, as I am not sure what to do with users if they try to use the table before it gets refreshed. Any insight would be great.
3. Also I don't need the first line of the csv file as it is just headings anwyay.
Thanks for your time.