By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,695 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,695 IT Pros & Developers. It's quick & easy.

Text file to Excel file

P: 34
Greetings!

I am still new to Python, sorry! I have been searching through many posts on this subject and have attempted to TRY, but I feel really lost. So, any detailed guidance would be oh-so helpful for my learning at this point!

So, I am working with GIS data using ESRI software. I spatially select data from numerous sources and write it to a > delimited text file. I then manually bring that file into Excel, to then pass onto users to do their thing with. How do I automate this process?

**A snippet of code:
writeFile.write("CATEGORY> TYPE> NAME> ADDRESS> CITY> STATE> COUNTY> MISC_INFO> STATUS> COMMENTS> POINT_X> POINT_Y" + "\n")

curLocalPolice = gp.searchcursor(t_localPolice)
rowLocalPolice = curLocalPolice.Next()
while rowLocalPolice:
writeFile.write("SECURITY> LOCAL POLICE> " + rowLocalPolice.Name + "> " + rowLocalPolice.address +
"> " + rowLocalPolice.city + "> " + rowLocalPolice.state + "> " + rowLocalPolice.county +
"> " + "ph(" + str(rowLocalPolice.area_) + ")" + str(rowLocalPolice.phone) +
"> > > " + str(rowLocalPolice.point_x) + "> " + str(rowLocalPolice.point_y) + "\n")
rowLocalPolice = curLocalPolice.Next()


**which produces a text file that looks like this (way more records then this, of course):
SWEAT_CATEGORY> TYPE> NAME> ADDRESS> CITY> STATE> COUNTY> MISC_INFO> STATUS> COMMENTS> POINT_X> POINT_Y
SECURITY> LOCAL POLICE> EAST WHITELAND TOWNSHIP POLICE DEPT HEADQUARTERS> 209 CONESTOGA RD> MALVERN> PA> CHESTER> ph(610)647-2100> > > -75.5554538863> 40.050059662
SECURITY> LOCAL POLICE> EAST VINCENT TOWNSHIP POLICE DEPT HEADQUARTERS> 262 RIDGE RD> SPRING CITY> PA> CHESTER> ph(610)933-0115> > > -75.5880005882> 40.1617776533
SECURITY> LOCAL POLICE> DOWNINGTOWN POLICE DEPT HEADQUARTERS> 10 W LANCASTER AVE> DOWNINGTOWN> PA> CHESTER> ph(610)269-0263> > > -75.7061431299> 40.0048218854
SECURITY> LOCAL POLICE> UWCHLAN TOWNSHIP POLICE DEPT HEADQUARTERS> 717 N SHIP RD> EXTON> PA> CHESTER> ph(610)363-6947> > > -75.6305628031> 40.0487863475
SECURITY> LOCAL POLICE> PHOENIXVILLE POLICE DEPT HEADQUARTERS> 140 CHURCH ST> PHOENIXVILLE> PA> CHESTER> ph(610)933-1180> > > -75.5135260786> 40.1328893105
SECURITY> LOCAL POLICE> MALVERN POLICE DEPT HEADQUARTERS> 1 E 1ST AVE> MALVERN> PA> CHESTER> ph(610)647-0261> > > -75.5125783923> 40.033827335

**which I then maually bring into Excel as a > delimited file, do some column adjustments and save as a CSV file so that it can then be brought into ArcMap (GIS mapping and analysis software).


How do I automate in Python 2.4? I have this/below bit of general code. Is this right? Do I need to build a dictionary with my text file? How? Is this code inserted at the end of my script, once all data has been put to the text file?

import sys, string, os, win32com.client
gp = win32com.client.Dispatch("esriGeoprocessing.GpDisp atch.1")

#import arcgisscripting, sys
#gp = arcgisscripting.create()

worksheet = gp.GetParameterAsText(0)
inValue = gp.GetParameterAsText(1)

#worksheet = "d:\\test\\excel\\test.xls"
#inValue = 10

xlApp = win32com.client.Dispatch("Excel.Application")
#xlApp.Visible = 1
xlApp.Workbooks.Open(worksheet)
xlApp.ActiveSheet.Cells(1,1).Value = inValue
xlApp.ActiveSheet.Calculate()
outValue = xlApp.ActiveSheet.Cells(2,1).Value
xlApp.ActiveWorkbook.Close(SaveChanges=0)
xlApp.Quit()
del xlApp

gp.SetParameterAsText(2,outValue)
del gp


***Sorry to be such a long post!
I would love some serious guidance from you Python gurus, otherwise I will continue to spin, ugh.

Thanks!
Dec 14 '07 #1
Share this Question
Share on Google+
2 Replies


P: 34
Greetings!

I am still new to Python, sorry! I have been searching through many posts on this subject and have attempted to TRY, but I feel really lost. So, any detailed guidance would be oh-so helpful for my learning at this point!

So, I am working with GIS data using ESRI software. I spatially select data from numerous sources and write it to a > delimited text file. I then manually bring that file into Excel, to then pass onto users to do their thing with. How do I automate this process?

**A snippet of code:
writeFile.write("CATEGORY> TYPE> NAME> ADDRESS> CITY> STATE> COUNTY> MISC_INFO> STATUS> COMMENTS> POINT_X> POINT_Y" + "\n")

curLocalPolice = gp.searchcursor(t_localPolice)
rowLocalPolice = curLocalPolice.Next()
while rowLocalPolice:
writeFile.write("SECURITY> LOCAL POLICE> " + rowLocalPolice.Name + "> " + rowLocalPolice.address +
"> " + rowLocalPolice.city + "> " + rowLocalPolice.state + "> " + rowLocalPolice.county +
"> " + "ph(" + str(rowLocalPolice.area_) + ")" + str(rowLocalPolice.phone) +
"> > > " + str(rowLocalPolice.point_x) + "> " + str(rowLocalPolice.point_y) + "\n")
rowLocalPolice = curLocalPolice.Next()


