Import semicolon separated CSV file using VBA without rename to txt

Question

This is not a duplicate since I want a solution not constisting in reformatting file to txt:

my intention is to open a csv file using semicolon as delimiter. For that purpose I have used the following code:

Sub prueba2()


Dim sfile As String
Dim wb As Workbook
Dim Path As String
Dim Namefile As String


Path = "V:\evfilesce9i9\apps9\vbe9\dep4\KFTP\KFTP001D_FicherosCeca"
Namefile = "\QryCECARFSECTORIAL0239*.txt"


Set wb = Workbooks.Open(Filename:=Path & Namefile, Delimiter:=";")

End Sub

When I try it, it is opened using commas as delimiter instead of which I have specified (semicolon)

I have read in other questions that this is normal in post 2006 Excel versions, and that the fastest solution is to reformat file to a txt.

This does not fit into my needs because I have to do it without changing format. I don't find any solution.

Could someone help me?

Chip R. · Accepted Answer · 2018-06-19T14:53:43.263

Please see the MS documentation here.

I think you want to use the Format parameter, and not the delimiter parameter.

Try:

Set wb = Workbooks.Open(Filename:=Path & Namefile, Format:=4)

It seems like the Delimiter argument is only used if Format is set to 6, which signifies a custom delimiter character. Semi-colon is a standard delimiter.

Edit:

Hmm... so, this seems to be something that's been tricky in Excel/VBA for a while.

After some more research, the "Format" option may only be used when opening .txt files. Which is why the "reformat file to .txt" is one possible solution.

There are some things that can be done, however.

Excel will handle opening a semicolon delimited file well if the first line of the file is:

sep=;

I know you said you could not reformat the files, but is that something that you can do?

If not, the next things I would suggest would be to either: 1) use the Open Statement to open your file and then write it to a temporary file (perhaps as a .txt), to be reopened with the original Workbooks.Open(Format:=4), or 2) write your own text importer. A sample text importer can be found in this stackoverflow page.

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

Tried it but didn't work. I tried also ``Format:= 5`` and it was opened with a "rough" format instead of separated by commas (which happened when I used the code showed above), so, is a step forward but I don't still know how to open it directly in semicolon-delimiter format. — Mauro, Jun 19 '18 at 11:05
I have solved this in a more tricky way, which consist in use ``.TextToColumns`` method to separate columns by semicolon, since I got with your first tip to open it in a rough format (and not in a comma-separated way, which was what happend when I didn't set the format to 5.). Then I use ``.Close`` method with ``.SaveChanges := FALSE`` to not to modify permanently the file on which I want to read data. However, your answer is useful. — Mauro, Jun 20 '18 at 10:47

Import semicolon separated CSV file using VBA without rename to txt

1 Answers1