Opening a comma delimited CSV file

Question

How to open CSV file with semicolon using VBA in Excel 2000?

Data examples

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 

the code

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

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

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

Compilation Error: Named argument not found

That is - I think ... because Excel 2000 does not know the named argument "Local".

So I removed the "Local: = True" part. But then the problem is that the whole line from the CSV file is written in one cell, and not split into separate parts with a comma.

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

Any ideas?

[Update 02.17.2009]

I tried the user lc suggestion with a macro recorder. However, the results were incomprehensible.

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

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

But when I use this VBA code in my macro, each line again falls into one cell.

As suggested by user barrowc , I also changed the Windows Regional and Language Settings from German (Switzerland) to English (United States). Even after restarting Excel, nothing has changed, the same problem.

I wonder why he is working on the Remou user system . What regional and language settings do you have?

Answer 1

[Update 02.22.2009]

At the same time, I solved the problem by writing an import function myself, instead of using Workbooks.OpenText.

I simply open the CSV file as a text file, read line by line, breaking each line into semicolon-separated ones and writing each element to 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 

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

I still don’t know why Workbooks.OpenText does not work on my system, even if it works on the Remou user system . I suppose this may have something to do with the language of the operating system (English) and the regional and language settings (German, Switzerland), but I'm not sure.

Anyway, a workaround works for me. Thank you for your suggestions and help!

Answer 2

Not sure, but you can try writing a macro to do the same, and check out the VBA code that it creates. You can get a hint about what is missing.

Answer 3

I find this works for me in Excel 2000:

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

Answer 4

Here's the OpenText method from Excel 2000:

OpenText Method

Loads and analyzes a text file as a new book with one sheet, which contains the analyzed text files.

Syntax

expression.OpenText (File name, Origin, StartRow, DataType, TextQualifier, Sequential separator, tab, semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator)

a source

and here is the version of Excel 2003:

OpenText Method [Excel 2003 VBA Language Reference]

Loads and analyzes a text file as a new book with one sheet, which contains the analyzed text files.

expression.OpenText (FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, semicolon, comma, space, other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

a source

therefore Local indeed a new parameter for Excel 2003 and will not work in Excel 2000

There is no idea about the cause of the erroneous behavior. The Local parameter is defined as:

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

You might want to double-check the regional settings on the Excel 2000 computer and check if there is anything that could lead to incorrect data interpretation. Also, try explicitly specifying the DecimalSeparator and ThousandsSeparator parameters in the Excel 2000 method and see if this helps

Answer 5

Another workaround is to simply rename the CSV files to .txt and use the OpenText method.

Answer 6

I prefer:

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

Answer 7

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

Answer 8

Re any ideas?

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

An easy way to manually open stupid files is to rename the extension to .txt or .htm, and then from the Excel file, Open.

From VBA, I recommend looking for a method on MSDN and manually specifying each parameter, my experience is that this fixes most of the regional issues.