**which produces a text file that looks like this (way more records then this, of course):
SWEAT_CATEGORY> TYPE> NAME> ADDRESS> CITY> STATE> COUNTY> MISC_INFO> STATUS> COMMENTS> POINT_X> POINT_Y
SECURITY> LOCAL POLICE> EAST WHITELAND TOWNSHIP POLICE DEPT HEADQUARTERS> 209 CONESTOGA RD> MALVERN> PA> CHESTER> ph(610)647-2100> > > -75.5554538863> 40.050059662
SECURITY> LOCAL POLICE> EAST VINCENT TOWNSHIP POLICE DEPT HEADQUARTERS> 262 RIDGE RD> SPRING CITY> PA> CHESTER> ph(610)933-0115> > > -75.5880005882> 40.1617776533
SECURITY> LOCAL POLICE> DOWNINGTOWN POLICE DEPT HEADQUARTERS> 10 W LANCASTER AVE> DOWNINGTOWN> PA> CHESTER> ph(610)269-0263> > > -75.7061431299> 40.0048218854
SECURITY> LOCAL POLICE> UWCHLAN TOWNSHIP POLICE DEPT HEADQUARTERS> 717 N SHIP RD> EXTON> PA> CHESTER> ph(610)363-6947> > > -75.6305628031> 40.0487863475
SECURITY> LOCAL POLICE> PHOENIXVILLE POLICE DEPT HEADQUARTERS> 140 CHURCH ST> PHOENIXVILLE> PA> CHESTER> ph(610)933-1180> > > -75.5135260786> 40.1328893105
SECURITY> LOCAL POLICE> MALVERN POLICE DEPT HEADQUARTERS> 1 E 1ST AVE> MALVERN> PA> CHESTER> ph(610)647-0261> > > -75.5125783923> 40.033827335

**which I then maually bring into Excel as a > delimited file, do some column adjustments and save as a CSV file so that it can then be brought into ArcMap (GIS mapping and analysis software).


How do I automate in Python 2.4? I have this/below bit of general code. Is this right? Do I need to build a dictionary with my text file? How? Is this code inserted at the end of my script, once all data has been put to the text file?

import sys, string, os, win32com.client
gp = win32com.client.Dispatch("esriGeoprocessing.GpDisp atch.1")

#import arcgisscripting, sys
#gp = arcgisscripting.create()

worksheet = gp.GetParameterAsText(0)
inValue = gp.GetParameterAsText(1)

#worksheet = "d:\\test\\excel\\test.xls"
#inValue = 10

xlApp = win32com.client.Dispatch("Excel.Application")
#xlApp.Visible = 1
xlApp.Workbooks.Open(worksheet)
xlApp.ActiveSheet.Cells(1,1).Value = inValue
xlApp.ActiveSheet.Calculate()
outValue = xlApp.ActiveSheet.Cells(2,1).Value
xlApp.ActiveWorkbook.Close(SaveChanges=0)
xlApp.Quit()
del xlApp

gp.SetParameterAsText(2,outValue)
del gp


***Sorry to be such a long post!
I would love some serious guidance from you Python gurus, otherwise I will continue to spin, ugh.

Thanks!

Sorry, forgot code brackets.


**A snippet of code:
Expand|Select|Wrap|Line Numbers
  1. writeFile.write("CATEGORY> TYPE> NAME> ADDRESS> CITY> STATE> COUNTY> MISC_INFO> STATUS> COMMENTS> POINT_X> POINT_Y" + "\n")
  2.  
  3. curLocalPolice = gp.searchcursor(t_localPolice)
  4. rowLocalPolice = curLocalPolice.Next()
  5. while rowLocalPolice:
  6.         writeFile.write("SECURITY> LOCAL POLICE> " + rowLocalPolice.Name + "> " + rowLocalPolice.address +
  7.                         "> " + rowLocalPolice.city + "> " + rowLocalPolice.state + "> " + rowLocalPolice.county +
  8.                         "> " + "ph(" + str(rowLocalPolice.area_) + ")" + str(rowLocalPolice.phone) +
  9.                         "> > > " + str(rowLocalPolice.point_x) + "> " + str(rowLocalPolice.point_y) + "\n")
  10.         rowLocalPolice = curLocalPolice.Next()
  11.  


Expand|Select|Wrap|Line Numbers
  1. import sys, string, os, win32com.client
  2. gp = win32com.client.Dispatch("esriGeoprocessing.GpDispatch.1")
  3.  
  4. #import arcgisscripting, sys
  5. #gp = arcgisscripting.create()
  6.  
  7. worksheet = gp.GetParameterAsText(0)
  8. inValue = gp.GetParameterAsText(1)
  9.  
  10. #worksheet = "d:\\test\\excel\\test.xls"
  11. #inValue = 10
  12.  
  13. xlApp = win32com.client.Dispatch("Excel.Application")
  14. #xlApp.Visible = 1
  15. xlApp.Workbooks.Open(worksheet)
  16. xlApp.ActiveSheet.Cells(1,1).Value = inValue
  17. xlApp.ActiveSheet.Calculate()
  18. outValue = xlApp.ActiveSheet.Cells(2,1).Value
  19. xlApp.ActiveWorkbook.Close(SaveChanges=0)
  20. xlApp.Quit()
  21. del xlApp
  22.  
  23. gp.SetParameterAsText(2,outValue)
  24. del gp
  25.  
Dec 14 '07 #2

P: 34
This thread can be closed. I figured it out on my own.
Dec 18 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.