Hey Jean Jourdain,
Thanks for the tip with fixlengths but - if I understand correctly what this command does - I wouldn’t try to set the lines to the same length because there are some address-fields there (like city, district and street) that have different lengths. Regards, Vlad > Am 28.03.2025 um 20:02 schrieb jj <[email protected]>: > > Vlad, > > xsv has a fixlengths command that might help: > > xsv fixlengths --help > Transforms CSV data so that all records have the same length. The length is > the length of the longest record in the data (not counting trailing empty > fields, > but at least 1). Records with smaller lengths are padded with empty fields. > > This requires two complete scans of the CSV data: one for determining the > record size and one for the actual transform. Because of this, the input > given must be a file and not stdin. > > Alternatively, if --length is set, then all records are forced to that length. > This requires a single pass and can be done with stdin. > > Usage: > xsv fixlengths [options] [<input>] > > fixlengths options: > -l, --length <arg> Forcefully set the length of each record. If a > record is not the size given, then it is truncated > or expanded as appropriate. > > Common options: > -h, --help Display this message > -o, --output <file> Write output to <file> instead of stdout. > -d, --delimiter <arg> The field delimiter for reading CSV data. > Must be a single character. (default: ,) > > HTH, > > Jean Jourdain > On Friday, March 28, 2025 at 7:16:34 PM UTC+1 GP wrote: >> Your Pattern Playground results are perplexing. Using your first post's >> example CSV data, the grep: >> >> \d{3};\w{3};[^;]*;[^;]*;\d{10};(\w{2});(\d{2});(\d{5});([^;]*);[^;]*;([^;]*);([^;]*);([^;]*);[^;]*;\w{2};\d{2};\d{5};[^;]*;\d{12};[^;]*;[^;]*;\d{8};[^;]*;\d{12};[^;]*;[^;]*;\d;\d;\d;\d;\d;\d;\d;\d;([^;]*);[^\n]* >> >> results in every line but the first column labels line matching. >> >> To figure out what the problem might be on your system with your local >> language configuration using either BBEdit's Pattern Playground or regex101 >> start out by building the grep pattern from scratch and rebuilding it from >> left to right by semicolon delineated field pattern parts. E.g., first >> \d{3}; which should find/highlight 7 matches in each line of the example CSV >> data - second add \w{3}; for a total grep of \d{3};\w{3}; which should >> result in the leading 200;BAG; being highlighted for each line in the >> example. Continue on like that until you find the next added semicolon >> delineated field pattern part fails to show a match for the left side part >> of each line in the example data. It'll be something in that line's or >> lines' field/column that isn't matching what the just add grep pattern >> part's matching criteria is. >> >> In addition to sorting, an additional use of a working grep pattern is that >> you can also use it with BBEdit's Text -> Process Lines Containing... to >> find all lines that do NOT contain that grep pattern which will help in >> finding malformed CSV data in the large CSV data files your working with. >> On Friday, March 28, 2025 at 7:12:03 AM UTC-7 Vlad Ghitulescu wrote: >> Hey GP >> >> >> I corrected the error re „Specific sub-patterns:“ but this didn’t seem to >> bring any change: The ADRC_POST_CODE1 is still not sorted >> >> >> >> The command gave also no recognizable sign that is ready, so I’m not sure >> that it didn’t have also problems with the line 25816, where the CRLF >> follows a house-number (see previous emails). >> >> BBEdit’s Pattern Playground shows however that there is no result after >> searching with the regex >> >> >> >> I’ll take the regex to regex101 (thanks for the hint!) and see if I could >> spot an error. >> >> >> >> Regards, >> Vlad >> >> >> >> >> >>> Am 26.03.2025 um 19:42 schrieb GP <[email protected] <>>: >>> >> >>> First, in your Sort Lines dialog screenshot, you need to select the >>> "Specific sub-patterns:" option instead of "Entire match" in order for the >>> lines to be sorted by your column sorting criteria (MSGNO, ADRC_COUNTRY, >>> ADRC_REGION, ADRC_POST_CODE1, ADRC_CITY1, ADRC_CITY2, ADRC_STREET and >>> ADRC_HOUSE_NUM1). Since the sort lines grep pattern: >>> >>> \d{3};\w{3};[^;]*;[^;]*;\d{10};(\w{2});(\d{2});(\d{5});([^;]*);[^;]*;([^;]*);([^;]*);([^;]*);[^;]*;\w{2};\d{2};\d{5};[^;]*;\d{12};[^;]*;[^;]*;\d{8};[^;]*;\d{12};[^;]*;[^;]*;\d;\d;\d;\d;\d;\d;\d;\d;([^;]*);[^\n]* >>> >>> will match every line in your example, using the "Entire match" option >>> devolves the sort into a simple whole line string sort which would put the >>> MSGNO (i.e. \8 in the example) column contents last instead of first in the >>> sort order. (See the "Sort Lines" section in Chapter 5 of the BBEdit User >>> Manual for details of using sub-pattern sort ordering.) >>> >>> With the "Entire match" option, if you look at every 2..> line the left >>> part of each line is the same until you get to the part of the string with >>> the ADRC_ADDRNUMBER characters so the differences in that part of the >>> string is Sort Line's "Entire match" is using to determine the ordering of >>> the whole line strings. >>> >>> Using the "Specific sub-patterns:" option is what allows you to specify >>> what substring part(s) of a string/line and what composed ordering of those >>> concatenated substring will be used in determining the sort ordering >>> between whole strings/lines. >>> >>> To see what's going on with Sort Lines' "Specific sub-patterns:" option you >>> can use BBEdit's Pattern Playground to see what the concatenated substring >>> for a line is being used to determine line sort ordering. For "Search >>> pattern:" put: >>> >>> \d{3};\w{3};[^;]*;[^;]*;\d{10};(\w{2});(\d{2});(\d{5});([^;]*);[^;]*;([^;]*);([^;]*);([^;]*);[^;]*;\w{2};\d{2};\d{5};[^;]*;\d{12};[^;]*;[^;]*;\d{8};[^;]*;\d{12};[^;]*;[^;]*;\d;\d;\d;\d;\d;\d;\d;\d;([^;]*);[^\n]* >>> >>> and for "Replace pattern" put: >>> >>> \8\1\2\3\4\5\6\7 >>> >>> and for "Contents of" chose an open example file. >>> >>> As you step through each grep pattern match (using the Next button), the >>> "Replacement text:" field will show you the concatenated string composed >>> from the capture group ordered substring of the whole matched string/line. >>> It is that "Replacement text:" string that Sort Lines uses for "Specific >>> sub-patterns:" option sorting evaluation. >>> >>> P.S. If an explanation of what the parts of a grep regular expression is >>> specifying would help, https://regex101.com <https://regex101.com/> has a >>> pretty good explanation panel that explains what each bit of a regular >>> expression is doing. >>> On Wednesday, March 26, 2025 at 6:24:57 AM UTC-7 Vlad Ghitulescu wrote: >>> Hey GP >>> >>> >>> And thanks for the suggestion! >>> >>> I tried the sort-solution before trying to understand the regex itself 😶 >>> >>> I pasted into Text —> Sort Lines… like this >>> >>> >>> >>> but after Sort it doesn’t look like the postal code column was considered >>> >>> >>> >>> Did I miss something? >>> >>> Thanks again! >>> >>> >>> Regards, >>> Vlad >>> >>> >>> >>> >>> >>> >>>> Am 25.03.2025 um 22:32 schrieb GP <[email protected] <>>: >>>> >>> >>>> As a follow up... >>>> >>>> BBEdit's Pattern Playground is a great help in constructing tedious grep >>>> patterns like you'll need for your filtering and sorting needs. The really >>>> tedious part is getting the field position(s) you want to filter or sort >>>> on so you can modify that field's match pattern to conform to the desired >>>> filter or sorting criteria. >>>> >>>> For example... For your " Filter all lines that have ADR_CHK_KZ = 1" using >>>> Text -> Process Lines Containing ... with the grep pattern: >>>> >>>> >>>> \d{3};\w{3};[^;]*;[^;]*;\d{10};\w{2};\d{2};\d{5};[^;]*;[^;]*;[^;]*;[^;]*;[^;]*;[^;]*;\w{2};\d{2};\d{5};[^;]*;\d{12};[^;]*;[^;]*;\d{8};[^;]*;\d{12};[^;]*;[^;]*;\d;\d;\d;\d;\d;\d;\d;(1);[^;]*;[^\n]* >>>> >>>> will do the trick. For filtering you don't need the group capturing on the >>>> 1 but it is useful with Pattern Playground to verify you're getting the >>>> right field position and field contents matched. >>>> >>>> For your "Sort the file by MSGNO, ADRC_COUNTRY, ADRC_REGION, >>>> ADRC_POST_CODE1, ADRC_CITY1, ADRC_CITY2, ADRC_STREET and ADRC_HOUSE_NUM1" >>>> using Text -> Sort Lines ... with a grep pattern of: >>>> >>>> \d{3};\w{3};[^;]*;[^;]*;\d{10};(\w{2});(\d{2});(\d{5});([^;]*);[^;]*;([^;]*);([^;]*);([^;]*);[^;]*;\w{2};\d{2};\d{5};[^;]*;\d{12};[^;]*;[^;]*;\d{8};[^;]*;\d{12};[^;]*;[^;]*;\d;\d;\d;\d;\d;\d;\d;\d;([^;]*);[^\n]* >>>> >>>> with "Specific sub-patterns" selected with \8\1\2\3\4\5\6\7 in the fill in >>>> field will sort your example text using your desired field ordering. >>>> On Tuesday, March 25, 2025 at 12:53:47 PM UTC-7 GP wrote: >>>> For filtering, look at Text -> Process Lines Containing ... and for >>>> sorting Text -> Sort Lines ... using grep patterns to identify what you >>>> want to match for filtering and what subpattern field or fields you want >>>> to sort ordered on. >>>> >>>> If the number of fields in your sample is representative of the real CSV >>>> files you're working with, it is going to be something of a pain in the >>>> rear coming up with the grep patterns needed to accomplish the desired >>>> filtering and sorting. >>>> >>>> On Tuesday, March 25, 2025 at 11:03:35 AM UTC-7 Vlad Ghitulescu wrote: >>>> Hey, >>>> >>>> >>>> I use BBEdit very often while working with big CSV-files (300 - 500 MB, up >>>> to 4 million rows) looking like this: >>>> >>>> MANDT;BU;IDENTIFIER;OBJNR;ADRC_ADDRNUMBER;ADRC_COUNTRY;ADRC_REGION;ADRC_POST_CODE1;ADRC_CITY1;ADRC_CITY_EXT;ADRC_CITY2;ADRC_STREET;ADRC_HOUSE_NUM1;ADRC_HOUSE_NUM2;LOKAREF_COUNTRY;LOKAREF_REGION;LOKAREF_POST_CODE1;LOKAREF_CITY1;LOKAREF_CITY_CODE;LOKAREF_CITY_EXT;LOKAREF_CITY2;LOKAREF_CITYP_CODE;LOKAREF_STREET;LOKAREF_STRT_CODE;LOKAREF_HOUSE_NUM1;LOKAREF_HOUSE_NUM2;COUNTRY_KZ;REGION_KZ;POST_CODE1_KZ;CITY1_KZ;CITY_EXT_KZ;CITY2_KZ;STREET_KZ;ADR_CHK_KZ;MSGNO;MESSAGE >>>> >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723592;DE;09;86415;Mering;;Sankt >>>> Afra;Egerländer Straße;;;DE;09;86415;Mering;500000002795;, Schwab;Sankt >>>> Afra;00000006;Egerländerstraße;910011919800;;;0;0;0;0;1;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723657;DE;09;85655;Aying;;Kaps;Kaps;;;DE;09;85653;Aying;500000002262;;Kaps;00000010;Kaps;700055566100;;;0;0;1;0;3;0;0;1;; >>>> >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723658;DE;09;83083;Riedering;;Patting;Patting;;;DE;09;83083;Riedering;500000002552;b >>>> Rosenheim, Oberbay;Patting;00000037;Pattinger >>>> Straße;910003809300;;;0;0;0;0;1;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723674;DE;09;85655;Aying;;Großhelfendorf;Hirschbergstraße;;;DE;09;85653;Aying;500000002262;;Großhelfendorf;00000007;Hirschbergstraße;910002873200;;;0;0;1;0;3;0;0;1;; >>>> >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723878;DE;09;93336;Altmannstein;;Berghausen;Altmannsteiner >>>> >>>> Str.;;;DE;09;93336;Altmannstein;500000005266;;Berghausen;00000003;Altmannsteiner >>>> Straße;910001339100;;;0;0;0;0;3;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723908;DE;09;93336;Altmannstein;;Berghausen;Altmannsteiner >>>> >>>> Str.;;;DE;09;93336;Altmannstein;500000005266;;Berghausen;00000003;Altmannsteiner >>>> Straße;910001339100;;;0;0;0;0;3;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723918;DE;09;93336;Altmannstein;;Berghausen;Altmannsteiner >>>> >>>> Str.;;;DE;09;93336;Altmannstein;500000005266;;Berghausen;00000003;Altmannsteiner >>>> Straße;910001339100;;;0;0;0;0;3;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007723956;DE;09;93336;Altmannstein;;Berghausen;Altmannsteiner >>>> >>>> Str.;;;DE;09;93336;Altmannstein;500000005266;;Berghausen;00000003;Altmannsteiner >>>> Straße;910001339100;;;0;0;0;0;3;0;1;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007724554;DE;09;95131;Schwarzenbach >>>> a.Wald;;Schwarzenbach a >>>> Wald;Walter-Münch-Straße;;;DE;09;95131;Schwarzenbach >>>> a.Wald;500000011836;;Schwarzenbach >>>> a.Wald;00000001;Walter-Münch-Straße;910007835500;;;0;0;0;0;3;1;0;1;; >>>> 200;BAG;20250324080508_/ETN/PM_EAV_ADR_CHK_ADRC_V14157F;;0007724593;DE;09;95131;Schwarzenbach >>>> a.Wald;;Schwarzenbach a >>>> Wald;Walter-Münch-Straße;;;DE;09;95131;Schwarzenbach >>>> a.Wald;500000011836;;Schwarzenbach >>>> a.Wald;00000001;Walter-Münch-Straße;910007835500;;;0;0;0;0;3;1;0;1;; >>>> >>>> Once in a while I’d like to filter or sort such huge files by one or more >>>> columns, like: >>>> >>>> 1. Filter all lines that have ADR_CHK_KZ = 1 or >>>> 2. Sort the file by MSGNO, ADRC_COUNTRY, ADRC_REGION, ADRC_POST_CODE1, >>>> ADRC_CITY1, ADRC_CITY2, ADRC_STREET and ADRC_HOUSE_NUM1. >>>> >>>> Is there a way to do this sort of tasks with BBEdit? >>>> >>>> Thanks! >>>> >>>> >>>> Regards, >>>> Vlad >>>> >>>> >>>> >>>> >>> >>>> -- >>>> This is the BBEdit Talk public discussion group. If you have a feature >>>> request or believe that the application isn't working correctly, please >>>> email "[email protected] <>" rather than posting here. Follow @bbedit >>>> on Mastodon: <https://mastodon.social/@bbedit> >>>> --- >>>> You received this message because you are subscribed to the Google Groups >>>> "BBEdit Talk" group. >>>> To unsubscribe from this group and stop receiving emails from it, send an >>>> email to [email protected] <>. >>> >>>> To view this discussion visit >>>> https://groups.google.com/d/msgid/bbedit/50130484-14eb-4298-b762-800f88b2c66en%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/bbedit/50130484-14eb-4298-b762-800f88b2c66en%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> >>> >>> -- >>> This is the BBEdit Talk public discussion group. If you have a feature >>> request or believe that the application isn't working correctly, please >>> email "[email protected] <>" rather than posting here. Follow @bbedit on >>> Mastodon: <https://mastodon.social/@bbedit> >>> --- >>> You received this message because you are subscribed to the Google Groups >>> "BBEdit Talk" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to [email protected] <>. >> >>> To view this discussion visit >>> https://groups.google.com/d/msgid/bbedit/3e139849-cf1a-41d8-821e-97f87cc39513n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/bbedit/3e139849-cf1a-41d8-821e-97f87cc39513n%40googlegroups.com?utm_medium=email&utm_source=footer>. >> > > > -- > This is the BBEdit Talk public discussion group. If you have a feature > request or believe that the application isn't working correctly, please email > "[email protected]" rather than posting here. Follow @bbedit on Mastodon: > <https://mastodon.social/@bbedit> > --- > You received this message because you are subscribed to the Google Groups > "BBEdit Talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > <mailto:[email protected]>. > To view this discussion visit > https://groups.google.com/d/msgid/bbedit/91d5fb2e-1280-40c1-b9c8-c83cbbf698fbn%40googlegroups.com > > <https://groups.google.com/d/msgid/bbedit/91d5fb2e-1280-40c1-b9c8-c83cbbf698fbn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- This is the BBEdit Talk public discussion group. If you have a feature request or believe that the application isn't working correctly, please email "[email protected]" rather than posting here. Follow @bbedit on Mastodon: <https://mastodon.social/@bbedit> --- You received this message because you are subscribed to the Google Groups "BBEdit Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/bbedit/D150D38C-AF17-4F14-BA90-A8A8B6731429%40Ghitulescu.de.
