(Solved) Extracting Data from CSV files

[ahcahc]

Hi, I have a .csv file that has two delimiters, a comma (,) for the cell, and a quotation mark "" for text fields that can contain commas. My script works until it encounters a comma within the text fields. My script gets the product IDs and image links and writes them to a new csv file with semicolon as delimiter. What should be my delimiter? Any help would be great thanks.

sample csv:

Code: Select all

Product ID,Model,Description,Images
Product123,"Model 123","Description, color, black","http://www.123.com/1.jpg"

notice the first entry is not enclosed in a quotation mark.

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

FileSelectFile, csv_file, 1,, Select CSV File, *.csv
FileDelete, UTF-8_Image_Links.csv

FileRead, csv, %csv_file%
loop,parse,csv,`r,`n
	{
	if (A_Index=1)
		FileAppend, id_product;Images URL, UTF-8_Image_Links.csv
	if (A_Index>=2)
		Loop, Parse, A_LoopField, csv
			{
			if (A_Index=1)	;Product ID
				FileAppend, `n%A_LoopField%;, UTF-8_Image_Links.csv
			else if (A_Index>=22 AND A_Index<31) ;images
				if (A_LoopField!="")
					FileAppend,%A_LoopField%`,, UTF-8_Image_Links.csv
			}
	}
ExitApp

[Blackholyman]

this may be of use

Code: Select all

csv =
(
Product ID,Model,Description,Images
Product123,"Model 123","Description, color, black","http://www.123.com/1.jpg"
)


loop, parse, csv, `n, `r
{
this_line := A_LoopField
pos := 1
While pos := RegExMatch(this_line, "O)""(.*?)""", M, pos+StrLen(m[0]))
    this_line := RegExReplace(this_line, m[1], RegExReplace(m[1], ",", "¢")) 
msgbox % this_line
}

This will replace comma's within quotation marks with ¢

Then you just stringReplace the ¢ after the parse

[trismarck]

else if (A_Index>=22 AND A_Index<31) ;images - this condition resolves to true only when the loop is dealing with columns 22 to 31 of every line in the csv file. The sample content of the csv file at the top of the post only has four columns, so statements under the condition are never executed.
Perhaps providing more examples on what the desired input/output should be, would help.
As for the delimiter, the standard delimiter for Comma-Separated-File is a comma, so perhaps it would be possible to use the comma (and enclose URLs containing the comma inside of double quotes), since the comma already works in the input csv file.

//edit: to parse the comma-delimited contents of a text field, another loop could be used:

Code: Select all

FileEncoding, UTF-8

;FileSelectFile, csv_file, 1,, Select CSV File, *.csv
FileDelete, UTF-8_Image_Links.csv

;FileRead, csv, %csv_file%
csv =
(Join`r`n
Product ID,Model,Description,Images
Product123,"Model 123","Description, color, black","http://www.123.com/1.jpg"
)
loop,parse,csv,`r,`n
{
    if (A_Index=1)
	{
        FileAppend, id_product;Images URL, UTF-8_Image_Links.csv
	}
    if (A_Index>=2)
	{
        Loop, Parse, A_LoopField, csv
		{
            if (A_Index=1)  ;Product ID
			{
                FileAppend, `n%A_LoopField%;, UTF-8_Image_Links.csv
			}
            ;else if (A_Index>=22 AND A_Index<31) ;images
            else if (A_Index=3) ;description
			{
				Loop, Parse, A_LoopField, `,
					MsgBox, % A_LoopField
			}
		}
	}
}
ExitApp

[ahcahc]

Thanks guys! The problem was fileappend was unable to write some lines since the loop is going so fast and my harddrive probably cant keep up. I had to first store the data in a variable then fileappend later after the loop is complete.

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

FileSelectFile, csv_file, 1,, Select CSV File, *.csv
FileDelete, UTF-8_Image_Links.csv

data:=
Loop, Read, %csv_file%
{
if (A_Index=1)
	data:= "id_product;Images URL" ;first line
if (A_Index>=2)
	Loop, Parse, A_LoopReadLine, csv
		{
		if (A_Index=1)
			data := % data . "`n" . A_LoopField ";" ;product id, 1st column
		else if (A_Index>=22 AND A_Index<31 AND A_LoopField!="") ;images, 2nd column
			data := % data .  A_LoopField . ","
		}
}
FileAppend, %data%, UTF-8_Image_Links.csv
ExitApp

[trismarck]

Thanks guys! The problem was fileappend was unable to write some lines since the loop is going so fast and my harddrive probably cant keep up.

That's unlikely, as the script executes commands in sequence and waits before each command has finished. There seems to be no difference between the two scripts, other than using Loop, Read vs Loop, Parse, `r, `n. Using FileAppend multiple times will slow the script down (vs using a variable) as the file is opened/closed during each FileAppend, but the slowdown shouldn't really matter as of _what_ is stored inside of the file. Perhaps the format of the newlines in the file had something to do with that? (Mac/Unix/DOS).

The other interesting part is: should % be allowed after := (thanks):

Code: Select all

data := % data .  A_LoopField . ","

[ahcahc]

Well, when using fileappend inside the loop (loop,parse,csv,`r,`n and loop, read) the output file has different number of lines (a lot are missing) every time it is executed. My input csv file has around 200+ lines the out has around 90 lines. I then MsgBox the current line and contents just before every fileappend operation, it correctly show which line and what contents, I press&hold esc key to close the rest of the msgboxes. The the output file now has significantly more lines but some are still missing. So this is why I came to the conclusion that some of the fileappend operation where overridden by another fileappend operation before it can write to the file or the file was still locked by the previous fileappend operation.

[trismarck]

Perhaps this happened because the file with previous results wasn't deleted and after adding the msgbox, the file contained the previous and the current results (that would be around 180 lines).

I could check what's wrong if I'd get a link to

• that file and
• the failing AHK code

in a private forum message (if you don't want to post the entire file here). I promise I'll delete the file after testing.

If you decide to upload the files, please upload the files themselves (vs copy-paste text to a service like pastebin.com). If the files are confidential, it could be a good idea to pack them into a zip archive with a password.

[ahcahc]

Thanks. I think I'll just use the variable before fileappend.

[trismarck]

Ok, so just for the reference, I was wrong on one thing. There is a case where FileAppend would _not_ open/close the file every time. That case is Loop, Read, InputFile, OutputFile.