Manage "blank" rows in a csv file Topic is solved

[Albireo]

I want to handle a number of CSV files ("," comma separated files).
Trying to find a good way to handle these on.

My wish .:

• Delete any rows that lack information in all fields.
• Give a message if a row has the wrong number of fields.

(It may be that a field has been incorrectly described
for example 24,56 instead of "24,56" and I want to draw attention to this.)

Background .:
(A CSV-file can be from a few rows to maybe 50,000 rows)
Encode = UTF-8
csvFile = the name of the CSV-file.

If I want to read the headlines I use .:

Code: Select all

FileEncoding %vEncode%	; Encoding for FileReadLine and Loop Read
		FileReadLine outLine, % csvFile, 1

And to read the rest of the file I use this instructions.

Code: Select all

	Loop Read, %csvFile%
	{	Loop Parse, A_LoopReadLine, CSV
		{

But how to know that the row is empty? (the content of the different fields is not as structured as in the examples)

Some examples
example 1. (row 3 is empty)

Head1,Head2,Head3
info1,info2,info3

InfoA,infoB,infoC

example 2. (row 3 is empty)

Head1,Head2,Head3
info1,info2,info3
,,
InfoA,infoB,infoC

example 3. (row 3 is empty)

Head1,Head2,Head3
info1,info2,info3
"","",""
InfoA,infoB,infoC

example 4. (row 3 is empty)

Head1,Head2,Head3,Head4,Head5,Head6
info1,info2,info3,,Info5,
"",,"",,"",""
InfoA,infoB,infoC,,,

example 5. (row 1 and row 4 is empty)

Head1,Head2,Head3,Head4,Head5,Head6
info1,info2,info3,,Info5,
"","",,,,""
InfoA,infoB,infoC,,,

Perhaps this example is impossible in all different cases.
But completely blank lines before the headline can occur.
example 6. (row 1, 4 and 6 is empty)
row 7 has the wrong number of fields

Head1,Head2,Head3,Head4,Head5,Head6
info1,info2,info3,,Info5,
,"","","","",
InfoA,infoB,infoC,,,
,,,,,
,,

[BoBo]

Well, example 6's row 1 isn't empty as it contains an EOL character! But you might end up with an empty line at the end of a text section (that is missing a trailing EOL).
Pretty sure that it can be easily sorted out using RegExMatch(). Nevertheless, here's a "workaround": viewtopic.php?p=464831#p464831

For "counting" the number of columns to identify a mismatch (expecting a confirmed unique/default delimiter) you could (ab)use MsgBox % StrSplit(A_LoopField,",").Count()!=6 ? "This line sucks!" : "Muito Bueno!"

But that won't handle a missing column-delimiter while one of the other cells contains a 'false friend' ie. '24,56' that would add up to a "correct" amount of cells again.

Which delimiter is finally used? https://www.autohotkey.com/docs/commands/LoopParse.htm#ExDelimiter

[Albireo]

Thank you!

You have right about "EOL" but is not counted by strLen()
The instruction Loop Parse, A_LoopReadLine, CSV is not read a row from the CSV-file with only EOL.
I'm not sure how to use your other suggestions in my case.

Can add additional information .:
a)
The first thing that happens in the "real" program, is that all headings are placed in an array aHeader,
which makes it easy to check how many fields the CSV-file contains. fieldCount: = aHeader.count()

b)
The CSV-files are always "," (comma separated) to be able to use the CSV as delimiter in AHK (works very good)

Wrote a simplified test program to more easily test ideas and suggestions.
To test my various examples, just modify Gosub.
for example Gosub ex1 tests example 1, Gosub ex5 tests example 5 and so on.
The correct fieldCount (number of columns) has been specified for each example below.

Code: Select all

#SingleInstance, Force

csvFile := A_ScriptDir "\exampleFile.csv"

; - - -   initiate csvFile   - - - - - - - 
vEncode := "UTF-8" 
FileEncoding %vEncode%	; Encoding for FileReadLine and Loop Read

if FileExist(csvFile)
	FileDelete % csvFile

Gosub ex1

FileAppend % csvField1, %  csvFile, % vEncode
; - - -   csvFile is created   - - - - - - - 


; - - - - Handle empty rows - - - - -
Loop Read, % csvFile
{	tmp := A_Index
	if (strLen(A_LoopReadLine) < 1)
		Continue	; Ignore rows without content
		
	; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % A_LoopReadLine
	Loop Parse, A_LoopReadLine, CSV
	{	res .= tmp "`n"
		; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % A_LoopField
	}
	res .= "`n"
}
MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % res

