Workbooks.OpenText ignoring FieldInfo column parameter

Question

I have the below line to import a csv format file.

 Workbooks.OpenText Filename:=sPath, DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(Array(18, 5), Array(19, 5)), Local:=True

From microsoft's documentation here, the FieldInfo doesn't have to be in any order if it is in delimited.

The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting.

However excel seems to treat the first array as 1st column and 2nd array as 2nd column no matter what I put in the first parameter Array(Array(x, 5), Array(y, 5)). So to reach the 18th & 19th column, I have to do this, which isn't pretty:

Workbooks.OpenText Filename:=sPath, DataType:=xlDelimited, Comma:=True, _
    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), _
    Array(9, 1), _
    Array(10, 1), _
    Array(11, 1), _
    Array(12, 1), _
    Array(13, 1), _
    Array(14, 1), _
    Array(15, 1), _
    Array(16, 1), _
    Array(17, 1), _
    Array(18, 5), _
    Array(19, 5)), _
    Local:=True

csv file sample data:

fill_c1,pick_n2,po_num3,quanti4,addres5,cust_s6,color_7,size_d8,style9,shipto10,shipto11,addres12,addres13,city14,state15,zipcod16,custom17,start_18,end_da19,udford20
"52","1","2","000000001","000000000000000000000000000000","6","Z","XS","7","","","","","","","","M",20190310,20190318,"CF3"
"52","1","2","000000002","000000000000000000000000000000","6","Z","S","7","","","","","","","","M","20190310","20190318","CF3"

That line just opens the CSV file for me... What are you trying to do once it's open? — dwirony, Mar 13 '19 at 21:27
That line should open the CSV but also format the 18th & 19th column as date MDY. To make the issue more apparent you can try Array(18, 9), Array(19, 9), which should NOT import those 2 columns, however all it disables are column 1 & 2. — Alf, Mar 13 '19 at 21:48
Very interesting - I'm taking a look into this right now. Btw value 5 is "YMD" format, Value 3 is MDY. — dwirony, Mar 14 '19 at 14:48
Take a look at this post [here](https://stackoverflow.com/questions/42197238/workbooks-opentext-not-parsing-csv-files-properly-excel-2016), this might help explain what's going on... — dwirony, Mar 14 '19 at 15:01

dwirony · Accepted Answer · 2019-03-15T16:20:26.050

I was able to reproduce this issue using a .txt file and using Tab=True:

Workbooks.OpenText Filename:=Path & "Testfile.txt", DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(Array(18, 9), Array(19, 9)), Local:=True

And by using value 9 (xlSkipColumn) I attempted to omit columns 18 and 19, but columns 1 and 2 were skipped instead (Test1 and Test2):

The documentation for clearly says "The column specifiers can be in any order", but this looks to be incorrect. The first element of the first array will always be column 1, the first element of the second array will always be column 2, and after all the arrays have been iterated the rest of the columns will be parsed with the General setting.

IMO, this looks like a bug to me. If it's not a bug, then the documentation is very confusing and needs to be re-written.

score 2 · Answer 2 · answered Mar 15 '19 at 00:20

This indeed looks like a bug or the documentation is misleading. One workaround to avoid ugly code would be to import the text file without delimiting it and using TextToColumns to split it. Something like that (which oddly enough seems to work):

Workbooks.OpenText Filename:=sPath, DataType:=xlDelimited, Comma:=False

Columns("A:A").TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    FieldInfo:=Array(Array(18, 4), Array(19, 4))

Workbooks.OpenText ignoring FieldInfo column parameter

2 Answers2

Linked