Workbooks.OpenText忽略FieldInfo列参数 [英] Workbooks.OpenText ignoring FieldInfo column parameter

问题描述

我在下面一行中导入了csv格式文件.

  Workbooks.OpenText文件名:= sPath，数据类型:= xlDelimited，逗号:= True，FieldInfo:= Array(Array(18(5)，Array(19，5))，Local:= True 

从Microsoft文档

文档明确表示列说明符可以按任何顺序排列"，但这似乎是不正确的.第一个数组的第一个元素将始终为列1，第二个数组的第一个元素将始终为列2，并且在对所有数组进行迭代之后，其余的列将使用常规"设置进行解析.

IMO，这对我来说似乎是个错误.如果不是错误，则说明文档非常混乱，需要重新编写.

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"

解决方案

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.

这篇关于Workbooks.OpenText忽略FieldInfo列参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！