0

I have a large list of data with rows like this:

672629,Maros,Maros,Marisus|Mures|Muresh|Muresul|Mureş|Mureşul|mwrysh,46.25,20.2,H,STM,HU,HU|RO,00,0,81,Europe/Budapest,2009-04-06
677528,Gémesi Révház,Gemesi Revhaz,46.33472,18.89167,P,PPL,HU,00,0,102,Europe/Budapest,2014-07-08
680145,Fekete-Körös,Fekete-Koros,Black Koros|Black Körös|Crisu Negru|Crisul Negru|Crişu Negru|Crişul Negru|Fekete-Koros|Fekete-Körös|Raul Crisu Negru|Râul Crişu Negru,46.7,21.26667,H,STM,HU,00,0,88,Europe/Budapest,2012-01-18
680147,Fehér-Körös,Feher-Koros,Crisu Alb|Crisul Alb|Crişu Alb|Crişul Alb|Feher-Koros|Fehér-Körös|Weifse Koros|Weifse Körös|Weisze Koros|Weisze Körös|White Koros|White Körös,46.93333,20.96667,H,STM,HU,00,0,80,Europe/Budapest,2012-01-18
680300,Kraszna-csatorna,Kraszna-csatorna,Canalul Crasna|Canalul Crasnei|Kraszna-csatorna,48.13333,22.31667,H,CNL,HU,00,0,112,Europe/Budapest,2014-07-08
680311,Kraszna,Kraszna,Crasna|Krasna|Kraszna,48.15,22.33333,H,STM,HU,00,0,108,Europe/Budapest,2014-07-08
713827,Mogyorós,Mogyoros,Moggorospuszta|Moggoróspuszta|Mogyoros|Mogyorospuszta|Mogyorós|Mogyoróspuszta,47.03333,21.56667,L,AREA,HU,HU,10,0,93,Europe/Budapest,2012-01-18
713828,Zvaradűlő,Zvaradulo,46.85,20.78333,P,PPL,HU,03,0,81,Europe/Budapest,2007-08-27
713829,Zuhogó,Zuhogo,Nagyzuhogo|Nagyzuhogó|Zuhogo|Zuhogó,47.85,22.56667,L,AREA,HU,HU,18,0,111,Europe/Budapest,2012-01-18
713830,Zúgó-tető,Zugo-teto,48.03333,20.8,T,HLL,HU,04,0,136,Europe/Budapest,1993-12-28
713831,Zugoly,Zugoly,Szugoly|Zugoly,46.2,20.45,P,PPLX,HU,HU,06,0,79,Europe/Budapest,2012-01-18
713832,Zúgó-dűlő,Zugo-dulo,47.58333,21.9,L,AREA,HU,10,0,144,Europe/Budapest,2007-08-27
713833,Zuglókert,Zuglokert,47.31667,20.93333,P,PPL,HU,20,0,85,Europe/Budapest,1993-12-28
713834,Zug-kert,Zug-kert,47.33333,20.9,L,AREA,HU,20,0,86,Europe/Budapest,1993-12-28
713835,Zubogyi Tanya,Zubogyi Tanya,48.38333,20.58333,P,PPL,HU,04,0,243,Europe/Budapest,1993-12-28
713836,Zubogy,Zubogy,Zubogy,48.38333,20.58333,P,PPL,HU,04,0,243,Europe/Budapest,2012-01-18
713837,Zubogó-tető,Zubogo-teto,48.33333,20.68333,T,HLL,HU,04,0,267,Europe/Budapest,1993-12-28
713838,Zubogórész,Zubogoresz,47.55,22.03333,P,PPL,HU,10,0,138,Europe/Budapest,1993-12-28
713839,Zsurk,Zsurk,Zsurk,48.41184,22.21955,P,PPL,HU,18,0,103,Europe/Budapest,2012-01-18
713840,Zsujta Vasúti Megállóhely,Zsujta Vasuti Megallohely,48.5,21.26667,S,RSTP,HU,04,0,155,Europe/Budapest,1993-12-28
713841,Zsujta,Zsujta,Zsujta,48.5,21.28333,P,PPL,HU,04,0,188,Europe/Budapest,2012-01-18
713842,Zsórytanya,Zsorytanya,47.78333,21,P,PPL,HU,04,0,92,Europe/Budapest,1993-12-28
713843,Zsombokos,Zsombokos,47.01667,21.5,P,PPLX,HU,10,0,86,Europe/Budapest,1993-12-28
713844,Zsombódűlő,Zsombodulo,46.31667,20,P,PPL,HU,06,0,85,Europe/Budapest,2007-08-27
713845,Zsombékosdűlő,Zsombekosdulo,Zsombekos|Zsombékos,47.2,20.01667,P,PPLX,HU,HU,16,0,88,Europe/Budapest,2007-08-27
713846,Zsombékakol,Zsombekakol,Zsombekakol|Zsombokakol|Zsombékakol|Zsombókakol,47.15,20.98333,P,PPLX,HU,HU,03,0,82,Europe/Budapest,2012-01-18
713847,Zsoldosdűlő,Zsoldosdulo,47.45,21.4,P,PPLX,HU,10,0,96,Europe/Budapest,2007-08-27
713848,Zsoldos,Zsoldos,47.15,21.45,P,PPLL,HU,10,0,88,Europe/Budapest,1993-12-28
713849,Zsolcai-halom,Zsolcai-halom,48.11667,20.88333,T,HLL,HU,04,0,125,Europe/Budapest,1993-12-28
713850,Zsófiatanya,Zsofiatanya,47.98333,20.93333,P,PPL,HU,04,0,97,Europe/Budapest,1993-12-28
713851,Zsófiamajor,Zsofiamajor,46.93333,20.91667,P,PPL,HU,03,0,82,Europe/Budapest,1993-12-28
713852,Zsőcetanya,Zsocetanya,48.18333,21.83333,P,PPL,HU,18,0,92,Europe/Budapest,1993-12-28
713853,Zsírostanya,Zsirostanya,47.85,22.56667,P,PPLX,HU,18,0,111,Europe/Budapest,1993-12-28 

I would like to get the 2. column, plus the two numbers which are usually in column 4 and 5

so they become like this:

Maros   46.25   20.2
Gémesi Révház   46.33472    18.89167
.
.
.

Can we achieve this with regex?

  • 1
    No, result should be either 3 columns or 4. We can pull column 2 & 3 and extract coordinates but figure out on the fly that "Zsőcetanya" is the same as "Zsocetanya" and leave it as a single word but in the same time keep two words for "Gémesi Révház" it would be hard or impossible with regex. – Alex Feb 16 '17 at 10:56
  • As your entries may contain spaces and apparently there is no column delimiter character it's quite difficult to write a regex which could determine second column. Please explain how it should find it. – Máté Juhász Feb 16 '17 at 10:57
  • okay, I have replaced all tabs with commas, would this help? like discarding anything between the first string enclosed in commas and the first number, and discarding anything after the second number, and keeping the first string and the two numbers? – xedemx Feb 16 '17 at 11:11
  • @xedemx With commas it is much better, check it in my answer – Alex Feb 16 '17 at 11:39
0

Find what: ^\d+,\s*([^,]+)+[^0-9]+([0-9\.]+),\s*([0-9\.]+),.+$

Replace with: \1,\2,\3

  • No problem, I'm glad we resolved it finally – Alex Feb 16 '17 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.