topic Re: How to export table with more than 1,048,576 rows of data in Python Questions

How to export table with more than 1,048,576 rows of data

BaileyFredlund1 — Thu, 20 Jun 2019 18:31:03 GMT

Hello all,

I have a data table with more than 1,048,576 rows of data which is the limit of microsoft and hence cannot use the Table to Excel function. I tried to export to txt file and then open in excel but still no luck. Is there an alternative that you have used to deal with these larger tables? There are about 10 million rows total. I am not opposed to working in python.

Thanks in advance.

Re: How to export table with more than 1,048,576 rows of data

DEWright_CA — Thu, 20 Jun 2019 18:37:30 GMT

I export them as logical blocks; export a given Zipcode, County, State, Watershed etc...

Re: How to export table with more than 1,048,576 rows of data

DanPatterson_Retired — Fri, 21 Jun 2019 00:05:44 GMT

What is it you want to do that you need excel?

Perhaps the functionality can be implemented through tools/scripts with the Arc* environment

Re: How to export table with more than 1,048,576 rows of data

forestknutsen1 — Sun, 12 Dec 2021 03:16:06 GMT

Pandas might be for you.

https://pandas.pydata.org/

http://geospatialtraining.com/tutorial-creating-a-pandas-dataframe-from-a-shapefile/

https://datatofish.com/export-dataframe-to-csv/

https://towardsdatascience.com/why-and-how-to-use-pandas-with-large-data-9594dda2ea4c

I only have a ~140,000 rows to work within my test... but I think you could scale stuff up.

https://stackoverflow.com/questions/14525344/whats-the-maximum-size-of-a-numpy-array

import pandas
import arcpy

arcpy.env.workspace = connection

source = 'coninfo'
field_names = arcpy.ListFields(source)
field_names = [x.name for x in field_names]
field_names = field_names[:1]

ar = arcpy.da.TableToNumPyArray(source, field_names)
df = pandas.DataFrame(ar)
df.to_csv('test.csv')

Re: How to export table with more than 1,048,576 rows of data

DanPatterson_Retired — Sun, 12 Dec 2021 03:16:09 GMT

You don't need pandas, and a cleaner check to ensure Unicode support etc is to use numpy.

I built this little function which I use instead of the standard versions that ship natively

def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True):
    """Save a NumPy structured, recarray to text.

    Parameters
    ----------
    a : array
        input array
    fname : filename
        output filename and path otherwise save to script folder
    sep : separator
        column separater, include a space if needed
    dt_hdr : boolean
        if True, add dtype names to the header of the file

    """
    a_names = ", ".join(i for i in a.dtype.names)
    hdr = ["", a_names][dt_hdr]  # use "" or names from input array
    s = np.array(a.tolist(), dtype=np.unicode_)
    widths = [max([len(i) for i in s[:, j]])
              for j in range(s.shape[1])]
    frmt = sep.join(["%{}s".format(i) for i in widths])
    # vals = ", ".join([i[1] for i in a.dtype.descr])
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
    print("\nFile saved...")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

since you already have your array, you can use the above with any separator you want, not just comma-delimited.

save_txt(ar, "c:/temp/arr.txt", sep=", ", dt_hdr=True)‍

Re: How to export table with more than 1,048,576 rows of data

MarcoBoeringa — Fri, 21 Jun 2019 07:54:33 GMT

I am not sure what you are trying to achieve. As you noted yourself, and is also documented here:

https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Newer_versions

Excel can only handle 1M rows maximum. There is no way you will be getting past that limit by changing your import practices, it is after all the limit of the worksheet itself.

For this amount of rows and data, you really should be looking at Microsoft Access. Databases can handle a far greater number of records. Access has basic functionality for summarizing and analyzing data too, so doing some calculations you would like to do in Excel, is likely possible in Access as well, although the process to get there will be different (defining SQL queries instead of defining formulas in Excel).

Re: How to export table with more than 1,048,576 rows of data

BaileyFredlund1 — Sat, 22 Jun 2019 21:36:32 GMT

Thanks for the replies. I ended up just copying the maximum amount of rows in the attribute table and pasting them into excel manually. Was time consuming but the python suggested was a bit too advanced for me! Thanks for yalls help!

Re: How to export table with more than 1,048,576 rows of data

BrittanyBurson — Tue, 17 May 2022 22:46:48 GMT

For anyone else here who was using the Table to Excel tool as a means to get to .csv, but hitting this record limit (me), you can follow instructions here for exporting straight to .csv. This should bypass the row limit if .csv is acceptable for your needs.