TextToColumns convert values to dates (VBA)

[karpe86]

I am using VBA to open a csv file and split it to columns by using commando TextToColumns. After running the macro the hext has been split to columns but some values have been changed to dates, for example values starting with 07-5... When I do the same thing in Excel without using VBA those values are not changed. To make the conversion to columns I use below code and it is the same code that changes some of the values to dates.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

I have tried to change number format to '@' and '0' before and after the TextToColumns commando with no success. Can you please tell me how to solve this problem?

 

[Joe4]

Note all the "Array" values in that code. That tells Excel how to parse the data.
The first argument in the Array tells the field number. The second tells the data type.
All yours are set to "1", which is General, which lets Excel try to figure out the format of each field.
For values like "07-5", where it is not a date, you should be importing those fields as Text, which is "2". So you need to change that fields data type to "2".

Note the Macro Recorder will do this for you, if you do it correctly. On Step 3 of "Text to Columns" or "Import", they both give you the screen that lets you choose the format of each field.
Go to the correct field, and select "Text" instead of leaving it at "General".

Here is a description of Text to Columns in VBA code: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-texttocolumns-method-excel
and here is the numeric value of all the different options for the second argument of Array: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlcolumndatatype-enumeration-excel

 

[karpe86]

Note all the "Array" values in that code. That tells Excel how to parse the data.
The first argument in the Array tells the field number. The second tells the data type.
All yours are set to "1", which is General, which lets Excel try to figure out the format of each field.
For values like "07-5", where it is not a date, you should be importing those fields as Text, which is "2". So you need to change that fields data type to "2".

Note the Macro Recorder will do this for you, if you do it correctly. On Step 3 of "Text to Columns" or "Import", they both give you the screen that lets you choose the format of each field.
Go to the correct field, and select "Text" instead of leaving it at "General".

Here is a description of Text to Columns in VBA code: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-texttocolumns-method-excel
and here is the numeric value of all the different options for the second argument of Array: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlcolumndatatype-enumeration-excel

It works when I record the macro and run it afterwards, but not when I change '1' to '2' in the array. However, when I use below code to open the csv file I get the same error as before. Do you have any idea what is wrog?

<code>
strfil_1 = Application.GetOpenFilename
On Error GoTo 99

Workbooks.OpenText Filename:=strfil_1, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2)


'tilldelar den öppnade filen ett variabelnamn
strfil_1 = ActiveWindow.Caption
'stopp tilldelning av namn

Windows(strfil_1).Activate
</code)

 

[Joe4]

In your original code, you showed 8 different fields:

[COLOR=#333333]FieldInfo _[/COLOR]
[COLOR=#333333]:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _[/COLOR]
[COLOR=#333333]Array(7, 1), Array(8, 1)),[/COLOR]

In your new code, you only show one:

[COLOR=#333333][FONT=monospace]FieldInfo:=Array(1, 2)[/FONT][/COLOR]

How many fields do you really have?
Which field number is the one you are having issues with?
Can you post a bunch of examples of the values in this field?

 

[karpe86]

In your original code, you showed 8 different fields:

[COLOR=#333333]FieldInfo _[/COLOR]
[COLOR=#333333]:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _[/COLOR]
[COLOR=#333333]Array(7, 1), Array(8, 1)),[/COLOR]

In your new code, you only show one:

[COLOR=#333333][FONT=monospace]FieldInfo:=Array(1, 2)[/FONT][/COLOR]

How many fields do you really have?
Which field number is the one you are having issues with?
Can you post a bunch of examples of the values in this field?

After running the text to columns command I have 8 columns, and it is the values in the first column that in some cases are changed to dates. Is is the first part of the code opening the file that is causing this error. Below is the complete code I use to open the csv file and split to 8 columns.
<code>

Sub test()

strfil_1 = Application.GetOpenFilename
On Error GoTo 99


Workbooks.OpenText Filename:=strfil_1, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Comma:=False, Space:=False, Other:=False ', _
FieldInfo:=Array(1, 2)


'tilldelar den öppnade filen ett variabelnamn
strfil_1 = ActiveWindow.Caption
'stopp tilldelning av namn


Windows(strfil_1).Activate


Windows("Items.csv").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select Selection.FindNext(After:=ActiveCell).Activate
Windows("Tillgänglighetsmätning.xlsm").Activate
99:
End Sub

</code>

Here are some examples of values in the fist column:

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 64"]07-5012[/TD]

[TD="class: xl65"]07-5022[/TD]

[TD="class: xl65"]07-5024[/TD]

[TD="class: xl65"]07-5027[/TD]

[TD="class: xl65"]07-5030[/TD]

[TD="class: xl65"]07-5032[/TD]

[TD="class: xl65"]07-5033[/TD]

[TD="class: xl65"]07-5037[/TD]

[TD="class: xl65"]07-5040[/TD]

[TD="class: xl65"]07-5041[/TD]

[TD="class: xl65"]07-5043[/TD]

[TD="class: xl65"]07-5124[/TD]

</tbody>

[/TD]
[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
[TR]

[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
[TR]

[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
[TR]


[/TR]
[TR]


[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[karpe86]

I have discovered that the problem occurs due to below section of the code. When opening the csv file it is split into 8 columns and some of the values in the A-column are changed to dates. Any idea how to resolve this?

<code><code><code>

Sub test()

strfil_1 = Application.GetOpenFilename
On Error GoTo 99


Workbooks.OpenText Filename:=strfil_1, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Comma:=False, Space:=False, Other:=False ', _
FieldInfo:=Array(1, 2)
</code>

</code></code>

 

[Joe4]

I think the issue is that when you open CSV files like that directly in Excel, it ignores the data types you try to define and makes it own assumptions. It works if you open it in a slightly different manner. Here is the block of code to import the file:

    strfil_1 = Application.GetOpenFilename
    On Error GoTo 99
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strfil_1, _
        Destination:=Range("$A$1"))
        .Name = "Test"
        .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 = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With