Opening a CSV file with VBA/Excel

[darvistor]

Hello all,

Does anyone know how to open an internationally formatted CSV file using VBA? The CSV file uses the semicolon as the column separator and the comma as the decimal point.

I created a userform that enables a user to point to a data directory and open a series of files that are incrementally numbered such as: p1_001.csv, p1_002.csv and so on.

I can open the files manually in Excel 2003 if I change the international options to not use the Systems Separators, then open the file, and then turn the Systems Separators option back on. This opens the file with the correct format so that I can copy the data to another sheet and move on with the next file.

I tried to automate this process, but I can't get the file to open with the right settings, which causes the data to become jumbled. Here are two options that I tried in my userform code.

 'Option 1
 ChDir filepath
        With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = ","
        .UseSystemSeparators = False
    End With

Application.Workbooks.Open fname

         With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = ","
        .UseSystemSeparators = True
        End With

    'Option 2
    Application.Workbooks.OpenText Filename:= _
        fname, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers _
        :=False

Note: fname and filepath are two declared string variables.

 

[Chance1234]

this might be your problem

TextQualifier:= xlDoubleQuote

Chance,

F, G + _HH

 

[darvistor]

I'm not sure what to change, but turning the "ConsectutiveDelimiter:=False" doesn't help.

I tried a third option that should work but I am not that familiar with the data query in Excel.

 With ActiveSheet.QueryTables.Add(Connection:= _
        fpname, _
        Destination:=Range("A1"))
        .Name = sname
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileDecimalSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I get the Runtime error '1004' when I execute this new code. Note: The sname variable is the short name of the file without the extension and fpname variable is the full name of the file including the path. Maybe I am missing a "" or , but if I do the data import manually with these options the file is opened with the right formatting.

 

[Chance1234]

The CSV file uses the semicolon as the column separator

TextQualifier:= _
xlDoubleQuote

have a look in VBA help for TextFileSemicolonDelimiter Property

Chance,

F, G + _HH

 

[darvistor]

Sorry Chance1234, I am not a VBA expert or anything. I have no idea what you are trying to get at. I looked at the help and looked at Google searching on "TextFileSemicolonDelimeter property" and I still don't know what the problem is. Are you trying to tell me the TextQualifier should be set to something else and if so what?

 

[Chance1234]

Yes,

This bit of code
TextQualifier:= xlDoubleQuote

here is sayign that the CSV file you are trying to import is using " as a qualifier,

as stated in your opening post, you are using a semi colon

so that line for starters needs to be

TextQualifier:= xlSemiColon

That help file i pointed to you to, has code in the example for querying a datasource which has ; as a dataqualifier.



Chance,

F, G + _HH