HELP sorting Large CSV files

[Zedna]

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

[Zedna]

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