Opening semicolon delimited CSV file

Question

How does one open a semicolon delimited CSV file with VBA in Excel 2000?

Sample data

An ID;TEST20090222
A Name;Firstname Surname
A Date;11.05.2000

Country:;SomeCountryName
Currency;EUR
CostA;
CostB;
CostC;
Part1;10;20;30
Part2;;;;
Part3;34;56;87

Code

In Excel 2003 11.8231.8221 SP3 with VBA 6.5.1025, I can open a semicolon delimited file with the following VBA code:

Workbooks.OpenText filename:=myFilename, _
    DataType:=xlDelimited, Semicolon:=True, Local:=True

However, when the same code is run in Excel 2000 9.0.8961 SP1 with VBA 6.5.1025, I get the following error:

Compile error: Named argument not found

That is --I think-- because Excel 2000 doesn't know the named argument "Local".

Therefore, I deleted the "Local:=True" part. But the problem then is that an entire line from the CSV file is written into one cell instead of being split up into the separate semicolon delimited parts.

I have searched the Internet for a solution, but did not find anything useful and concise.

Any ideas?

[Update 17.02.2009]

I tried the suggestion from user lc with the macro recorder. However, the results were confusing.

When I open the CSV file with menu File->Open... and then select the CSV file, the semicolon separated data is correctly parsed. And the recorded code is as simple as:

Workbooks.Open filename:= _
               "D:\testdata\Example 01 CSV\input.csv"

But when I use that VBA code in my macro, each line ends up in one cell again.

According to the suggestion from user barrowc, I also changed the The Windows "Regional and Language Options" settings from "German (Switzerland)" to "English (United States)". Even after restarting Excel, nothing changed, same problem.

I wonder why it is working on user Remou's system. What regional and language settings do you have?

Answer 1

I prefer:

Workbooks.Open fileName:=myFilename, UpdateLinks:=False, Local:=True

Answer 2

Re Any ideas?

If you want to fix a file for others using excel add this on the first line of the file without the quotes followed by a linebreak: "sep=;”

An easy way to manually open stupid files is to rename the extension to .txt or .htm then from excel use File - Open.

From VBA I recommend looking up the method in MSDN and manually specifying every parameter, my experience was that this eliminated most regional issues.

Answer 3

often the comma is ticked for true as a separator where it is usually the decimal separator. Add DecimalSeparator:="," and Bob's your uncle

Answer 4

[Update 22.02.2009]

In the meantime, I solved the problem by writing an import function myself instead of using Workbooks.OpenText.

I just open the CSV file as a text file, read line by line, split each line into the semicolon separated elements and write each element into a cell.

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

Got the inspiration from Shasur: http://vbadud.blogspot.com/2007/06/vba-read-text-files-with-leading.html

I still do not know why Workbooks.OpenText does not work on my system even though it seems to work on user Remou's system. I guess it might have something to do with the operating system language (English) and the regional and language settings (German, Switzerland), but I am not sure.

Anyway, the workaround works for me. Thank you all for you suggestions and help!

Answer 5

One more workaround - just rename .csv files to .txt and use OpenText method.

Answer 6

Here's the OpenText method from Excel 2000:

OpenText Method

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

Syntax

expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator)

source

and here's the Excel 2003 version:

OpenText Method [Excel 2003 VBA Language Reference]

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

source

so Local was indeed a new parameter for Excel 2003 and won't work in Excel 2000

No idea as to the cause of the erroneous behaviour. The Local parameter is defined as:

Local Optional Variant. Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

You might want to double-check the regional settings on the Excel 2000 PC and check to see if there is anything which may cause the data to be wrongly interpreted. Also, try explicitly specifying the DecimalSeparator and ThousandsSeparator parameters on the Excel 2000 method and see if that helps

Answer 7

I find that this works for me in Excel 2000:

Workbooks.OpenText filename:=myFilename, _
    DataType:=xlDelimited, Semicolon:=True

Answer 8

Not sure, but you can try recording a macro to do the same thing and check the VBA code it produces. You might get a hint there as to what's missing.