Help with reading csv file Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Help with reading csv file

25 Oct 2020, 14:47

i need help with getting only the values of the columns "ID" and "CompanyID" in each row to a new csv file.

so according to my sample code the outcome of the new csv file should be:
1. 210269, SC12345678910
2, 210271, 456123

notes:
* the row numbers not constant
* csv file may have more than 2 rows

i managed to get only the "ID" values using "digit" ,
i would like to have a better method and as i requested getting the "CompanyID" values as well.

Code: Select all

FileDelete, c:\csvfile.csv
FileDelete, c:\new_csvfile.csv

; sample of csv file
FileAppend, 
(
ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123
), c:\csvfile.csv



Loop, Read, C:\csvfile.csv
{
	Column1 := StrSplit(A_LoopReadLine, ",").1
	
	if Column1 =
	{
		; skip
	}
	else if Column1 is digit
	{
		; getting only the "ID" values  :(
		FileAppend,
		(
        %Column1%,ComapnyID`n
		)
		, c:\new_csvfile.csv
	}
}

Thanks in advance :!:
User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: Help with reading csv file

25 Oct 2020, 15:05

Code: Select all

prop := [], field := [], old := "c:\testfile.csv", ttext := ""
FileRecycle, %old%

FileAppend,
(
ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123
), %old%

Loop, Read, %old%
{
 lineNum := A_Index
 Loop, parse, A_LoopReadLine, CSV
  If (lineNum = 1)
   field[A_Index] := StrReplace(A_LoopField, A_Space)
  Else prop[field[A_Index]] := A_LoopField
 If (lineNum > 1)
  ttext .= "`n" lineNum - 1 ", " prop.ID ", " prop.CompanyID
}
MsgBox, % SubStr(ttext, 2)
User avatar
Xtra
Posts: 2744
Joined: 02 Oct 2015, 12:15

Re: Help with reading csv file

25 Oct 2020, 15:31

Another Example:

Code: Select all

#NoEnv
SetBatchLines, -1
FileDelete, c:\csvfile.csv
FileDelete, c:\new_csvfile.csv

; sample of csv file
FileAppend,
(
ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123
), c:\csvfile.csv

FileRead, myCSV, c:\csvfile.csv

Loop, Parse, myCSV, `n, `r
{
	Line := A_LoopField , ID := CompanyID := ""
    Loop, Parse, Line, CSV
	{
	    Switch A_Index
		{
			Case 1:ID := A_LoopField
			Case 14:CompanyID := A_LoopField
			Default:continue
		}
	}
	newCSV .= ID . "," . CompanyID . "`n"
}
FileAppend, % RTrim(newCsV, "`n"), c:\new_csvfile.csv
Run, Edit c:\new_csvfile.csv
ExitApp
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file

25 Oct 2020, 15:49

Thanks mikeyww! its works fine!
problem is the the original csv i work with sometimes have more text outside the "table" at the beginning and end of the csv,
so the files csv looks like this:

Code: Select all

FileAppend,
(
text1
text2
text3
text4
text5

ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123


text6
text7
text8
text9
text10
), %old%
in this cases your code not working :(
Last edited by Tomer on 25 Oct 2020, 15:51, edited 1 time in total.
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file

25 Oct 2020, 15:51

Thanks Xtra !
but i get:
"This line does not contain a recognized action." :(
User avatar
Xtra
Posts: 2744
Joined: 02 Oct 2015, 12:15

Re: Help with reading csv file

25 Oct 2020, 15:53

Update your AHK to use switch requires [v1.1.31+]
User avatar
Xtra
Posts: 2744
Joined: 02 Oct 2015, 12:15

Re: Help with reading csv file

25 Oct 2020, 15:57

This will handle your new case:

Code: Select all

#NoEnv
SetBatchLines, -1
FileDelete, c:\csvfile.csv
FileDelete, c:\new_csvfile.csv

; sample of csv file
FileAppend,
(
text1
text2
text3
text4
text5

ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123
), c:\csvfile.csv

FileRead, myCSV, c:\csvfile.csv

Loop, Parse, myCSV, `n, `r
{
	Line := A_LoopField
	if InStr(Line, ",")
	{
		ID := CompanyID := ""
		Loop, Parse, Line, CSV
		{
			Switch A_Index
			{
				Case 1:ID := A_LoopField
				Case 14:CompanyID := A_LoopField
				Default:continue
			}
		}
		newCSV .= ID . "," . CompanyID . "`n"
    }
}
FileAppend, % RTrim(newCsV, "`n"), c:\new_csvfile.csv
Run, Edit c:\new_csvfile.csv
ExitApp
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file

25 Oct 2020, 16:03

Thanks Xtra its perfect !

Edit:
its still not handle this for example:

Code: Select all

; sample of csv file
FileAppend,
(
From: dsadasdas <dsadasd>,,,,,,,,,,,,,
"Sent: Sunday, April 07, 2019 10:22 AM",,,,,,,,,,,,,
To: dasdsadsada‏ <adsadasdas>; Tsdadi‏ <dasdasda>,,,,,,,,,,,,,
Subject: dasdasdas,,,,,,,,,,,,,
,,,,,,,,,,,,,

ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123

,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
ssssss,,,,,,,,,,,,,
"aaaa,",,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
ssss,,,,,,,,,,,,,
aaaa,,,,,,,,,,,,,
2143123213 ttt,,,,,,,,,,,,,
,,,,,,,,,,,,,
dsadasdasda,,,,,,,,,,,,,

), c:\csvfile.csv
Last edited by Tomer on 25 Oct 2020, 16:12, edited 2 times in total.
User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: Help with reading csv file

25 Oct 2020, 16:05

@Xtra always gives you extra.
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file

25 Oct 2020, 16:13

mikeyww wrote:
25 Oct 2020, 16:05
@Xtra always gives you extra.
Sure!
Thanks for your code share,
its really impressive!
User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: Help with reading csv file

25 Oct 2020, 16:42

You can replace the Instr line with whatever condition identifies your lines of interest. It might be like the following.

Code: Select all

If RegExMatch(Line, "^\d+?,")
You can decide based on whatever sort of text you are handling.
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file

26 Oct 2020, 10:27

mikeyww wrote:
25 Oct 2020, 16:42
You can replace the Instr line with whatever condition identifies your lines of interest. It might be like the following.

Code: Select all

If RegExMatch(Line, "^\d+?,")
You can decide based on whatever sort of text you are handling.
it works tnx ! :)
User avatar
Tomer
Posts: 366
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv file  Topic is solved

26 Oct 2020, 10:46

my final code thanks to @Xtra & @mikeyww

Code: Select all

#NoEnv
SetBatchLines, -1
FileDelete, c:\csvfile.csv
FileDelete, c:\new_csvfile.csv

; sample of csv file
FileAppend,
(
From: dsadasdas <dsadasd>,,,,,,,,,,,,,
"Sent: Sunday, April 07, 2019 10:22 AM",,,,,,,,,,,,,
To: dasdsadsada‏ <adsadasdas>; Tsdadi‏ <dasdasda>,,,,,,,,,,,,,
Subject: dasdasdas,,,,,,,,,,,,,
,,,,,,,,,,,,,

ID,SN,Type,Model,Model Company,Address,Adress2,Room,Username,IT Name,Phone,Service,Info,CompanyID
210269,111,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,"AAA,BBB,CCC,DDD",SC12345678910
210271,222,NNN,MMM,TTT,EEE,YYY1,L001,UUU,VVV,050-1234456,BBB,AAAAAAA,456123

,,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
ssssss,,,,,,,,,,,,,
"aaaa,",,,,,,,,,,,,,
,,,,,,,,,,,,,
,,,,,,,,,,,,,
ssss,,,,,,,,,,,,,
aaaa,,,,,,,,,,,,,
2143123213 ttt,,,,,,,,,,,,,
,,,,,,,,,,,,,
dsadasdasda,,,,,,,,,,,,,

), c:\csvfile.csv

FileRead, myCSV, c:\csvfile.csv

Loop, Parse, myCSV, `n, `r
{
	Line := A_LoopField
	If RegExMatch(Line, "^\d+?,")
	{
		ID := CompanyID := ""
		Loop, Parse, Line, CSV
		{
			Switch A_Index
			{
				Case 1:ID := A_LoopField
				Case 14:CompanyID := A_LoopField
				Default:continue
			}
		}
		newCSV .= ID . "," . CompanyID . "`n"
    }
}
FileAppend, % RTrim(newCsV, "`n"), c:\new_csvfile.csv
Run, c:\new_csvfile.csv
ExitApp


Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Chunjee and 141 guests