Array conversion help Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
anahkuser
Posts: 3
Joined: 10 Jul 2020, 08:10

Array conversion help

10 Jul 2020, 08:28

Hi,

Wonder if someone could help me.

I have a data source in this format:

Code: Select all

$RECORD=!
-$0:NAME=Mark Johnsson
-$1:PHONE=987654321
-$2:[email protected]
-
$RECORD=!
-$0:NAME=Clara Hamill
-$1:PHONE=123456789
-$2:[email protected]
-
I need help with some array magic to make a csv file in below format:

Code: Select all

NAME;PHONE;EMAIL
Mark Johnsson;987654321;[email protected]
Clara Hamill;123456789;[email protected]
Then when more contact persons is added in Excel I want a way to read that csv file back into the data source format.
Note! It can be many more columns (like -$3: -$4: etc) and hundreds of records.

Many thanks in advance to any contributor.
Rohwedder
Posts: 3259
Joined: 04 Jun 2014, 08:33
Location: Germany

Re: Array conversion help

10 Jul 2020, 09:19

Hallo,
try:

Code: Select all

Source =
(
$RECORD=!
-$0:NAME=Mark Johnsson
-$1:PHONE=987654321
-$2:[email protected]
-
$RECORD=!
-$0:NAME=Clara Hamill
-$1:PHONE=123456789
-$2:[email protected]
-
)
Out = NAME;PHONE;EMAIL`n
Loop, Parse, Source, `n, `r
{
	StringSplit, Z, A_LoopField, :,-$
	IF Z1 is Number
		Out.= SubStr(Z2,1+InStr(Z2,"="))
	Else
		Continue
	IF (Z1 = 2)
		Out .= "`n"
	Else
		Out .= ";"
}
MsgBox,% Out
User avatar
boiler
Posts: 5640
Joined: 21 Dec 2014, 02:44

Re: Array conversion help

10 Jul 2020, 10:00

anahkuser wrote:
10 Jul 2020, 08:28
I want a way to read that csv file back into the data source format.

Code: Select all

CsvText =
(
NAME;PHONE;EMAIL
Mark Johnsson;987654321;[email protected]
Clara Hamill;123456789;[email protected]
)

OrigSource := ""
Lines := StrSplit(CsvText, "`n", "`r")
Headers := StrSplit(Lines.1, ";")
loop, % Lines.MaxIndex() - 1
{
	OrigSource .= "$RECORD=!`n"
	Record := StrSplit(Lines[A_Index + 1], ";")
	for Each, Field in Record
		OrigSource .= "-" A_Index - 1 ":" Headers[A_Index] "=" Field "`n"
	OrigSource .= "-`n"
}

MsgBox, % OrigSource
Rohwedder
Posts: 3259
Joined: 04 Jun 2014, 08:33
Location: Germany

Re: Array conversion help

11 Jul 2020, 04:29

@boiler
Replace:

Code: Select all

loop, % Lines.MaxIndex() - 1
by:

Code: Select all

While, Lines[A_Index+1]
then it does not bother if CsvText ends with line breaks.
teadrinker
Posts: 1737
Joined: 29 Mar 2015, 09:41
Contact:

Re: Array conversion help

11 Jul 2020, 04:49

Rohwedder wrote:

Code: Select all

Out = NAME;PHONE;EMAIL`n
What if the headings are not known in advance? ;)
anahkuser
Posts: 3
Joined: 10 Jul 2020, 08:10

Re: Array conversion help

11 Jul 2020, 06:32

teadrinker wrote:
11 Jul 2020, 04:49
Rohwedder wrote:

Code: Select all

Out = NAME;PHONE;EMAIL`n
What if the headings are not known in advance? ;)
Headings can be anything so cannot be hardcoded in my case. Name,phone was just an example.

I haven't got around to test it yet, but great help from Rohwedder and boiler. Thank you so much!
AHKStudent
Posts: 882
Joined: 05 May 2018, 12:23

Re: Array conversion help

11 Jul 2020, 06:39

anahkuser wrote:
11 Jul 2020, 06:32
teadrinker wrote:
11 Jul 2020, 04:49
Rohwedder wrote:

Code: Select all

Out = NAME;PHONE;EMAIL`n
What if the headings are not known in advance? ;)
Headings can be anything so cannot be hardcoded in my case. Name,phone was just an example.

I haven't got around to test it yet, but great help from Rohwedder and boiler. Thank you so much!
I think what mr @teadrinker was getting at is that it could be automatically detected when each line is parse as we know its located between : and =
teadrinker
Posts: 1737
Joined: 29 Mar 2015, 09:41
Contact:

Re: Array conversion help  Topic is solved

11 Jul 2020, 06:39

Maybe like this:

Code: Select all

text =
(
$RECORD=!
-$0:NAME=Mark Johnsson
-$1:PHONE=987654321
-$2:[email protected]
-
$RECORD=!
-$0:NAME=Clara Hamill
-$1:PHONE=123456789
-$2:[email protected]
-
)
m := csv := "", headers := {}, lines := [], i := 0, idx := 1
while RegExMatch(text, "m`aO)^-\$\d+:([^=]+)=([^\r\n]+)", m, m ? m.Pos + m.Len : 1) {
   if !headers.HasKey(m[1])
      headers[ m[1] ] := i++, lines[1]  .= m[1] . ";"
   lines[headers[ m[1] ] ? idx : ++idx] .= m[2] . ";"
}
for k, v in lines
   csv .= (csv = "" ? "" : "`r`n") . RTrim(v, ";")
MsgBox, % csv

source := ""
Loop, parse, csv, `n, `r
{
   if (A_Index = 1)
      headers := StrSplit(A_LoopField, ";")
   else {
      source .= "$RECORD=!`r`n"
      Loop, parse, A_LoopField, `;
         source .= "-$" . (A_Index - 1) . ":" . headers[A_Index] . "=" . A_LoopField . "`r`n"
      source .= "-`r`n"
   }
}
MsgBox, % RTrim(source, "`r`n")
anahkuser
Posts: 3
Joined: 10 Jul 2020, 08:10

Re: Array conversion help

03 Aug 2020, 08:57

Sorry for late reply due to summer break.

Thank you so much guys for your input. Much appreciated! :D

The suggestion from teadrinker works like a charm.

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, Burnsy, karkas, majstang, TAC109, WlodiXowy and 34 guests