ExitApp


; Example1
ex1:
	csvField1 =
	( LTrim
		Head1,Head2,Head3
		info1,info2,info3
	
		InfoA,infoB,infoC
	)
	fieldCount = 3
Return


; Example2
ex2:
	csvField1 =
	( LTrim
		Head1,Head2,Head3
		info1,info2,info3
		,,
		InfoA,infoB,infoC
	)
	fieldCount = 3
Return



; Example3
ex3:
	csvField1 =
	( LTrim
		Head1,Head2,Head3
		info1,info2,info3
		"","",""
		InfoA,infoB,infoC
	)
	fieldCount = 3
Return


; Example4
ex4:
	csvField1 =
	( LTrim
		Head1,Head2,Head3,Head4,Head5,Head6
		info1,info2,info3,,Info5,
		"",,"",,"",""
		InfoA,infoB,infoC,,,
	)
	fieldCount = 6
Return


; Example5
ex5:
	csvField1 =
	( LTrim
		Head1,Head2,Head3,Head4,Head5,Head6
		info1,info2,info3,,Info5,
		"","",,,,""
		InfoA,infoB,infoC,,,
	)
	fieldCount = 6
Return


; Example6
ex6:
	csvField1 =
	( LTrim

		Head1,Head2,Head3,Head4,Head5,Head6
		info1,info2,info3,,Info5,
		,"","","","",
		InfoA,infoB,infoC,,,
		,,,,,
		,,
	)
	fieldCount = 6
Return

[BoBo]

You have right about "EOL" but is not counted by strLen()

Probably, bc you're getting each line extracted using A_LoopReadLine and that is stripping any EOL before you start parsing that line?

Code: Select all

#SingleInstance, Force

csv = 
(LTrim

a,b,c,d

)

Loop, Parse, csv, CSV
   MsgBox % StrLen(A_LoopField) "|" A_LoopField "|"

This is returning a length of 2 for a and d regardless that they should be counted as "single" characters, but StrLen() is adding the leading/trailing EOL to them. But as said, that might be different if Loop, Parse is embedded within a Loop, Read.

Code: Select all

csv = 
(

"1","2,5","3","4"
"","",""
,,,
)

