TextToColumns in Excel with win32com and arcpy

[KateJohnson]

I have searched extensively but can't really find any good documentation on using the TextToColumns method within Excel using win32com. The VB developer help gives the following example, but translating the parameters into Python has been a challenge.

VBA:

Worksheets("Sheet1").Activate
ActiveSheet.Paste
Selection.TextToColumns DataType:=xlDelimited, _
  ConsecutiveDelimiter:=True, Space:=True

The code I currently have takes 2 fields in a shapefile and concatenates them together with a "_" for unique identifier summarizing purposes while in ArcGIS, i.e.,

exRock = "!ROCKTYPE1!+'_'+!ROCKTYPE2!"

Once the tables are exported to DBF and then converted to an XLS file, I want to separate the fields again based on the "_".

If there is an easier way to do this without Excel any insights would be appreciated! I have tried using the .partition() method and csv files/strings however I can't get that to work correctly either.

[KevinBell]

the split method on strings in python should do it:

s = 'hello world'

a,b = s.split(' ')

a

hello

b

world

I'm splitting on a single whitespace here, but you could use a comma or another delimter the same.

[OwenEarley]

Why not just include the two original fields in the DBF as well as the concatenated ID value?

If you have to do this in Excel you can use formulas:

	A	B	C
1	ExRock	RockType1	RockType2
2	Andesite_Basalt	Andesite	Basalt
3	Basalt_Granite	Basalt	Granite

ROCKTYPE1

=LEFT(A2,(FIND("_",A2) - 1))

ROCKTYPE2

=RIGHT(A2, LEN(A2) - FIND("_",A2))

Otherwise, if you are processing this within python then use split('_") as Kevin suggests.

[KateJohnson]

Thanks to you both for some great insight! Owen, your suggestion of adding both original fields to the DBF motivated me to look at the documentation for arcpy.Statistics_analysis(), which is what I was using originally to create it, and gave me the answer!

A second look at the documentation shows that you can actually use a combination of fields to create unique values for the case field on which to summarize. The code I was using originally was this:

arcpy.Statistics_analysis(outputPoly, outputDBFTable, [["LithPerc", "SUM"]], "ConcatField")

The code I ended up using that worked was this:

arcpy.Statistics_analysis(outputPoly, outputDBFTable, [["LithPerc", "SUM"]], ["ROCKTYPE1","ROCKTYPE2"])

This allows you to summarize unique values but also maintain the original, separate fields. Cool!