Can't import .CSV file from macro

[Hermanator]

Hello people,

I'm busy programming a simple macro. As part of this macro, a .CSV needs to be imported. The .CSV is formatted as follows:

0.500; 100
1.000; 200
1.500; 500

So a semicolon seperates different columns, and a line-end goes to the next row. I can open the .CSV files without any problem. When I double click the file, it is opened correctly in Excel, and also when I open the file using File, Open.

However, when I try to import a .CSV file from a macro, it places all the values of one row in one cell. So the contents of the cell is then: "0.500; 100". Delimiter settings are correct!

This leads to the following strange situation:
- I Select "Record New Macro"
- Using File, Open, I open the .CSV file. (It is opened correctly)
- I Select "Stop recording Macro"
- I close the .CSV file
- I run the macro that I have just recorded, but this time the file has been incorrectly imported!

I can check the macro, and all delimiter options are correct. It also doesn't matter wether I'm using

Workbooks.OpenText

or

Workbooks.Open

In both cases I select the semicolon ";" as the delimiter. But in both cases, Excel places all the values in the first cell.
Normally I can find a solution to any problem that I encounter, however, this time I have absolutely no clue!

 

[combo]

OpenText method needs parameters to force delimiter, for instance (see help file for details):
Workbiiks.OpenText FileName:="path\filename.csv", DataType:=xlDelimited, Semicolon:=True

combo

 

[rmikesmith]

Combo,

I've tried this method with appropriate params, as I suspect Hermanator has. Still no joy. What I have found as a workaround (doesn't really address the why question) is to change the file extension from csv to txt. The OpenText method with Semicolon:=True works properly in that case. ?? The only apparent alternative is the two-step approach: Open the csv then apply the TextToColumns method.


Regards,
Mike

 

[Hermanator]

I know that both OpenText and Open require paremeters. In both cases I correctly use the parameters to set the import options to Delimited, and set the semicolon as delimiter. All of the following options do not work. The file is is opened, but the data of 1 row is put in a single cell.

Workbooks.Open Filename:="data.csv", _
    Format:=4

Workbooks.Open Filename:="data.csv", _
    Format:=6, _
    Delimiter:=";"

Workbooks.OpenText Filename:="data.csv", _
    DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, _
    Semicolon:=True

 

[combo]

Sorry, I haven't fully tested it. For me, Workbooks.OpenText works in excel 2k, in xp - nope. However, it works with additional parameter set:

Workbooks.OpenText Filename:="c:\test.csv", DataType:=xlDelimited, Semicolon:=True, Local:=True

combo

 

[xlbo]

To be honest - what do you expect - you have a Comma Seperated Values File that is not seperated by Commas.

Lesson is not to save things as .CSV unless they really are .CSV - what you have there is a .TXT file that has been saved with a different extension - it therefore needs to be opened using the import specifications dialog like a standard .TXT file

Having said all that, combo's last post does shed some light in that it might appear your local settings use ; instead of , . In which case, combo's last suggestion should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HughLerwill]

I got into this a while back when dealing with ".csv"s containing numbers formatted in Euro style ie. using the comma as the decimal point character. Comma separation of fields in such a file is of course useless. I wanted the solution to accomodate old and new versions of Excel.

The best approach I found was to only use the .csv extension/ handling in Excel when dealing with true .csv (comma separated values) files.

Use the .txt extension/ handling when dealing with files delimited with other characters.

Your file is not a true csv file.

Hugh

 

[Hermanator]

Whoohoo!

Thanks combo! Local:=True does the trick! Just curious: what does Local :=True do actually? The VBA help file doesn't say.

About the .CSV format; I realise that using a semicolon as a separater isn't the correct format for a comma separated file. However, when I start a blank workbook in Excel, enter a few values and export the sheet as a .CSV, it uses a semicolon as separater. Also I can just correctly open a semicolon-separated .CSV file by double clicking it. It might have something to do with my regional settings.
Although most of the time, I use the dot "." as decimal separater, once in a while I use the "," as decimal separater (which is according to our European standard). That's why I find it very handy to use the semicolon as separater. No matter what decimal separater I use (. or ,) it gets imported correctly using "semicolon-separated" .CSV files. (Maybe I should call them .SCV files).

Thanks all for your help.

 

[xlbo]

local = true will allow your local settings to override default settings - that is why it allows you to use a ; seperator in a .csv file

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

Sometimes MS solutions are far from logic: excel saves csv file using regional settings list separator, but always 'dot' as decimal separator. One could expect that setting 'True' to Semicolon parameter of OpenText, as available, would force the separator usage, but it is so for 'txt' files. For csv it is necessary to set 'Local' (BTW, what happens when the list separator is comma and we use semicolon and Local:=True?).

A chapter of Excel 2002 VBA Programmer's Reference (by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg, Wrox Press) on international aspects of excel is here (including OpenText and much more):

http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

combo