TextToColumns convert values to dates (VBA)

K

karpe86

New Member
Joined
Dec 2, 2016
Messages
9
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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
 
Upvote 0
Joe4 said:
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
Click to expand...

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)
 
Upvote 0
In your original code, you showed 8 different fields:
Code: 
[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:
Code: 
[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?
 
Upvote 0
Joe4 said:
In your original code, you showed 8 different fields:
Code: 
[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:
Code: 
[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?
Click to expand...

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]
 
Upvote 0
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>
 
Upvote 0
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:
Code: 
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Delimiters in VBA has different result than manual
Replies
7
Views
140
Peter_SSs
Peter_SSs
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
348
MARK858
MARK858
J
  • Question Question
Text to Columns in Macro
2
Replies
10
Views
797
Alex Blakenburg
A
D
  • Question Question
VBA optimization advice
Replies
3
Views
418
bobsan42
bobsan42
I
  • Solved
Moving Data based off a condition
Replies
7
Views
409
ItalianPlatinum
I

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top