PDA

View Full Version : Solved: TextToColumns question



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

shrivallabha
11-02-2012, 01:36 AM
I tried to run : DoesNotWork and it doesn't work as intended:doh:

It seems that the column number passed through Array is not being read correctly. Excel is somehow applying settings on 1st and 2nd column and not on 4th and 6th column.

I am having the same issues as you did. I have Excel 2007 [just in case if it is some version issue].

Edit:I should have used search facility. See Bob's method of building array:
http://www.vbaexpress.com/forum/showthread.php?t=28668

Teeroy
11-02-2012, 02:47 AM
You named the routines well and they work exactly as described :rotlaugh:.

I tried in excel 2003 and got the same thing but I did find that it is not a new issue, see http://www.mrexcel.com/forum/excel-questions/138652-visual-basic-applications-texttocolumns-fieldinfo-not-working.html. (http://www.mrexcel.com/forum/excel-questions/138652-visual-basic-applications-texttocolumns-fieldinfo-not-working.html)

As @shrivallabha has suggested you can use XLD's technique to generate the Array or simply modify the CSV to enclose those values in quotes then you don't need to specify any Fieldinfo.

dantzu
11-02-2012, 02:56 AM
I believe the above mentioned Bob's method is the resolution. You have to have all fields specified. If you skip FiledInfo at all, all fileds are considered as GENERAL format.

snb
11-02-2012, 03:54 AM
another way to overcome this omission:


sub M_snb()
with sheets("control")
.Columns(1).Replace ",0", ",'"
.Columns(1).TextToColumns , , , , False, False, True, False, False
.Cells(1).CurrentRegion.Value = .Cells(1).CurrentRegion.Value
end with
end sub

Paul_Hossler
11-02-2012, 06:01 AM
Guess I'll have to live with it :banghead:

Shrivallabha -- Thanks for Bob's link; I'll have to go that method. I Googled all last night and didn't find that technique

snb -- Leading 0's is not the only issue; things like ...,10-4308, ... get 'fixed' as a date, 10/1/4308, and then converted as a long = 89123.

All -- thanks for verifying that it is a problem (and that it's not my problem)

Paul