Importing csv file with hidden characters into Excel using vba

Question

With the help of this forum have been able to solve most problem but this has me stuck.

I have a comma delimited csv file ("xxxx","zzz",) that has hidden chr(10) and chr(13) in the file. If I use a script to replace both these characters, I lose the end of record chr(10) so only imports as one record.

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

With thanks to other contributors, below is what I have been using.

Ideally what I would like to do is:

select the csv file
copy the file to keep the original <<< extra function
clean up all hidden characters that would affect the import
then import

-

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 & import  ----------
    Sheets("Entries").Cells.ClearContents
    Set ws = ActiveWorkbook.Sheets("Entries") 'set to current worksheet name
    strFile = strFileName

    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _
    Destination:=ws.Range("A1"))
         .TextFileParseType = xlDelimited
         .TextFileCommaDelimiter = True
         .Refresh
    End With
End Sub

Any help would be most appreciated.

`If I open as a an excel file it is ok` If you can open it as an excel file then do that and then copy the sheet into your workbook rather than doing all that cleaning stuff? — Siddharth Rout, Sep 22 '13 at 05:45
Thanks very much for your tip Siddharth Rout - I had another go at that option and finally got it to work. Yes its a smarter approach. For any one elses reference here's what I used. (Had to add the Local:= True for local UK date format). Thanks again. Sub opencsv() strFile = Application.GetOpenFilename("Text Files (*.csv),*.*", , "Please selec text file...") strFileName = strFile Set src = Workbooks.Open(Filename:=strFile, Local:=True) Cells.Copy ThisWorkbook.Activate Sheets("Entries").Activate Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False src.Close End Sub — jon, Sep 22 '13 at 11:29
Gr8 :) Why don't you post it as an answer and then accept it? — Siddharth Rout, Sep 22 '13 at 11:36

score 1 · Answer 1 · edited Sep 22 '13 at 14:54

1

Sub opencsv()

    strFile = Application.GetOpenFilename("Text Files (*.csv),*.*", , "Please selec text file...")
    strFileName = strFile

    Set src = Workbooks.Open(Filename:=strFile, Local:=True)
    Cells.Copy
    ThisWorkbook.Activate
    Sheets("Entries").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    src.Close

End Sub

edited Sep 22 '13 at 14:54

Siddharth Rout

147,039
17
206
250

answered Sep 22 '13 at 12:59

jon

21
3

+ 1 for solving it yourself :) Next Tip: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select – Siddharth Rout Sep 22 '13 at 14:55

Importing csv file with hidden characters into Excel using vba

1 Answers1