Rearranging BT Meridian csv reports

This is a pretty specific-use script, but I'll stick it here anyway since I keep losing it. It's to rearrange the output from a BT Meridian switch reporting on the usage of its DNs. What it does is to space the DNs such that each DN appears in a row with a number equal to its own. DN 123 appears on row 123, DN 498 appears on row 498 etc. I don't know why this is particularly useful, but I'm assured it is.

There are enough comments, hopefully, to make it reasonably easy to modify this script for similar uses in future. In short, it reads the csv file as plain-text line-by-line and uses a regexp to pick out the DN. It writes the whole line to the array, at the element numbered the same as the DN. (if the same DN appears twice, the last one encountered survives, the rest don't). It then prints _every_ line of the array to a CSV file, including null ones, such that even if $array[285] is the first non-null element, it is still the 285th line printed to the file.

To use it, stick the csv file you want processed and the script in the same directory, and run the script. It will produce 'out.csv' which is the processed script. It always operates on the alphabetically first *.csv file it finds (except out.csv) and will silently overwrite out.csv if it exists. If it has problems, it writes 'errors.txt' which contains the output. It's mostly run on Windows boxen outside of a terminal

  1. #! /usr/bin/perl
  3. use strict;
  5. ## Most Recent Changes:
  6. ##
  7. ## Added dupe checking. Before writing to array, checks whether that element exists or not.
  8. ## If it does, writes message to error array and carries on. At the end of the run, if the
  9. ## error array contains messages it dumps them to a new errors file, and exits. It does not
  10. ## create the output csv.
  11. ##
  12. ## Also writes more general errors to the file, including if it finds no csv files.
  13. ##
  14. ## Might support filenames with spaces.
  15. ##
  16. ##
  18. my <span class="katex math inline">output_file = './out.csv';
  20. my</span>error_file = "./errors.txt";
  21. my @error_array = ();
  23. # Open the current directory and read its full contents to a new array called @files.
  24. # Then go through @files and, if the file does not begin with a dot, ends with `csv`
  25. # and is not `out.csv` add it to an array. We then sort the array and pick the first
  26. # file and presume that is our input file.
  27. opendir(DIR, ".") || die("Error opening working dir");
  28. my @files=readdir(DIR);
  29. my @csvs;
  30. foreach my <span class="katex math inline">f (@files){
  31. if ((</span>f !~ /^\./) && (<span class="katex math inline">f =~ /csv</span>/) && (<span class="katex math inline">f !~ /^out\.csv</span>/)){
  32. push( @csvs, $f);
  33. }
  34. }
  35. if (@csvs < 1){
  36. print "no csvs";
  37. push (@error_array, "Can find no csv files. Check it's not saved as xls");
  38. }
  40. @files=sort(@csvs);
  41. my $input_file = "./$files[0]";
  43. # Handy info for the user
  44. print $input_file."\n";
  46. # Initialise an array for the output, and one for error messages.
  47. my @output_array = ();
  49. # Go through each element of the array (and therefore line of the file) and,
  50. # if it contains the string 'LO' but not 'Unknown', process it
  53. open INPUT_FILE , "< $input_file" || die ("Error opening input file at $input_file");
  55. foreach (<INPUT_FILE>){
  56. my <span class="katex math inline">line =</span>_;
  57. ## If the line begins with two or more decimal digits, it's probably one of
  58. ## those funny bungay ones
  59. if (<span class="katex math inline">line =~ /^\d{2}/){
  60. my</span>line_number = (split /,/, <span class="katex math inline">_)[0];</span>line_number--;
  61. ## If the line we want to write to is non-empty (i.e. it has
  62. ## digits in it), then error
  63. if (<span class="katex math inline">output_array[</span>line_number] =~ /\d/){
  64. my $repeat_count = 0;
  65. foreach (<input_file>){
  66. if ((split /,/, <span class="katex math inline">_)[0] =~ m/</span>line_number/){
  67. <span class="katex math inline">repeat_count++;
  68. }
  69. }</span>error_array[<span class="katex math inline">line_number] =</span>line_number." exists about ".<span class="katex math inline">repeat_count." times.";
  70. }</span>output_array[<span class="katex math inline">line_number]=</span>line;
  71. ## If it doesn't, carry on as normal.
  72. }elsif (<span class="katex math inline">line =~ /LO/ ){
  73. if (</span>line !~ /Unknown/){
  74. # Pick out where the TN is in the line (in the third /-separated
  75. # group of the third comma-separated group).
  76. # Then write the entire line to the element of the array that is one
  77. # less than the TN. Chop() gets rid of the last character of the string
  78. # which here is a "
  79. # (remember that the first element of an array is 0, but the first
  80. # line of a file is 1; element 0 will become line 1)
  81. my @cur_line = split (/,/, <span class="katex math inline">_);
  82. my @tn_string = split (/\//,</span>cur_line[2]);
  83. my <span class="katex math inline">tn =</span>tn_string[2];
  84. <span class="katex math inline">tn--;</span>output_array[<span class="katex math inline">tn] =</span>line;
  85. }
  86. }
  87. }
  88. close INPUT_FILE;
  90. foreach (@error_array){print;}
  92. # If the error array contains records (i.e. its length is greater than zero),
  93. # create an error log file
  94. if (@error_array > 0){
  95. open ERROR_FILE , "> <span class="katex math inline">error_file" || die ("Error opening error log at</span>error_file. You're fucked.");
  96. print ERROR_FILE "Errors encountered processing ".<span class="katex math inline">input_file.":\n";
  97. print STDERR "Errors encountered processing ".</span>input_file.":\n";
  98. foreach my <span class="katex math inline">error (@error_array){
  99. print ERROR_FILE</span>error;
  100. print STDERR "\t".<span class="katex math inline">error;
  101. }
  102. close ERROR_FILE;
  103. print "\n";
  104. }
  107. # Open the output file, and write the contents of the output array to file.
  108. # Remember that Perl will append a \n to each non-null element of the array, but we want one
  109. # on the end of every element, including null. So we chomp each line to remove the /n from
  110. # those that have it, and re-add it to _every_ array.
  112. open OUTPUT_FILE, "></span>output_file" || die ("Error opening output file at <span class="katex math inline">output_file");
  113. my</span>output_line;
  114. foreach <span class="katex math inline">output_line (@output_array){
  115. chomp</span>output_line;
  116. print OUTPUT_FILE <span class="katex math inline">output_line. "\n";
  117. #print</span>_, "\n";
  118. }
  119. close OUTPUT_FILE;
  120. print "\n";
  121. </input_file>