Loop, Parse, csv, `n
   Msgbox   % StrSplit(A_LoopField,"""").Count()=0 
            ? StrSplit(A_LoopField,"""").Count() 
            : Round((StrSplit(A_LoopField,"""").Count()-1)/2)

Another workaround until a RegEx-solution pops up - that should validate the number of cells per row, if its content is "quoted" correctly.

[Albireo]

Thank you!
The handling of "blank rows" was more difficult than I thought.

A small modification of the wish.
If field 1 is missing and one of the other fields has some content, the event must be noted (in some way)

I created the following test script .:

Code: Select all

#SingleInstance Force

csv = 
(

"1","2,5","3","4"
"bla","bla","bla "bla" bla",
"","",""
"",34
,,,

)

Loop Parse, csv, `n
{	MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "Before the filter for blank rows`nA_Index .: " A_Index "`n`nstrLen . .: " strLen(A_LoopField) "`n|" A_LoopField "|"

	flagField := false
	field1 := ""
	loop parse, A_LoopField, CSV, """"
	{	fieldCount := A_Index
		If ( strLen(A_LoopField) > 0 )
			flagField := true	; any field exist
		
		If A_Index = 1
			field1 := A_LoopField	; Save the first field for errorhandling
	}
	If !flagField	; No fields exist
		Continue
	
	; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "A_Index .: " A_Index "`nflagField .: " flagField "`nfield1 .: " field1
	If flagField and (strLen(field1) = 0)
	{	MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "Error handling!`n`n Field 1 is missing .: " A_LoopField
		Continue
	}
	
	MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "After compare`nA_Index .: " A_Index "`n`nstrLen . .: " strLen(A_LoopField) "`nnumber of fields . .: " fieldCount "`nA_LoopField .:`t |" A_LoopField "|"
}

ExitApp

I do not know if it works in all cases, and hope all comparisons are fast (can be many 100,000 compares in one run)

[BoBo]

"bla","bla","bla "bla" bla", You're able to catch this? Otherwise, it means that your "quote"-regime/spec gets busted. IMHO you can't handle any weird content setting and shouldn't allow it in the first place. Btw, "Quoting 'quoted' quotes" has to be done this way or in a similar fashion, JM2€ents

[LazarouJoinery]

Hi Albireo;

My advices:
- maybe consider ObjCSV.ahk?
I wrote this for it;
maybe it will help you
viewtopic.php?f=6&t=101791
- maybe consider Excel?
viewtopic.php?f=6&t=103500

[Albireo]

Suppose I know the field separator (in this example as ; )
Then every rows with only the following characters " and/or ; should be able to be skipped / deleted?
Then check if anything is left. (if nothing is left, the row is empty)

Edit .:
How to write a RegexMatch() that removes all field dividers ( ; in this case) and " ?
(or is it better to use StrSplit() ) ?
end Edit

Comments!

"bla","bla","bla "bla" bla", You're able to catch this? Otherwise, it means that your "quote"-regime/spec gets busted. IMHO you can't handle any weird content setting and shouldn't allow it in the first place. Btw, "Quoting 'quoted' quotes" has to be done this way or in a similar fashion, JM2€ents

I know it's difficult. but only the CSV option in AHK can handle this. (that's why I want to have all my CSV files with , (comma) as separator.
But the challenge is that many of the source files have ; as separator, because in Sweden we have , (comma) as decimal point.

...My advices:
- maybe consider ObjCSV.ahk?
I wrote this for it;
maybe it will help you
viewtopic.php?f=6&t=101791 ....

I have tried to understand how your proposal could be used in my case (but failed)

At the beginning of the link there are 3 + 4 points that try to explain what can be done.
(but I do not understand a single of the points - sorry)
One example point 1 loads from txt, instead of working with files.
I understand loads from txt (maybe the source is a textfile)
but Instead of working with files ??? What does this want to convey to me?
( I don't use Excel - I use LibreOffice. )

---

The main issue in this case was to handle blank lines. (after that the result was placed in an array)
If I know which field separator is used in the source file.
(or it's always converted to , as field separator)

the function .: CheckDelim()

Code: Select all

CheckDelim(StrLine, FileName := "")
{	; Version 30 juni 2019
	; Idea from Odlanir
	; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=65648&p=282172#p282172
	; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=65967&p=284389&hilit=delimiter#p284389
	; Information .:
	; - The FileName is only used for error handling.
	; - If validDelim ("[;,`t ]") is changed - maybe error handling also must be changed?
	; - This Function is Check / Manage CSV-data (StrLine) on both Strings and Objects
	; - The delimitation check is performed on up to 5 rows (The value of MaxRow is specified in the program)
	; - Empty rows are cleared at the beginning of the string / object (Max MaxRows from StrLine)
	;
	; Restrictions .:
	; - Can't handle CSV-files with different number of fields in the records
	;------------------------------------------------------------------------------------------------***
	
	Global Support	; Only for error handling
	
	MaxRows = 5	; Maximum number of rows on which the delimitation control is performed
	;~ validDelim := "(*UCP)[^[:alnum:]]"
	; 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 )
	Obj := Object()
	
	If ( !IsObject(StrLine) )	; If StrLine is a string (not object) - Convert StrLine to an Object
		StrLine := StrSplit(StrLine, "`n", "`r")
	
	
	;------------------------------------------------------------------------------------------------***
	; Only check the first five rows (MaxRows) of content.
	; Remove empty rows, less than 3 characters, in the object (StrLine) Create an new object (InputData)
	DataIndex := 0
	InputData := Object()
	For LineIndex, Line In StrLine
	{	LineCount := LineIndex
		If ( StrLen(Line) > 2 )	; The sortest string with delimiter, is more than two characters?
		{	DataIndex += 1
			InputData[DataIndex] := Line
			If (DataIndex = MaxRows)
				Break
		}
	}
	
	
	;------------------------------------------------------------------------------------------------***
	; Search for possible delimiters - Skip characters within quotation marks
	Loop % InputData.Count()
	{	totRows := A_Index
		
		; Avoid valid delimiters in quoted string
		for each, Char in StrSplit(InputData[A_Index])
		{	if !inQuotes
			{	if (Char = """")
				{	inQuotes := true
					Continue
				}
			
				if ( char ~= validDelim )
				{		if (!Obj.haskey(Asc(Char)))
						obj[Asc(Char)] := 1
					else
						obj[Asc(Char)] += 1
				}
			}			
			else
			{	if (Char = """")
					inQuotes := false
			}
		}
	}
	
	
	;------------------------------------------------------------------------------------------------***
	; Save the delimiter only if is present in all record in equal or multiple count
	Candidate := Object(), cnt:= 0
	for k, v in Obj
	{	if ( Mod(v, totRows) = 0 ) 
			Candidate[++cnt] := chr(k) 
	}
	
	
	;------------------------------------------------------------------------------------------------***
	; Error Handling
	If (cnt <> 1)
	{	; Delimiter not found 
		if ( cnt < 1 )
		{	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 "  "
			}
			If ( FileName <> "" )
			{	SplitPath FileName, OutFileName, OutDir
				FileInfo := "File name .: " OutFileName "`nPath .: " OutDir
			}

			MsgBox 64, Row %A_LineNumber% -> %A_ScriptName%,
			( LTrim Comments
			%	"No one of the possible (" Count - 2 ") delimiters " ResDelim "	; (Count - 2) do not count parentheses
				are present in the CSV-data. (" InputData.Count() " row(s) is checked) 
				(one error can be - different number of fields in some record) `n
				" FileInfo "`n
				This program will be closed `n
				Contact .: " Support
			)
			MsgBox ,,, The program closes, 1
			ExitApp
		}
		

		; Several delimiters exist as suggestions, and this program could make the selection. All possible Delimiters are displayed
		if ( cnt > 1 )
		{	names := {"`t":"Tab" , ",":"Comma", ";":"Semicolon", " ":"Space"}
			strOUT := "Found this " Cnt " delimiters.`n"
			for each, delim in Candidate
				strOUT .= "           " delim "`t" names[delim] "`n"
			; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % strOUT "`n" Cnt
			
			Loop % cnt
				cntAll .= "Delimiter " A_Index " ( " Candidate[A_Index] " - chr(" asc(Candidate[A_Index]) "))`n"
			MsgBox 64, Row %A_LineNumber% -> %A_ScriptName%,
			( LTrim 
			%	"This program can't automatically analyze the delimiter. (" MaxRows " row(s) is checked)
				" strOut "can't choose any of these. `n
				This program will be closed `n
				Contact .: " Support
			)
			MsgBox ,,, The program closes, 1
			ExitApp
		}
	}
	
	Return % Candidate[1]
}

the function .: ChangeDelim()

Code: Select all

; -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
; -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
ChangeDelim(aInputCSV, aSysProg, DelimFrom, ByRef FieldNo, DelimTo := ",")
{	; Version 24 juli 2019
	; aString := ChangeDelim(CSVData, DelimFrom, CountField, DelimTo)
	; aString := ChangeDelim(CSVData, DelimFrom, CountField)
	; 
	; Both Strings and Arrays can be managed.
	; Change the field delimiters From ?? to ","
	; Manages both to change the delimiter, and the same delimiter as the result
	;
	; All CSV-files must be checked / handled
	; If no fields with content could be found, give an Error message.
	; Skip all lines without characters (< 2 characters)
	; Skip lines with only field delimiters and quotes characters.
	; Don't handle fields with many quotes (like this .: ;""Test"";
	; Assumes that all strings are delimited with quotation marks, especially strings with delimiters in the text eg. price.
	; If number of fields is equal in all rows, it is not checked (summa / antal godkända poster/rader = jämt antal?)
	;
	; An array of rows is created in Return
	; Also the number of fields is returned from the function
	; Only the first field is checked)
	; Do not handles delimiters with more than one character
	
	; Support förväntas vara en array Support.namn, Support.telefon, Support.epost
	Global Support
	
	FuncName := "ChangeDelim(aInputCSV, aSysProg, " DelimFrom ", ByRef " FieldNo ", " DelimTo " )"	; Only for error handling
	; MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%, % FuncName
	
	DelimFromNo := StrLen(DelimFrom)
	DelimToNo := StrLen(DelimTo)
	If ((DelimFromNo <> 1) OR (DelimToNo <> 1))
	{	MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%,
		( LTrim
		 %	"Fältdelare med fler än ett tecken kan inte hanteras! `n
			Fältdelare från  " DelimFrom "  (" DelimFromNo " tkn.)
			FältDelare till  " DelimTo "  (" DelimToNo " tkn.) `n
			Programnamn .: " A_ScriptName "
			Funktion .: " FuncName "`n
			Kontakta .: " Support.namn " - " Support.telefon "`n
			Detta program måste avslutas!"
		)
		MsgBox ,,, Programmet avslutas!, 1
		ExitApp
	}		
	
	arrayCSV := {}
	If ( !IsObject(aInputCSV) )	; If InputCSV is a string (not object) - Convert aInputCSV to an Object
		aInputCSV := StrSplit(aInputCSV, "`n", " `t`r")
	; MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%, % aInputCSV[1] "`n" aInputCSV[2] "`n`n" aInputCSV.Length()
	; MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%, % DelimFrom "`n" DelimTo "`n" aInputCSV.1
	
	For LineNo, Line in aInputCSV
	{	If ( StrLen(Line) < 2 )	; Do NOT process completely empty lines
			Continue
			
		NewInputCSV = 
		CharPos = 1
		CountField = 0
		CountQuotes = 0
		For CharNo, Char in StrSplit(Line)
		{	if !inQuotes
			{	; Check for START quote character
				if ( Char = """" )
				{	inQuotes := True
					CountQuotes += 1
					Continue
				}
				
				; Check if the character is a delimiter
				if ( Char = DelimFrom )
				{	If ( DelimFrom <> DelimTo )	; If the delimiter is to be changed
						NewInputCSV .= SubStr(Line, CharPos, A_Index - CharPos) DelimTo
					CharPos := A_Index + 1	; Next field begin
					CountField += 1
					
					; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % vText "`n`nCharPos .: " CharPos "`nCountField .: " CountField "`n`n" NewInputCSV "`n`nDelimFrom .: " DelimFrom "`nDelimTo .: " DelimTo
				}
			}
			else
			{	; Check for END quote character
				if ( Char = """" )
				{	inQuotes := False
					CountQuotes += 1
				}
			}
		}
		
		; Create the result string
		; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % vText "`nCountField .: " CountField "`n`nCountQuotes .: " CountQuotes "`n`nNewInputCSV `n" NewInputCSV "`n- " StrLen(NewInputCSV)
		NewInputCSV .= SubStr(Line, StrLen(NewInputCSV) + 1 , StrLen(Line) - StrLen(NewInputCSV) + 1 )
		; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % "`n- " CountField "`n`n- " CountQuotes "`n`n- " StrLen(NewInputCSV)
		If ( StrLen(NewInputCSV) > (CountField + CountQuotes + 2) )
		{	arrayCSV.Push( NewInputCSV )
			RowOK += 1
			If RowOK = 1
			{	FieldNo := CountField + 1	; Räkna antal fält i första godkända sträng!
				; MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%, % NewInputCSV "`n`nGodkänd! ( " FieldNo " )"
			}
		}
	}
	
	; StopTime := A_TickCount
	; RunTime := A_TickCount - StartTime
	; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % "Input CSV .: `n" aInputCSV[1] "`n`nThe new CSV .: `n" NewInputCSV "`n`n`nRun time .: " RunTime " ms"
	; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % arrayCSV.1
	; MsgBox ,, Rad.: %A_LineNumber% -> %A_ScriptName%, % CountField
	
	; If no fields with content could be found
	If ( FieldNo < 1 )
	{	MsgBox 64, Rad.: %A_LineNumber% -> %A_ScriptName%,
		( LTrim Join
		 %	"Fält saknas i CSV-variabeln! `n
			Funktion .: " FuncName "
			Programnamn .: " A_ScriptName "`n
			Variabel .: aInputCSV[] `n`n
			Kontakta .: " Support.namn " - " Support.telefon "`n
			Detta program måste avslutas!"
		)
		MsgBox ,,, Programmet avslutas!, 1
		ExitApp
	}
	Return % arrayCSV
}
; -  -  -   S l u t   p å   C h a n g e D e l i m   -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  
; -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

[BoBo]

@Albireo
Determines which delimiter was encountered...
https://www.autohotkey.com/docs/commands/LoopParse.htm#
viewtopic.php?f=10&t=104590

Code: Select all

#SingleInstance, Force

delm  = ";"
ColNo =  5
row   =
   (LTrim Comment
   "a";"2.300,50€";"b";"Hello World";"3"  ; 1
   ""                                     ; 0
   ,,,,                                   ; 0
   ";                                     ; 0
   ","                                    ; 0
                                          ; 0
   "bla";'blu';"blo"                      ; 0
   "";"";"";""                            ; 1 (valid but skipped bc number of 'columns' < 5)
   "x";"y";"z";"@";"#"                    ; 1
   ) 

Loop, parse, row, `n
   {  if InStr(A_LoopField,delm)!=0
         filteredRows .= StrSplit(A_LoopField,delm).Count()=ColNo ? A_LoopField "`n" : ""
   }
MsgBox % filteredRows      
Return

HTH

[Albireo]

Thank you!
But the delimitercheck from the manual, only works with some csv-files.

... Determines which delimiter was encountered...
https://www.autohotkey.com/docs/commands/LoopParse.htm# ...

(It doesn't work on this cases)

"1,5;2,5;3,5;4,5;5,5;6,5;7,5;8,5;9"
"0,001"; "0,002"; "0,003"; "0,004"; "0,005"
"34,25",,"43,52"

And so on.

@BoBo shows a second example - it probably works perfectly because the delimiter is already known.

My questions still remains.
How to write a RegexMatch() that removes all field dividers ( ; in this case) and " ?
(or is it better to use StrSplit() ) ?

[BoBo]

But the delimitercheck from the manual, only works with some csv-files.

Well, you gave yourself the answer already - bc there are too many possible irregularities.

My questions still remain.
How to write a RegexMatch() that removes all field dividers ( ; in this case) and " ?

Tricky for the above reason. How to decide what is in fact a divider or part of a cell's content? If those are fixed valid settings you wouldn't need RegEx-stuff but only StrReplace(), right?

(or is it better to use StrSplit() ) ?

I don't think so. While it can be (ab)used to do some basic pattern detection, it's more error-prone and less flexible bc it's not meant to be used that way. Go 4 RegEx!

[Albireo]

If I use this test data .:

Code: Select all

"";"";"";"";"";""
"1,5;2,5;3,5;4,5;5,5;6,5;7,5;8,5;9"
"0,001"; "0,002"; "0,003"; "0,004"; "0,005"; "0,006"; "0,007"; "0,008"; "0,009"
0,001; 0,002; 0,003
"";"";"";"";"";34

"1";"2,5";"3";"4";;
"bla";"bla";"bla "bla" bla";;;;
"";"";"";;
"34,25";;"43,52"
"";"34,25"
;;;

and run the function() CheckDelim() I got the result ;
CheckDelim() checks a maximum of 5 lines. - if there is still uncertainty, which delimiter has ben used, an error message is displayed.

If the field divider is changed from ; to , with the function() ChangeDelim() (above)
Gave the following results (with the empty rows removed).:

Code: Select all

"1,5;2,5;3,5;4,5;5,5;6,5;7,5;8,5;9"
"0,001", "0,002", "0,003", "0,004", "0,005", "0,006", "0,007", "0,008", "0,009"
0,001, 0,002, 0,003
"1","2,5","3","4",,
"bla","bla","bla "bla" bla",,,,
"34,25",,"43,52"
"","34,25"

The only problem I found in this field divider conversion was .: from 0,001; 0,002; 0,003 to 0,001, 0,002, 0,003
(In this case it is not possible to split the fields with Loop parse, aString, CSV)
Will try to handle this, so the result will instead be: "0,001", "0,002", "0,003"

---

After analysis of the field divider that the CSV-file uses, it should not be difficult for someone who can use RegexMatch() to filter out, for example, a line like this. ""; ""; ""; "" (if the field divider is ; )

[Albireo]

Thank you!

Have not done so many different tests, but since the field divider is known, the solution was the following. (the idea by flyingDman)
this solution is not speed tested, eg if 50,000 rows and many fields are to be processed. - Most rows have content - How long will it take?)

Code: Select all

#Singleinstance force
csv =
( LTrim

	"";"";"";"";"";""
	"1";"2,5";"3";"4";;
	"";"";"";"";"";3
	"";"";"";;;
	"bla";"bla";"bla "bla" bla";;;

	123;"some text" 125 "abcdef";"174,50";asd";"asd;
	0,001; 0,002; 0,003; 0,004; 0,005; 0,006
	"0,001"; "0,002"; "0,003"; "0,004"; "0,005"; "0,006"
	"1,5;2,5;3,5;4,5;5,5;6,5;7,5;8,5;9"
	"34,25";;"43,52"
	;;;;"";"34,25"
	;;;
)
Delim := ";"
for x, y in strsplit(csv,"`n")
	if (y ~= "[^" Delim """""]" )
		ncsv .= y "`n"
msgbox % ">>" rtrim(ncsv,"`n") "<<"    ; << and >> included to show we're catching the start and the end of the string