VBA to open .CSV files

[Tim_Excel_]

Hi forum,

I have a vba module with which I want to open .CSV files delimited by the | character. I couldn't get it to work, so I instead worked around it by having excel open a nonsense file (.cvs, but it does work). Since this isn't very handy for the user, I'd like to look at it again and open .CSV files instead.
I came across this code:

<code>Sub ImportCSVFile(filepath As String)
    Dim line As String
    Dim arrayOfElements
    Dim linenumber As Integer
    Dim elementnumber As Integer
    Dim element As Variant

    linenumber = 0
    elementnumber = 0

    Open filepath For Input As #1 ' Open file for input
        Do While Not EOF(1) ' Loop until end of file
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, "|")

            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1 ' Close file.
End Sub
</code>

but it doesn't seem to open it delimited by the | character, plus it's being opened INSIDE the already opened workbook.

The code I have now:

     Workbooks.OpenText FileName:="" & inpt & "", _
        Origin:=65000, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True

this works perfectly with nonsense files, but refuses to work with .CSV files.

 

[wideboydixon]

I've seen this before and it's actually down to the file extension. When Excel "sees" a file with a CSV (Comma Separated Values afterall!) extension, it mandates that the text is separated by commas. Changing the file extension will work for your code. Alternatively, you could adapt the first piece of code to add a new workbook first:

Sub ImportCSVFile(filepath As String)
    Dim line As String
    Dim arrayOfElements
    Dim linenumber As Integer
    Dim elementnumber As Integer
    Dim element As Variant
    Dim newSheet As Worksheet


    linenumber = 0
    elementnumber = 0
    Set newSheet = Workbooks.Add().Sheets(1)


    Open filepath For Input As #1 ' Open file for input
        Do While Not EOF(1) ' Loop until end of file
            linenumber = linenumber + 1
            Line Input #1, line
            arrayOfElements = Split(line, "|")


            elementnumber = 0
            For Each element In arrayOfElements
                elementnumber = elementnumber + 1
                newSheet.Cells(linenumber, elementnumber).Value = element
            Next
        Loop
    Close #1 ' Close file.
End Sub

WBD

 

[Tim_Excel_]

WBD,

Although the code now opens in a new wb (thanks for that), the code actually puts everything in one line. It does delimit for the | character, but it just puts all characters after a | in a new column.

 

[wideboydixon]

Strange; it was working for me. Perhaps your "CSV" file does not have Windows line endings so "Line Input" just reads the whole thing in a single line? Alternatively you could copy the input file before importing it although then you're left with an extra file:

Sub ImportCSVFile(filePath As String)


FileCopy filePath, filePath & ".tmp"


Workbooks.OpenText Filename:=filePath & ".tmp", _
        Origin:=65000, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True


End Sub

WBD

 

[Tim_Excel_]

Thank WBD, this could work when I just delete that redundant file after the code has been executed. However, it's still a bit of a workaround. Anyhow, it seems this would be my only option.

If any other geniuses might see what's causing the problem, I'd be happy to hear it

 

[Tim_Excel_]

WBD,
I'm getting a 'Permission Denied' error, which is odd since in the same workbook I safe files to another directory. What's the cause of this error?

 

[wideboydixon]

Perhaps you don't have permission to create files in the same folder. This would use your TEMP folder:

Sub ImportCSVFile(filePath As String)

Dim tempFile As String

tempFile = Environ("TEMP")
If Right$(tempFile, 1) <> "\" Then tempFile = tempFile & "\"
tempFile = tempFile & Format$(Now, "yyyymmddhhmmss") & ".tmp"

FileCopy filePath, tempFile

Workbooks.OpenText Filename:=tempFile, _
        Origin:=65000, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True

End Sub

WBD

 

[Tim_Excel_]

I had already tried writing the destination path as the desktop, but this did not work either. This has me thinking I don't have permission to copy said file.. Odd