Text to GIS dbase Best Practices

[TedCronin]

This may seem trivial to some, but what is the best way to go for conversion purposes, from a text file into a GIS format.  I was looking to use win32com and convert a txt file right into Access, but Esri does not support the .accdb format, so this does not solve my issue, unless win32com allows to go straight into a mdb.  I was also thinking of the dbf format (Seems like a dated approach), or does it make sense to just use the insertcursor from a text file right into a file GB.  I get a lot of txt files from management, and I am looking to just build a tool for myself or them to get something a bit more useful and streamline my own workflow.  i don't really want to go into SQL Server or Postgress for simple operations.

[DavidWynne]

This may seem trivial to some, but what is the best way to go for conversion purposes, from a text file into a GIS format.  I was looking to use win32com and convert a txt file right into Access, but Esri does not support the .accdb format, so this does not solve my issue, unless win32com allows to go straight into a mdb.  I was also thinking of the dbf format (Seems like a dated approach), or does it make sense to just use the insertcursor from a text file right into a file GB.  I get a lot of txt files from management, and I am looking to just build a tool for myself or them to get something a bit more useful and streamline my own workflow.  i don't really want to go into SQL Server or Postgress for simple operations.

Hi Ted
Could depend on how your .txt files are structured, but a .txt can be read by most tools that accept a table or table view as input.  So you could feed it directly into tools like Make XY Event Layer or XY To Line.  Or you could read in the file in Python and write out in your own flavor with a cursor.

-Dave

[TedCronin]

Hi Ted
Could depend on how your .txt files are structured, but a .txt can be read by most tools that accept a table or table view as input.  So you could feed it directly into tools like Make XY Event Layer or XY To Line.  Or you could read in the file in Python and write out in your own flavor with a cursor.

-Dave

That was fast, so txt file can be read as a table, thats interesting, something to explore for sure.  thanks David.  I like the last idea at least for testing purposes at 10.1.  So, regarding the structure, do the tools, handle comma delimited, and treat the top row as the column headings?

[DavidWynne]

That was fast, so txt file can be read as a table, thats interesting, something to explore for sure.  thanks David.  I like the last idea at least for testing purposes at 10.1.  So, regarding the structure, do the tools, handle comma delimited, and treat the top row as the column headings?

Yes.

Just to confirm myself, I created a little text file that contained this:
a,b,c,d
1,2,3,S
4,5,6,T

I used that as input to TableToTable and it spit out a table of 2 rows with 5 fields (OBJECTID, a, b, c, d)

-Dave

[TedCronin]

Yes.

Just to confirm myself, I created a little text file that contained this:
a,b,c,d
1,2,3,S
4,5,6,T

I used that as input to TableToTable and it spit out a table of 2 rows with 5 fields (OBJECTID, a, b, c, d)

-Dave

I am actually playing with that now, which is pretty cool, I would just need to write a read to check the first line for bad field names, and then do  str.replace, like ("/", "_") or (" ", "_").  Cool, this is MUCH easier than what I was planning on doing.  Thank you David, for helping me with the MUCH easier route.

WOW.  Is this new, or was this always here?

Damn, cool, way to go...

[TedCronin]

So, not to pick a brain or two, but are there any issues with 2.7 with reading/write a txt file and making a change and thinking it would change.  i am trying with "r+", within an open ("....txt", "r+), and the txt file does not seem to be saving.  My replace i think is sound, it prints on screen what I would expect, but nothing changes in the txt file?  hoping for 2 for 2 😉

with open (r"C:\1GIS\Sales\ReplaceString\Sales2011.txt", "r+") as txtFile:
    data = txtFile.readline()
    print "*" * 530
    print data
    change = data.replace("/ ", "_").replace("/", "_").replace(" ", "_")
    print change

playing with with statements, but I have the same code w/o with

[JasonScheirer]

r+ is for appending, how does rb work?

[TedCronin]

r+ is for appending, how does rb work?

i tried rb, nothing happens. I tried r+b, same. I tried w, which does work, but not what I need. If r+ is for appending then python needs to update there doc. I also tried a.

http://docs.python.org/tutorial/inputoutput.html


7.2. Reading and Writing Files
open() returns a file object, and is most commonly used with two arguments: open(filename, mode).

>>> f = open('/tmp/workfile', 'w')
>>> print f
<open file '/tmp/workfile', mode 'w' at 80a0960>
The first argument is a string containing the filename. The second argument is another string containing a few characters describing the way in which the file will be used. mode can be 'r' when the file will only be read, 'w' for only writing (an existing file with the same name will be erased), and 'a' opens the file for appending; any data written to the file is automatically added to the end. 'r+' opens the file for both reading and writing. The mode argument is optional; 'r' will be assumed if itâ??s omitted.

On Windows, 'b' appended to the mode opens the file in binary mode, so there are also modes like 'rb', 'wb', and 'r+b'. Python on Windows makes a distinction between text and binary files; the end-of-line characters in text files are automatically altered slightly when data is read or written. This behind-the-scenes modification to file data is fine for ASCII text files, but itâ??ll corrupt binary data like that in JPEG or EXE files. Be very careful to use binary mode when reading and writing such files. On Unix, it doesnâ??t hurt to append a 'b' to the mode, so you can use it platform-independently for all binary files.

[JasonScheirer]

Ohhhh, you're not actually doing the .write() call, you're just reading. You may want to read from the file and output to a new one to be safe.

[TedCronin]

Ohhhh, you're not actually doing the .write() call, you're just reading. You may want to read from the file and output to a new one to be safe.

I actually have tried both:
txtFile.write (change)
#txtFile.writelines(change)

inside the with statement

The file is not getting updated.

So basically, I am doing:

with open (r"C:\1GIS\Sales\ReplaceString\Sales2011.txt", "r+b") as txtFile:
    data = txtFile.readline()
    print "*" * 530
    print data
    change = data.replace("/ ", "_").replace("/", "_").replace(" ", "_")
    print change
    txtFile.write (change)
    #txtFile.writelines(change)