How to deal with csv comma delimiters that also have comma's inside ?

[WalkerOfTheDay]

Hi, I have a comma delimeted csv file which also contains comma's that are not actual delimiters.
How do you deal with this ?

The fields that contain the comma's that are not delimiters have a quote in front and after the text.
So for example:

Code: Select all

#1000,someone@somebody.com,"this contains a comma, that shouldn't be use as a delimeter",bla bla bla

How would you parse this so it stores it into an array like so:

Code: Select all

#1000
someone@somebody.com
this contains a comma, that shouldn't be use as a delimeter  <----- contains a comma
bla bla bla

[just me]

If field values of CSV records contain the field separator, the fields are commonly 'masked' (i.e. encapsulated by double-quotes). It's common CSV format and can be processed with Loop, Parse, String, CSV:

Code: Select all

#NoEnv
CsvLine = #1000,someone@somebody.com,"this contains a comma, that shouldn't be use as a delimeter",bla bla bla
LineArr := []
Loop, Parse, CsvLine, CSV
   LineArr[A_Index] := A_LoopField
FieldCount := LineArr.Length()
For Index, Value In LineArr
   MsgBox, 0, Field %A_Index% of %FieldCount%, %Value%
ExitApp

See also: Translate PowerShell to AHK - possible?

[WalkerOfTheDay]

Hi I've been playing around with this and I'm noticing something strange (at least to me).

Please find my (working) code below.

The issue I'm having is, when I replace If Instr(Value, Orderno)

with

if (Value := Orderno)

It only gives the value of the top row of the file with if instr() included it skips through each line containing my searchstring,
like intended. However I don't want to use instr because it would also give data when only inputting one digit.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
FileEncoding, UTF-8

InputBox, Orderno, Ordernumber, Ordernumber to find:

LineArray := [] ; start with an empty Array

Loop, read, orders_export.csv
{
	LineNumber = %A_Index%
	Loop, parse, A_LoopReadLine, CSV
		LineArray[A_Index] := A_LoopField
	For Index, Value In LineArray
	{
		If Instr(Value, Orderno) ; <--------------- IF CHANGE THIS, STUFF GOES WRONG
			MsgBox, % "Ordernumber: " LineArray[1] "`nStatus: " LineArray[3] "`nSubtotal: " LineArray[9]
				. "`nShipping " LineArray[10] "`nLineNumber " LineNumber
	}
}

[JoeWinograd]

You want this:

if (Value = Orderno)

Not this:

if (Value := Orderno)

Regards, Joe

[WalkerOfTheDay]

You want this:

if (Value = Orderno)

Not this:

if (Value := Orderno)

Regards, Joe

Yes, that's what I thought too, but when I do that the MsgBoxes are ignored completely.

[just me]

Which field of your sample file holds the order number and what term do you search for?

[WalkerOfTheDay]

Which field of your sample file holds the order number and what term do you search for?

Hi just me,

The very first one, 'Name' contains the ordernumbers.

edit
I just realized I'm an idiot I never included the #-sign when seaching

[DRocks]

Thanks this works fine!
Here's another example for others to refer to:

Drag and drop a .csv on Gui to create a global csv.fileName array / object
or link a file directly to the getCSV_CONTENT() function

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance, Force ; Prevent double execution of this script
#Persistent

Gui, Add, Text,, Drop a .csv file on this window
Gui, Show

;csv:={} ; create empty object
;csv:=GetCSV_CONTENT("C:\Users\Alex\Downloads\releve.csv")
return

;--------------------------------------------------------------------------------
GuiDropFiles(GuiHwnd, FileArray, CtrlHwnd, X, Y) {
;--------------------------------------------------------------------------------
;To temporarily disable drag-and-drop for a window,
; remove the WS_EX_ACCEPTFILES style via Gui -E0x10. 
; To re-enable it later, use Gui +E0x10.
;[v1.1.20+]: If GuiDropFiles is a function, the parameters are as shown in the example below. CtrlHwnd is blank if files were dropped on the GUI itself. FileArray is an array (object) of filenames, where FileArray[1] is the first file and FileArray.MaxIndex() returns the number of files. A for-loop can be used to iterate through the files:
    GLOBAL csv
	
	if !csv
		csv:={} ; create new object
	
	for i, file in FileArray
	{
		SplitPath, file,,,,RawFileName
		fileName:=RegExReplace(RawFileName, "\W")
		RawFileName:=""
		MsgBox File %i% is:`n%file% `n`nObject Name: %fileName%
		
		csv[fileName]:={} ; create new dimension for this file
		csv[fileName]:=GetCSV_CONTENT(file)
		MsgBox % csv[fileName,2,1]
	}
}

;--------------------------------------------------------------------------------
GetCSV_CONTENT(ByRef file) {
;--------------------------------------------------------------------------------
	2D_array:={} ; create empty array
	
	if (file) {
		FileRead, CSV_CONTENT, %file%
		if (!CSV_CONTENT) 
		{
			MsgBox % "No CSV content found."
			return
		}
		Loop, Parse, CSV_CONTENT, `n,`r ; rows
		{
			if (A_LoopField) ; skips empty rows
			{
				row++
				Loop, Parse, A_LoopField, CSV ; cols
				{
					col:=A_Index
					2D_array[row,col]:=A_LoopField
				}
			}
		}
	}
	
	return 2D_array
}