title: How to clean large malformed CSV file using Python
body: <p>I'm attempting to use Python 2.7.5 to clean up a malformed CSV file.  The CSV file is fairly large (over 1GB).  The first row of the file correctly lists the column headings, but after that each field is on a new line (unless it is blank) and some fields are multi-line.  The multi-line fields are not surrounded by quotes, but need to be surrounded by quotes in the output.  The number of columns is static and known.  The pattern in the sample input provided is repeated throughout the length of the file.</p>
  
  <p>Input file (sample):</p>
  
  <pre><code>Hostname,Username,IP Addresses,Timestamp,Test1,Test2,Test3
  my_hostname
  ,my_username
  ,10.0.0.1
  192.168.1.1
  ,2015-02-11 13:41:54 -0600
  ,,true
  ,false
  my_2nd_hostname
  ,my_2nd_username
  ,10.0.0.2
  192.168.1.2
  ,2015-02-11 14:04:41 -0600
  ,true
  ,,false
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>Hostname,Username,IP Addresses,Timestamp,Test1,Test2,Test3
  my_hostname,my_username,"10.0.0.1 192.168.1.1",2015-02-11 13:41:54 -0600,,true,false
  my_2nd_hostname,my_2nd_username,"10.0.0.2 192.168.1.2",2015-02-11 14:04:41 -0600,true,,false
  </code></pre>
  
  <p>I've gone down a couple paths that address one of the issues only to realize that it doesn't handle another aspect of the malformed data.  I would appreciate if anyone could please help me identify an efficient way to clean up this file.</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have several code scraps from going down different paths, but here is the current iteration.  It isn't pretty, just a bunch of hacks to try and figure this out.</p>
  
  <pre><code>import csv
  
  inputfile = open('input.csv', 'r')
  outputfile_1 = open('output.csv', 'w')
  
  counter = 1
  for line in inputfile:
      #Skip header row
      if counter == 1:
          outputfile_1.write(line)
          counter = counter + 1
      else:
          line = line.replace('\r', '').replace('\n', '')
          outputfile_1.write(line)
  
  inputfile.close()
  outputfile_1.close()
  
  with open('output.csv', 'r') as f:
      text = f.read()
  
      comma_count = text.count(',') #comma_count/6 = total number of rows
  
      #need to insert a newline after the field contents after every 6th comma
      #unfortunately the last field of the row and the first field of the next row are now rammed up together becaue of the newline replaces above...
      #then process as normal CSV
  
      #one path I started to go down... but this isn't even functional
      groups = text.split(',')
  
      counter2 = 1
      while (counter2 &lt;= comma_count/6):
          line = ','.join(groups[:(6*counter2)]), ','.join(groups[(6*counter2):])
          print line
          counter2 = counter2 + 1
  </code></pre>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>Thanks to @DSM and @Ryan Vincent for getting me on the right track.  Using their ideas I made the following code, which seems to correct my malformed CSV.  I'm sure there are many places for improvement though, which I would happily accept.</p>
  
  <pre><code>import csv
  import re
  
  outputfile_1 = open('output.csv', 'wb')
  wr = csv.writer(outputfile_1, quoting=csv.QUOTE_ALL)
  
  with open('input.csv', 'r') as f:
      text = f.read()
      comma_indices = [m.start() for m in re.finditer(',', text)] #Find all the commas - the fields are between them
  
      cursor = 0
      field_counter = 1
      row_count = 0
      csv_row = []
  
      for index in comma_indices:
          newrowflag = False
  
          if "\r" in text[cursor:index]:
              #This chunk has two fields, the last of one row and first of the next
              next_field=text[cursor:index].split('\r')
              next_field_trimmed = next_field[0].replace('\n',' ').rstrip().lstrip()
              csv_row.extend([next_field_trimmed]) #Add the last field of this row
  
              #Reset the cursor to be in the middle of the chuck (after the last field and before the next)
              #And set a flag that we need to start the next csvrow before we move on to the next comma index
              cursor = cursor+text[cursor:index].index('\r')+1
              newrowflag = True
          else:
              next_field_trimmed = text[cursor:index].replace('\n',' ').rstrip().lstrip()
              csv_row.extend([next_field_trimmed])
  
              #Advance the cursor to the character after the comma to start the next field
              cursor = index + 1
  
          #If we've done 7 fields then we've finished the row
          if field_counter%7==0:
              row_count = row_count + 1
              wr.writerow(csv_row)
  
              #Reset
              csv_row = []
  
              #If the last chunk had 2 fields in it...
              if newrowflag:
                  next_field_trimmed = next_field[1].replace('\n',' ').rstrip().lstrip()
                  csv_row.extend([next_field_trimmed])
                  field_counter = field_counter + 1
  
          field_counter = field_counter + 1
      #Write the last row
      wr.writerow(csv_row)
  
  outputfile_1.close()
  
  # Process output.csv as normal CSV file...    
  </code></pre>