Is it possible for ArcMap to treat data types (numbers as text) correctly when importing from a text file?

Question

I am creating a text/csv file (using a python script). The file contains data to import into ArcMap. I have several fields that look like numbers but have leading zeros ("0012") etc. that I want to preserve. I thought I could force this on ArcMap by adding a dummy line at the beginning of the file that has obvious text in it (like an "X").

In the screenshot below, I have an X in several of the fields at the beginning (including ACCTID in the first column) for the file but ArcMap has made them into integers anyway. To clarify, I'm just trying to add the file into ArcMap directly using "Add Data".

Excel seems to treat them similarly. I do not want to open this in Excel and fiddle with the formatting of over 100 fields if I can avoid it and it seems preposterous that I would have to do that. I found similar questions but nothing exactly like this. Some folks had the reverse issue (wanting numbers and getting text) and others were working with Excel files rather than plain text.

The code I'm using to write the CSV (if that matters) looks like this ...

    with open(filehandle, 'wb') as csvfile:
    data_writer = csv.writer(csvfile, delimiter = ",")
    if printHeaders:
        csvfile.write(', '.join(these_headers) + '\n')
    if dummy:
        csvfile.write(dummy + '\n')

further down I have ...

if linecount < linelimit and printline:
    data_writer.writerow(theDict[acct])
    linecount += 1

I am actually running a search cursor through this file to use to run against an update cursor using a key (the ACCTID).

Answer 1

If you want those numbers to be treated as text, you need to quote them in the CSV. That means that you want to make sure your items are string types in Python already.

You don't say how you're writing the CSV from Python, but if you're just using the csv module, you'll want to use a quote option different from the default (default is QUOTE_MINIMAL). QUOTE_NONNUMERIC is probably what you would want to use here. The documentation for that can be found here.

Here is the example from the doc on how to use this for writing to CSV:

import csv
with open('eggs.csv', 'w', newline='') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                            quotechar='"', quoting=csv.QUOTE_NONNUMERIC)
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

So your code could be modified as follows:

with open(filehandle, 'wb') as csvfile:
data_writer = csv.writer(csvfile, delimiter = ",", quotechar='"', quoting=csv.QUOTE_NONNUMERIC)
if printHeaders:
    csvfile.write(', '.join(these_headers) + '\n')
if dummy:
    csvfile.write(dummy + '\n')

Since you say you are going to run a SearchCursor through this file, why not just iterate through the data after you read it into your script, instead of first writing it to disk? That sounds like it may be easier.

Answer 2

You could try to generate an schema.ini file along your CSV (in fact after the first import ArcGis probably automatically create it, hopefully you'll just have to modify it and re-import).

based on your data it could look like that :

[PDR_OUTPUT_1210.csv] 
Format=Delimited(59) 
ColNameHeader=True
Col1=ACCTID Text Width 254
Col2=JURSCODE Text Width 254
...
Colx=... Long
...
Col100=...

More information on schema.ini file

Answer 3

I recommend pandas module (which is included since appr. ArcGIS 10.4). I dont know how you want the dictionaries to be structured, but any way is possible when the data is in pandas. See pandas.DataFrame.to_dict documentation.

import pandas as pd
df = pd.read_csv(r"C:\Test\11111.txt",dtype={'field1':object}) #field1 is forced to be object(=string)
>>> df
  field1 field2  field3
0     01      A       9
1      2      B       9
2      3      C       9
3      4      D       9
>>> df.dtypes
field1    object
field2    object
field3     int64
dtype: object
>>> df.set_index('field1').T.to_dict('list')
{'2': ['B', 9L], '3': ['C', 9L], '01': ['A', 9L], '4': ['D', 9L]}