title: Importing csv file with hidden characters into Excel using vba
body: <p>With the help of this forum have been able to solve most problem but this has me stuck. </p>
  
  <p>I have a comma delimited <code>csv</code> file ("xxxx","zzz",) that has hidden <code>chr(10)</code> and <code>chr(13)</code> in the file. If I use a script to replace both these characters, I lose the end of record  <code>chr(10)</code> so only imports as one record. </p>
  
  <p>In notepad the file shows perfect - one record per line. If I open as a an excel file it is ok, its only when I import as a csv delimited file</p>
  
  <p>With thanks to other contributors, below is what I have been using.</p>
  
  <p>Ideally what I would like to do is:</p>
  
  <ol>
  <li>select the csv file</li>
  <li>copy the file to keep the original   &lt;&lt;&lt; extra function</li>
  <li>clean up all hidden characters that would affect the import</li>
  <li>then import</li>
  </ol>
  
  <p>-</p>
  
  <pre><code>Private Sub CSV_ImportRepl()
      Dim strFile As String
      Dim strBuffer As String
      Dim ff As Integer
      Dim strFileName As String
      Dim ws As Worksheet
  
      'ENTRIES CSV FILE ----------------------------------
      ' ---open file ----------
      strFile = Application.GetOpenFilename("Text Files (*.csv),*.*", _
      , "SELECT ENTRIES csv FILE")
      strFileName = strFile
      MsgBox strFileName
  
      ' ---start cleaning file ----------
      strBuffer = Space(FileLen(strFile))
      ff = FreeFile
      Open strFile For Binary Access Read As #ff
      Get #ff, , strBuffer
      Close #ff
  
      strBuffer = Replace(strBuffer, Chr(13), "")
      Kill strFile
  
      Open strFile For Binary Access Write As #ff
      Put #ff, , strBuffer
      Close #ff
  
      ' --- clear contents &amp; import  ----------
      Sheets("Entries").Cells.ClearContents
      Set ws = ActiveWorkbook.Sheets("Entries") 'set to current worksheet name
      strFile = strFileName
  
      With ws.QueryTables.Add(Connection:="TEXT;" &amp; strFile, _
      Destination:=ws.Range("A1"))
           .TextFileParseType = xlDelimited
           .TextFileCommaDelimiter = True
           .Refresh
      End With
  End Sub
  </code></pre>
  
  <p>Any help would be most appreciated.</p>