Importing CSV file via VBA into Excel won't seperate data into different columns

Question

I have a CSV file filename.csv with the following content:

UID;Datum;Klasse;SendungsNr
6177;14.08.20;624;00340434299338038179
6178;14.08.20;624;00340434299338038186
6179;14.08.20;624;00340434299338038193

As far as I understand (here, here, here for the MS reference, etc. ...) this should import the file an insert the data into different columns:

Public Sub OpenCsvFile()
    filepath = "C:\folder 1\folder 2\filename.csv"
    Workbooks.OpenText Filename:=filepath, DataType:=xlDelimited, Semicolon:=True
    End Sub

But it doesn't (see "update" below - seems to be a problem with the filename), it just uses the A column:

I tried specifying it over the other flag but the result stays the same:

Workbooks.OpenText Filename:=filepath, DataType:=xlDelimited, Other:=True, OtherChar:=";"

Since this can not be that difficult, I guess I'm just "blind" today, where's the problem?

Update: I found out that changing ".csv" to ".txt" seems to do the trick: it opens it into separate columns. Is that a bug or the desired behavior? Did I overlook anything in the MS reference? Now the question is, how do I get it to work without renaming the files (if it can be done at all)?

Update2: The solution, for whatever reason is to use Local:=True as a parameter (see the second link in the duplication notice, and some info is here as well). Why this has anything to do with the file suffix I can not fathom.

@Tarik it uses "Workbooks.Open" (or some other procedures depending on how excatly I open it) which doesn't help me in this case. — Albin, Aug 16 '20 at 09:35
Your code works as expected with your sample data. In which encoding is your file saved? — GSerg, Aug 16 '20 at 09:45
@GSerg interesting what sample data did you use? So now the question is, why doesn't it work with this specific data, and how can I make it work... :S — Albin, Aug 16 '20 at 09:47
@GSerg I ment which did you use to make it work? But never mind, I already found part of the problem (see update addition at the end of my question) — Albin, Aug 16 '20 at 09:54
Do you mean changing of "filename.csv" with "filename.txt"? If yes, this only means that your file were/are not csv... — FaneDuru, Aug 16 '20 at 10:00
@FaneDuru yes, but I wonder what I have to change (to make it csv). You can see my file's conents in the quesiton (the real file has just a few lines more). — Albin, Aug 16 '20 at 10:40
I cannot get you... When and why to make it/them csv? If they **already exist** and they are of txt type, you can open them using their existing name, like you said. Do you want saving the file after processing as csv? I cannot understand what you want, sorry... Do you mean that you manually rename the csv files in txt ones? IF yes, this is something completely strange. It should be open in exactly the same way if you use the initial extension (csv). How does it behave if you open it manually? But if you use the same settings (Checking Other:=True, OtherChar:=";")... — FaneDuru, Aug 16 '20 at 11:09
@GSerg: Yes, it depends on the list separator (from localisation)... I wanted to see what result he obtains trying to manually open the csv file, using similar settings. — FaneDuru, Aug 16 '20 at 11:33
@GSerg: I mean `Local:=True or False` should solve the problem, according to the manually opening test result... — FaneDuru, Aug 16 '20 at 11:49
@FaneDuru I don't make the files, I get them "ready" made from another tool as `filename.csv`. And yes my test was manually changing the filename to `filename.txt`. So you are correct it is s.th. completely strange. Anyway 'Local:=True' seems to do the trick (see Update2 at the end of my question), I'll have to check if it causes other problems though. Anyway thanks for the help! — Albin, Aug 16 '20 at 14:22
@GSerg for now, it seems to be solved, (see update2), thanks for the help. — Albin, Aug 16 '20 at 14:26

Importing CSV file via VBA into Excel won't seperate data into different columns

0 Answers0