I'm SOO close to having an amazingly cool script for running sql on a text file but just can't get this to work. I'd be incredibly grateful if someone could point out my (surely) naïve error.
Spoiler
#SingleInstance, Force
#NoEnv
;~ GoSub RegistryCheck
GoSub CreateFiles
GoSub Headers
GoSub BuildQuery
return
RAlt::
Browser_Forward::Reload
;~ RControl::
;~ Browser_Back::
CreateFiles: ;********************Create tsv example file and schema ini file********************************.
Folder := A_ScriptDir . "\" ;Grab folder
FileName:= "joes.txt" ;Grab file name (will do programatically later
FileDelete, Joes.txt
TSVFile=
(
STAKEHOLDER_NUM EMAIL CUST_REGION_CODE COUNTRY_NAME EMAILING_READY_FLAG
3232`[email protected]`tEurope`tFrance`tY
2122`[email protected]`tEurope`tGermany`tN
2522`[email protected]`tEurope`tFrance`tN
1522`[email protected]`tUS`tUS`tN
)
FileAppend, %TSVFile%, Joes.txt, UTF-8
FileDelete, schema.ini
Schema=
(
--Leave first row blank--
[joes.txt]
Format=TabDelimited
)
FileAppend, %Schema%, schema.ini
Return
BuildQuery: ;***********************Build gui for getting sql query********************************.
Gui, Add, Text, x3 y5 , Select
Gui, Add, Edit, r3 x+4 y3 vSelect_Vars w850 , Distinct %Heads%
Gui, Add, Text, x3 y+10 , WHERE
Gui, Add, Edit, r8 X0 y+4 vWhere w900 ,
Gui, Add, Button, x820 y55 w50 h20 , Submit
Gui, Show, w900 h800, Selection GUI
return
ButtonSubmit: ;***********************;clear some variables and submit the new sql code********************************.
LV_Delete()
Loop, % LV_GetCount("Column")
LV_DeleteCol(1)
sData:= ""
pfields:=""
sData:=""
Where:="" , Query:="" , Select_Vars:="" , sData:=""
GuiControlGet, Select_Vars
GuiControlGet, Where
If (Where <>"") ;If there is a where clause- add "where " to the statement
Where:= " Where " . Where
Query.="Select " . Select_Vars . " From [" . FileName . "]" . Where
Gui, Submit, NoHide
GoSub Connect
GoSub ReturnedItems
return
GuiClose:
Gui Destroy
return
Headers: ;***********************Grab headers from file planning to work on********************************.
FileReadLine, Header, %Folder%%FileName%,1
Headers := StrSplit(Header, A_tab) ;~ MsgBox % headers[4]
DDH:=DelimitArray(Headers, Delim:="|",Remove_Last:=1)
Heads:= DelimitArray(Headers, Delim:=", ",Remove_Last:=1)
return
Connect: ;***********************Connect to Text file using ADO & text driver********************************.
objConnection:= ComObjCreate("ADODB.Connection")
objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0`;Data Source=" . Folder . "`;Extended Properties =""text;HDR=YES;FMT=Delimited;CharacterSet=65001;""`;")
objRecordSet:= ComObjCreate("ADODB.Recordset")
objRecordset.Open(Query, ObjConnection)
;***********************loop through data and put it together in a flat file********************************.
New_Delimiter:=","
pFields := objRecordset.Fields
While !objRecordset.EOF {
Loop, % pFields.Count{
pField:= pFields.Item(A_Index-1)
If (A_Index=pFields.Count)
sData .= pField.Value
Else
sData .= pField.Value . New_Delimiter
}
Rows_Count:=A_index ; get count of rows
sData .="`r"
objRecordset.MoveNext()
}
objRecordset.Close()
objRecordset:=
MsgBox,,%Rows_Count%, % "this works fine each time however the LV_add doesn't referesh the second time through`n`n"Clipboard:=sData
return
ReturnedItems: ;*****************Build ListView and update GUI********************************.
Gui, Add, ListView, x3 w900 h600, %DDH%
OutputDebug DEBUG: Line %A_LineNumber%: %A_Index% and Var is: %sData%
loop, parse, sData, `r
{
Sub:= StrSplit(A_LoopField,",")
LV_Add(,Sub*) ; uses variadic variable to adjust for however many columns there are
}
LV_ModifyCol() ;adjust column based off data instead of header
return
;***********************Functions********************************.
DelimitArray(Array,Delim=",",Remove_Last="1"){
for each, item in Array
string .= item Delim
if (Remove_Last=1)
StringTrimRight, string, string, strlen(delim) ;remove extra Delimiter
return string
}
#NoEnv
;~ GoSub RegistryCheck
GoSub CreateFiles
GoSub Headers
GoSub BuildQuery
return
RAlt::
Browser_Forward::Reload
;~ RControl::
;~ Browser_Back::
CreateFiles: ;********************Create tsv example file and schema ini file********************************.
Folder := A_ScriptDir . "\" ;Grab folder
FileName:= "joes.txt" ;Grab file name (will do programatically later
FileDelete, Joes.txt
TSVFile=
(
STAKEHOLDER_NUM EMAIL CUST_REGION_CODE COUNTRY_NAME EMAILING_READY_FLAG
3232`[email protected]`tEurope`tFrance`tY
2122`[email protected]`tEurope`tGermany`tN
2522`[email protected]`tEurope`tFrance`tN
1522`[email protected]`tUS`tUS`tN
)
FileAppend, %TSVFile%, Joes.txt, UTF-8
FileDelete, schema.ini
Schema=
(
--Leave first row blank--
[joes.txt]
Format=TabDelimited
)
FileAppend, %Schema%, schema.ini
Return
BuildQuery: ;***********************Build gui for getting sql query********************************.
Gui, Add, Text, x3 y5 , Select
Gui, Add, Edit, r3 x+4 y3 vSelect_Vars w850 , Distinct %Heads%
Gui, Add, Text, x3 y+10 , WHERE
Gui, Add, Edit, r8 X0 y+4 vWhere w900 ,
Gui, Add, Button, x820 y55 w50 h20 , Submit
Gui, Show, w900 h800, Selection GUI
return
ButtonSubmit: ;***********************;clear some variables and submit the new sql code********************************.
LV_Delete()
Loop, % LV_GetCount("Column")
LV_DeleteCol(1)
sData:= ""
pfields:=""
sData:=""
Where:="" , Query:="" , Select_Vars:="" , sData:=""
GuiControlGet, Select_Vars
GuiControlGet, Where
If (Where <>"") ;If there is a where clause- add "where " to the statement
Where:= " Where " . Where
Query.="Select " . Select_Vars . " From [" . FileName . "]" . Where
Gui, Submit, NoHide
GoSub Connect
GoSub ReturnedItems
return
GuiClose:
Gui Destroy
return
Headers: ;***********************Grab headers from file planning to work on********************************.
FileReadLine, Header, %Folder%%FileName%,1
Headers := StrSplit(Header, A_tab) ;~ MsgBox % headers[4]
DDH:=DelimitArray(Headers, Delim:="|",Remove_Last:=1)
Heads:= DelimitArray(Headers, Delim:=", ",Remove_Last:=1)
return
Connect: ;***********************Connect to Text file using ADO & text driver********************************.
objConnection:= ComObjCreate("ADODB.Connection")
objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0`;Data Source=" . Folder . "`;Extended Properties =""text;HDR=YES;FMT=Delimited;CharacterSet=65001;""`;")
objRecordSet:= ComObjCreate("ADODB.Recordset")
objRecordset.Open(Query, ObjConnection)
;***********************loop through data and put it together in a flat file********************************.
New_Delimiter:=","
pFields := objRecordset.Fields
While !objRecordset.EOF {
Loop, % pFields.Count{
pField:= pFields.Item(A_Index-1)
If (A_Index=pFields.Count)
sData .= pField.Value
Else
sData .= pField.Value . New_Delimiter
}
Rows_Count:=A_index ; get count of rows
sData .="`r"
objRecordset.MoveNext()
}
objRecordset.Close()
objRecordset:=
MsgBox,,%Rows_Count%, % "this works fine each time however the LV_add doesn't referesh the second time through`n`n"Clipboard:=sData
return
ReturnedItems: ;*****************Build ListView and update GUI********************************.
Gui, Add, ListView, x3 w900 h600, %DDH%
OutputDebug DEBUG: Line %A_LineNumber%: %A_Index% and Var is: %sData%
loop, parse, sData, `r
{
Sub:= StrSplit(A_LoopField,",")
LV_Add(,Sub*) ; uses variadic variable to adjust for however many columns there are
}
LV_ModifyCol() ;adjust column based off data instead of header
return
;***********************Functions********************************.
DelimitArray(Array,Delim=",",Remove_Last="1"){
for each, item in Array
string .= item Delim
if (Remove_Last=1)
StringTrimRight, string, string, strlen(delim) ;remove extra Delimiter
return string
}