loop

[MOKRANI Rachid [e-mail] [sed-users]]

Hi,

From an address file I would add the lat and long in this file.

Some conditions must be respected in the script.

It is important not to execute and don't try the script on the lines which the town AND country AND adress fields are empty
AND
Do not execute or try the script on the lines which lat and long value has already been registered.


# cat input.csv
firstname;lastname;adress;town;country;Goog_add;lt;lg
first1;lastname1;81 Kensington Gardens Square;london;uk;;;
firts2;lastname2;;;;;;
firts3;lastname3;14 cleveland row saint james;london;uk;14 Cleveland Row, St. James's, London SW1A 1DH, Royaume-Uni;51.5037514;-0.1397412
firts4;lastname4;4301-4399 3rd avenue brooklyn;New York;USA;4301-4399 3rd Ave, Brooklyn, NY 11220, USA;40.651233;-74.010647
firts5;lastname5;;;;;;
firts6;lastname6;place de la bastille;paris;france;;;


With the script below I can obtain an outcome, but it is insufficient.

# cat lg.sh
url="https://maps.googleapis.com/maps/api/geocode/json?address="
for i in $( awk -F ";" ' NR==1{next} {print $3 $4 $5 }' input.csv | egrep -o "[^,]+" | sed 's/\,$//g' | sed 's/\,/\, /g' | sed 's/[ ]/+/g' ) ; do
content="$(curl -s "$url/$i" | grep -m3 -E 'lat|lng|formatted_address' | sed "s/.*/;&/" | xargs | sed 's/formatted_address ://g' )"
echo -e "$content"
done


; 81 Kensington Gardens Square, London W2 4DJ, UK, ; lat : 51.5140226, ; lng : -0.1892987
; 14 Cleveland Row, St. James's, London SW1A 1DH, UK, ; lat : 51.5042408, ; lng : -0.1398204
; 4399 3rd Ave, Brooklyn, NY 11220, USA, ; lat : 40.6454789, ; lng : -74.01718509999999
; Place de la Bastille, Paris, France, ; lat : 48.8538045, ; lng : 2.3705269


I need the following output
cat input.csv
firstname;lastname;adress;town;country;Goog_add;lt;lg
first1;lastname1;81 Kensington Gardens Square;london;uk;81 Kensington Gardens Square, London W2 4DJ, UK, ;51.5140226;-0.1892987
firts2;lastname2;;;;;;
firts3;lastname3;14 cleveland row saint james;london;uk;14 Cleveland Row, St. James's, London SW1A 1DH, Royaume-Uni;51.5037514;-0.1397412
firts4;lastname4;4301-4399 3rd avenue brooklyn;New York;USA;4399 3rd Ave, Brooklyn, NY 11220, USA, ;40.6454789;-74.01718509999999
firts5;lastname5;;;;;;
firts6;lastname6;place de la bastille;paris;france;Place de la Bastille, Paris, France, ;48.8538045;2.3705269


Some help please.
Regards.




__________________________
Avant d'imprimer, pensez à l'environnement ! Please consider the environment before printing !
Ce message et toutes ses piÚces jointes sont confidentiels et établis à l'intention exclusive de ses destinataires. Toute utilisation non conforme à sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. IFP Energies nouvelles décline toute responsabilité au titre de ce message. This message and any attachments are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. IFP Energies nouvelles should not be liable for this message.
__________________________


[Non-text portions of this message have been removed]

[[e-mail] [sed-users]]

# cat lg.plx
url='https://maps.googleapis.com/maps/api/geocode/json?address='


CSV='input.csv'


perl -Mvars='$URL,@F' -wMstrict -sple '
@F = split /;/, $_, 8; # ensure line has exactly eight fields


# Index
# 0 1 2 3 4 5 6 7
# firstname;lastname;adress;town;country;Goog_add;lt;lg



# skip non-interesting lines
next if "@F[2..4]" =~ /^\s+$/ or "@F[5..7]" =~ /\S/;


y/ /+/ for my $i = join "", @F[2..4];


chomp(my @curl = qx{curl -s "$URL/$i"});


my $content = do{reset;
join(";",
map {
($_) = /[:]\s+(.+?)[,]?\s*$/;
/"(.*)"/ ? $1 : $_
}
grep {
m?lat? or m?lng? or m?formatted_address?
}
@curl
)};

$_ = join( ";", @F[0..4], "\e[31m$content\e[0m" );



' -- -URL="$url" "$CSV"




HTH
-Rakesh





[Non-text portions of this message have been removed]

[[e-mail] [sed-users]]

This is a commented version of the same with slight modifications and might be of help
of others....




# cat lg.sh
CSV='input.csv';
debug='0'; # controls the color me red (CMR) flag
url='https://maps.googleapis.com/maps/api/geocode/json?address=';


perl -Mvars='$URL,$CMR' -wMstrict -F'/;/' -spale '


push @F, map "", 1 .. 8-@F if @F < 8;
# increase fields to 8 only in case they are less


#--------0------------1---------2---------3---------4--------- --5-------6--7--
# firstname; lastname; adress; town; country; Goog_add; lt; lg


# skip when address/town/country all are empty or
# also skip when google-address, latitiude, longitude are filled up already
next if "@F[2..4]" =~ /^\s+$/ or "@F[5..7]" =~ /\S/;


# whitespace -> plus sign in order to string the google search argument
y/ \t/++/ for my $i = join "", @F[2..4];


# run the curl command on the url with the current address/town/country
chomp(my @curl = qx[curl -s "$URL/$i"]);


# extract the contents (google address/latitude/longitude) from the curl output
my ($content) = (
join(";",
map {
($_) = /[:]\s+(.+?)[,]?\s*$/; # this regex influenced by the fmt of the google search o/p
/"(.*)"/ ? $1 : $_
}
grep {
m?lat? or m?lng? or m?formatted_address?
}
@curl
),
reset()
);


$content =~ s/(.*)/\e[31m$1\e[0m/ if $CMR; # Color_Me_Red

# put the results of curl into the csv input
$_ = join ";", @F[0..4], $content;


' -- -URL="$url" -CMR="${debug:-0}" "$CSV"

Sent: Wednesday, January 20, 2016 7:19 AM
Subject: RE: loop
Hi Rakesh,
Thanks a lots for all.
Have a nice day.

-----Message d'origine-----
Envoyé : mercredi 20 janvier 2016 08:16
Objet : Re: loop
# cat lg.plx
url='https://maps.googleapis.com/maps/api/geocode/json?address='
CSV='input.csv'
@F = split /;/, $_, 8; # ensure line has exactly eight fields
# Index
# 0 1 2 3 4 5 6 7
# firstname;lastname;adress;town;country;Goog_add;lt;lg
# skip non-interesting lines
my $content = do{reset;
join(";",
map {
($_) = /[:]\s+(.+?)[,]?\s*$/;
/"(.*)"/ ? $1 : $_
}
grep {
m?lat? or m?lng? or m?formatted_address?
}
@curl
)};
' -- -URL="$url" "$CSV"
HTH
-Rakesh

[Non-text portions of this message have been removed]