Paul_Hossler
11-01-2012, 07:48 PM
I have a CSV with 100+ fields.
For almost all of them xlGeneralFormat will work. For a few, xlTextFormat is needed, usually to preserve leading 0's
I'd like to just specify the few exceptions in FileInfo and let xlGeneralFormat be assumed for all the others
Online help seems (to me at least) to allow this
The column specifiers can be in any order. If a given column specifier is not present for a particular column in the input data, the column is parsed with the xlGeneralFormat setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the xlGeneralFormat setting.
If I specify all FileInfo for all the columns, it works (first sub), but if I only specify the few exceptions, then it appears to be ignored (second sub)
Option Explicit
Sub Works()
ActiveSheet.Columns(1).TextToColumns Destination:=ActiveSheet.Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array( _
Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), _
Array(3, xlGeneralFormat), Array(4, xlTextFormat), _
Array(5, xlGeneralFormat), Array(6, xlTextFormat) _
), _
TrailingMinusNumbers:=True
End Sub
Sub DoesNotWork()
ActiveSheet.Columns(1).TextToColumns Destination:=ActiveSheet.Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array( _
Array(4, xlTextFormat), Array(6, xlTextFormat) _
), _
TrailingMinusNumbers:=True
End Sub
In my little test XLSM, there's strings with 6 fields
AAAAAAAAAAAAAAA,BBBBBBBBBBBBBBBBBB,CCCCCCCCCCCCCCC,00012345,EEEEEEEEEEEEEEE EEEEEEEEEEEE,9876,GGGGGGGGGGGGGGGGGG
The 4th and 6th should be converted as strings 00012345 and 9876
I really don't want to have to maintain 100+ FileInfo arrays if I don't have to
Is there something I'm missing?
Thanks
Paul
For almost all of them xlGeneralFormat will work. For a few, xlTextFormat is needed, usually to preserve leading 0's
I'd like to just specify the few exceptions in FileInfo and let xlGeneralFormat be assumed for all the others
Online help seems (to me at least) to allow this
The column specifiers can be in any order. If a given column specifier is not present for a particular column in the input data, the column is parsed with the xlGeneralFormat setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the xlGeneralFormat setting.
If I specify all FileInfo for all the columns, it works (first sub), but if I only specify the few exceptions, then it appears to be ignored (second sub)
Option Explicit
Sub Works()
ActiveSheet.Columns(1).TextToColumns Destination:=ActiveSheet.Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array( _
Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), _
Array(3, xlGeneralFormat), Array(4, xlTextFormat), _
Array(5, xlGeneralFormat), Array(6, xlTextFormat) _
), _
TrailingMinusNumbers:=True
End Sub
Sub DoesNotWork()
ActiveSheet.Columns(1).TextToColumns Destination:=ActiveSheet.Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array( _
Array(4, xlTextFormat), Array(6, xlTextFormat) _
), _
TrailingMinusNumbers:=True
End Sub
In my little test XLSM, there's strings with 6 fields
AAAAAAAAAAAAAAA,BBBBBBBBBBBBBBBBBB,CCCCCCCCCCCCCCC,00012345,EEEEEEEEEEEEEEE EEEEEEEEEEEE,9876,GGGGGGGGGGGGGGGGGG
The 4th and 6th should be converted as strings 00012345 and 9876
I really don't want to have to maintain 100+ FileInfo arrays if I don't have to
Is there something I'm missing?
Thanks
Paul