Hello team, I have a very mis-formatted string exported to csv file from oracle db table (around 60 columns table) Seems each line is very badly formatted so I got some perl code to format the same. As example a single original line from csv file when I print in a Linux terminal: 

""6357445"                                                            
+                                                            "1349947"
+                                                                     
+                                                 "0"                 
+                                                                     
+                                      "1"                            
+                                                                     
+                           "3"                                       
+                                                                     
+                "2"                                                  
+                                                                     
+     "1"                                                             
+                                                               "1"   
+                                                                     
+                                                    "1"              
+                                                                     
+                                         "1"                         
+                                                                     
+                              "-2"                                   
+                                                                     
+                   "1394531830"                                      
+                                                                     
+        "1394531830"                                                 
+                                                                  "14
+15599200"                                                            
+                                                       "0"           
+                                                                     
+                                            "0"                      
+                                                                     
+                                 "0"                                 
+                                                                     
+                      "0"                                            
+                                                                     
+           "196"                                                     
+                                                                     
+"29240"                                                              
+                                                          "378"      
+                                                                     
+                                               "1394531846"          
+                                                                     
+                                    "0"                              
+                                                                     
+                         "0"                                         
+                                                                     
+              "0"                                                    
+                                                                     
+   "8201"                                                            
+                                                             "0"     
+                                                                     
+                                                  "64"               
+                                                                     
+                                       "0"                           
+                                                                     
+                            "2"                                      
+                                                                     
+                 "89799"                                             
+                                                                     
+      "8201"                                                         
+                                                                "8980
+5"                                                                   
+                                                     "-1"            
+                                                                     
+                                          "-1"                       
+                                                                     
+                               "Local Cell id=2, Cell Name=21842C11" 
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+           "0"                                                       
+                                                                     
+"1394531057"                                                         
+                                                          "1394531057
+"                                                                    
+                                               "1415599200"          
+                                                                     
+                                    "1394531092"                     
+                                                                     
+                         ""                                          
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                      "0"                            
+                                                                     
+                           "RAT_INFO=GL, AFFECTED_RAT=L"             
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                       ""            
+                                                                     
+                    ""                                               
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                            
"10.35.201.146"                                                       
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+            "195;"                                                   
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+          "0"                                                        
+                                                                    "
+5705354"                                                             
+                                                         "0"         
+                                                                     
+                                              ""                     
+                                                                     
+                                                                 ""  
+                                                                     
+                                                                     
+               "0"                                                   
+                                                                     
+    "0"                                                              
+                                                              "Cell""

[download]

This looks odd, but that's only 1 single line, splitted in some weird characters. After some perl code I got this: 

my $file = `filename`;
chomp($file);
open(my $data, '<', $file) or die "Couldn't open '$file'\n ";
while (my $line = <$data>){
        $line =~ s/\s+/ /g ;
        $line =~ s/\" \"/\"\"/g ;
        $line =~ s/\n+//g ;
        $line =~ s/\s+$//;
        if ($line !~ /select/ &&  $line !~ /spool/ &&  $line !~ /\|\|/
+ ){

                my @array = $line;
                my $result;
                for (@array){
                        chomp;
                        $result .= $_ . " ";

                        }

                $result =~ s/\s+//g;
                print "$result\n";

[download]
Result of this is 
""6357445""1349947""0""1""3""2""1""1""1""1""-2""1394531830""1394531830
+""1415599200""0""0""0""0""196""29240""378""1394531846""0""0""0""8201"
+"0""64""0""2""89799""8201""89805""-1""-1""LocalCellid=2,CellName=2184
+2C11""0""1394531057""1394531057""1415599200""1394531092""""0""RAT_INF
+O=GL,AFFECTED_RAT=L"""""
"10.35.201.146""195;""0""5705354""0""""""0""0""Cell""

[download]
Ok, so I have one line still splited for some reason. Here each field is separated by double quote "" character, so we can expect empty fields as well. My target is to have this in a single line in order to access to each element, even the NULL ones!

Hope this is clear, any thoughts?

Thanks!

In reply to extract string columns with perl by juanito23

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.