Thank you for all the ideas and opinions. (Now it works as desired)
Code: Select all
#NoEnv
#SingleInstance Force
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
/*
FileSelectFile FileName, 3, A_ScriptDir\, Open a file, Text Documents (*.csv; *.txt) ; choose a file to analyze
FileRead FileContent, %FileName%
If ErrorLevel
{ MsgBox 16, Row %A_LineNumber% -> %A_ScriptName%, % "The file could not be read (maybe locked)`nErrorLevel .: " ErrorLevel
ExitApp
}
; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, %FileContent%
*/
/*
FileContent =
( LTrim
"100";"Test ";"200";"abcdef";"0";"0";"0";"9";"100";"0";;"0";"20170401";"0"
"101";"Test2";"200";"999999";"0";"0";"0";"1";"0";"0";;"0";;"0"
)
*/
; FileContent = ; No delimiter - Give an Error message (no delimiter)
; FileContent := "1,5;2,5;3,5;4,5;5,5;6,5;7,5;8,5;9" ; "," and ";" as delimiter - Give an Error message (two delimiters)
; FileContent := "1,5 2,5 3,5 4,5 5,5 6,5 7,5 8,5 9" ; Tab and "," as delimiter - Give an Error message (two delimiters)
/*
; "," and ";" and " " as delimiter - Give an Error message (three delimiters)
FileContent =
( LTrim
0,001; 0,002; 0,003
0,004; 0,005; 0,006
0,007; 0,008; 0,009
)
*/
; FileContent = 302;"Emelie";"174,50"`n303;"Jenny";"19,90" ; ";" as delimiter
; FileContent = 302,"Emelie","174,50"`n303,"Jenny","19,90" ; "," as delimiter
; FileContent = "Emelie";"174,50";542`n"Jenny";"19,90";303 ; ";" as delimiter
; FileContent = "Emelie","174,50",542`n"Jenny","19,90",303 ; "," as delimiter
; FileContent = "19,90";Emelie;302;"Row 1"`n"18";Erica;201;"Row 2" ; ";" as delimiter
; FileContent = 302`t"Erica"`t"174,50"`t"asd" ; TAB as delimiter
; FileContent = 302 "Erica" "174,50" "asd" ; TAB as delimiter
; FileContent = 302 "Erica" "174,50" "asd" ; Space as delimiter
; FileContent = "19,90",Emelie,302,"Row 1"`n"18",Erica,201,"Row 2" ; "," as delimiter
FileContent = "19,90",Emelie,302,"Row 1"
MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "Delimiter " CheckDelim(FileContent)
ExitApp
CheckDelim(InputString)
{ ; Version 26 juni 2019
; Idea from Odlanir
; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=65648&p=282172#p282172
maskKO := Object()
ini := 0x02B0 ; Start of unicode Spacing modifier letters ( hopefully not present in the file )
; validDelim := ";,`t " ; <---- String of valid delimiter accepted ( can be modified by the user )
validDelim := ";,`t" ; <---- String of valid delimiter accepted ( can be modified by the user )
for i, v in StrSplit(validDelim)
maskKO.Push(chr(format("0x{:X}",ini+i))) ; build an array of substitute chars based on validDelim
StrLine := StrSplit(InputString, "`n", "`r")
If ( StrLine.Count() < 10 )
CheckNo := StrLine.Count()
else
CheckNo = 10
; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % StrLine[1] "`n`n" CheckNo
; ExitApp
; Loop Read, %fname% ; ten records should be enough to analize the file structure
Loop % CheckNo ; ten records should be enough to analize the file structure
{ ; if A_Index > 10
; Break
totRows := A_Index
; Avoid valid delimiters in quoted string
Result = ; Clear the result
for each, char in StrSplit(StrLine[A_Index])
{ if !inQuotes
{ if (char = """")
inQuotes := true
}
else
{ if (char ~= "[" validDelim "]")
{ ;~ result.modif := true
for k, v in StrSplit(validDelim)
char := RegExReplace(char, v, maskKO[k])
}
if (char = """")
inQuotes := false
}
;~ result.line .= char
StrVar .= char
}
}
StrVar := substr(RegExReplace(StrVar, """"),1,-1) ; no need qouted string in analysis
Obj := Object()
Loop Parse, StrVar
{ if ( RegExMatch(A_LoopField, "`n|`r") or RegExMatch(A_LoopField, "[^" validDelim "]")) ; grab only valid delimiters
Continue
if (!Obj.haskey(Asc(A_LoopField)))
obj[Asc(A_LoopField)] := 1
else
{ obj[Asc(A_LoopField)] += 1
MaxDLM := MaxDLM > obj[Asc(A_LoopField)] ? MaxDLM : obj[Asc(A_LoopField)] ; Max delimiter presence in file
}
}
;------------------------------------------------------------------------------------------------***
; save the delimiter only if is present in all record in equal or multiple count
;------------------------------------------------------------------------------------------------***
Candidate := Object(), cnt:= 0
for k, v in Obj
{ charX := ( (k < 32 or k > 127) and k <> 9 ) ? "N/A" : chr(k)
if ( Mod(v, totRows) = 0 and charX <> "N/A")
Candidate[++cnt] := chr(k)
}
if ( cnt = 0 )
{ ResDelim := " "
Loop % StrLen(validDelim)
{ Count += 1
DelimChr := SubStr(validDelim, A_Index , 1)
If Asc(DelimChr) = 9
DelimChr = {TAB}
If Asc(DelimChr) = 32
DelimChr = {Space}
If ( A_Index > 1 )
ResDelim .= DelimChr " "
else
ResDelim .= " " DelimChr " "
}
MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,
( LTrim
% "No one of the possible (" Count ") delimiters [" ResDelim "]
are present in the CSV-file `n
This program will be closed"
)
ExitApp
}
if ( cnt > 1 )
{ Loop % cnt
cntAll .= "Delimiter " A_Index " ( " Candidate[A_Index] " - chr(" asc(Candidate[A_Index]) "))`n"
MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,
( LTrim
% "This program can't automatically analyze the delimiter
" cnt " delimiters is present `n
" cntAll "
can't choose any of these, and this program will be closed."
)
ExitApp
}
Return % Candidate[1]
}