topic Re: TextToColumns in Excel with win32com and arcpy in Python Questions

TextToColumns in Excel with win32com and arcpy

KateJohnson — Sun, 12 Dec 2021 01:03:51 GMT

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.

Re: TextToColumns in Excel with win32com and arcpy

KevinBell — Tue, 09 Dec 2014 20:50:12 GMT

the split method on strings in python should do it:

s = 'hello world'

a,b = s.split(' ')

hello

world

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

Re: TextToColumns in Excel with win32com and arcpy

OwenEarley — Tue, 09 Dec 2014 21:24:39 GMT

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.

Re: TextToColumns in Excel with win32com and arcpy

KateJohnson — Tue, 09 Dec 2014 21:53:46 GMT

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!