Zedna Posted July 28, 2011 Share Posted July 28, 2011 (edited) Good progress! Variant with 2 tables Global $aResult, $iRows, $iColumns ; group 1 _SQLite_Exec (-1, "BEGIN;") _SQLite_Exec (-1, "CREATE TABLE Symbol1 (DateTime TEXT, Open TEXT, Vol TEXT, SynthVIX Text, VWAP Text);") _SQLite_Exec (-1, "CREATE INDEX ix1 on Symbol1 (DateTime);") For $i = 1 to $loopCount1 Step 1 $lineRead1 = FileReadLine($infile1, $i) $Array1 = StringSplit($lineRead1, ",") $sql_cmd = "INSERT INTO Symbol1 (DateTime, Open, Vol, SynthVIX, VWAP) VALUES ('" & $Array1[1] & "', '" & $Array1[2] & "', '" & $Array1[3] & "', '" & $Array1[4] & "', '" & $Array1[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next ;~ ; group 2 _SQLite_Exec (-1, "CREATE TABLE Symbol2 (DateTime TEXT, Open TEXT, Vol TEXT, SynthVIX Text, VWAP Text);") _SQLite_Exec (-1, "CREATE INDEX ix2 on Symbol2 (DateTime);") For $i = 1 to $loopCount2 Step 1 $lineRead2 = FileReadLine($infile2, $i) $Array2 = StringSplit($lineRead2, ",") $sql_cmd = "INSERT INTO Symbol2 (DateTime, Open, Vol, SynthVIX, VWAP) VALUES ('" & $Array2[1] & "', '" & $Array2[2] & "', '" & $Array2[3] & "', '" & $Array2[4] & "', '" & $Array2[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next _SQLite_Exec (-1, "COMMIT;") _SQLite_GetTable2d (-1, "SELECT s1.*, s2.* FROM Symbol1 s1, Symbol2 s2 WHERE s1.DateTime = s2.DateTime ORDER BY s1,DateTime;", $aResult, $iRows, $iColumns) _ArrayDisplay($aResult) Variant with 1 table - recommend this one Global $aResult, $iRows, $iColumns _SQLite_Exec (-1, "BEGIN;") _SQLite_Exec (-1, "CREATE TABLE Symbol (Group INT, DateTime TEXT, Open TEXT, Vol TEXT, SynthVIX Text, VWAP Text);") _SQLite_Exec (-1, "CREATE INDEX ix1 on Symbol (DateTime, Group);") ; group 1 For $i = 1 to $loopCount1 Step 1 $lineRead1 = FileReadLine($infile1, $i) $Array1 = StringSplit($lineRead1, ",") $sql_cmd = "INSERT INTO Symbol (Group, DateTime, Open, Vol, SynthVIX, VWAP) VALUES (1,'" & $Array1[1] & "', '" & $Array1[2] & "', '" & $Array1[3] & "', '" & $Array1[4] & "', '" & $Array1[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next ;~ ; group 2 For $i = 1 to $loopCount2 Step 1 $lineRead2 = FileReadLine($infile2, $i) $Array2 = StringSplit($lineRead2, ",") $sql_cmd = "INSERT INTO Symbol (Group, DateTime, Open, Vol, SynthVIX, VWAP) VALUES (2,'" & $Array2[1] & "', '" & $Array2[2] & "', '" & $Array2[3] & "', '" & $Array2[4] & "', '" & $Array2[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next _SQLite_Exec (-1, "COMMIT;") _SQLite_GetTable2d (-1, "SELECT s1.*, s2.* FROM Symbol s1, Symbol s2 WHERE s1.DateTime = s2.DateTime AND s1.Group = 1 AND s2.Group = 2 ORDER BY s1.DateTime;", $aResult, $iRows, $iColumns) _ArrayDisplay($aResult) Edited July 28, 2011 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
Zedna Posted July 29, 2011 Share Posted July 29, 2011 (edited) Variant with 1 table - recommend this one Global $aResult, $iRows, $iColumns _SQLite_Exec (-1, "BEGIN;") _SQLite_Exec (-1, "CREATE TABLE Symbol (Group INT, DateTime TEXT, Open TEXT, Vol TEXT, SynthVIX Text, VWAP Text);") _SQLite_Exec (-1, "CREATE INDEX ix1 on Symbol (DateTime, Group);") ; group 1 For $i = 1 to $loopCount1 Step 1 $lineRead1 = FileReadLine($infile1, $i) $Array1 = StringSplit($lineRead1, ",") $sql_cmd = "INSERT INTO Symbol1 (DateTime, Open, Vol, SynthVIX, VWAP) VALUES ('" & $Array1[1] & "', '" & $Array1[2] & "', '" & $Array1[3] & "', '" & $Array1[4] & "', '" & $Array1[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next ;~ ; group 2 For $i = 1 to $loopCount2 Step 1 $lineRead2 = FileReadLine($infile2, $i) $Array2 = StringSplit($lineRead2, ",") $sql_cmd = "INSERT INTO Symbol2 (DateTime, Open, Vol, SynthVIX, VWAP) VALUES ('" & $Array2[1] & "', '" & $Array2[2] & "', '" & $Array2[3] & "', '" & $Array2[4] & "', '" & $Array2[5] & "');" _SQLite_Exec (-1, $sql_cmd) Next _SQLite_Exec (-1, "COMMIT;") _SQLite_GetTable2d (-1, "SELECT s1.*, s2.* FROM Symbol s1, Symbol s2 WHERE s1.DateTime = s2.DateTime AND s1.Group = 1 AND s2.Group = 2 ORDER BY s1.DateTime;", $aResult, $iRows, $iColumns) _ArrayDisplay($aResult) Here is optimization + column Group renamed to MyGroup because Group is SQL keyword (maybe the same should apply also for your other column with name DateTime) Global $aResult, $iRows, $iColumns _SQLite_Exec (-1, "BEGIN;") _SQLite_Exec (-1, "CREATE TABLE Symbol (MyGroup INT, DateTime TEXT, Open TEXT, Vol TEXT, SynthVIX Text, VWAP Text);") _SQLite_Exec (-1, "CREATE INDEX ix1 on Symbol (DateTime, MyGroup);") ; group 1 For $i = 1 to $loopCount1 Step 1 $lineRead1 = FileReadLine($infile1, $i) $lineRead1 = "'" & StringReplace($lineRead1, ",", "','") & "'" _SQLite_Exec (-1, "INSERT INTO Symbol (MyGroup, DateTime, Open, Vol, SynthVIX, VWAP) VALUES (1," & $lineRead1 & ");") Next ;~ ; group 2 For $i = 1 to $loopCount2 Step 1 $lineRead2 = FileReadLine($infile2, $i) $lineRead2 = "'" & StringReplace($lineRead2, ",", "','") & "'" _SQLite_Exec (-1, "INSERT INTO Symbol (MyGroup, DateTime, Open, Vol, SynthVIX, VWAP) VALUES (2," & $lineRead2 & ");") Next _SQLite_Exec (-1, "COMMIT;") _SQLite_GetTable2d (-1, "SELECT s1.*, s2.* FROM Symbol s1, Symbol s2 WHERE s1.DateTime = s2.DateTime AND s1.MyGroup = 1 AND s2.MyGroup = 2 ORDER BY s1.DateTime;", $aResult, $iRows, $iColumns) _ArrayDisplay($aResult) Edited July 29, 2011 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now