nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

BaileyFredlund1

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.

Vind meer berichten getagd met

Accepted answers

BaileyFredlund1

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!

All comments

BrittanyBurson

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.

BaileyFredlund1

MarcoBoeringa

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).

DanPatterson_Retired

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

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">save_txt</SPAN><SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> name<SPAN class="operator token">=</SPAN><SPAN class="string token">"arr.txt"</SPAN><SPAN class="punctuation token">,</SPAN> sep<SPAN class="operator token">=</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">,</SPAN> dt_hdr<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="string token">"""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

    """</SPAN>
    a_names <SPAN class="operator token">=</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a<SPAN class="punctuation token">.</SPAN>dtype<SPAN class="punctuation token">.</SPAN>names<SPAN class="punctuation token">)</SPAN>
    hdr <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> a_names<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>dt_hdr<SPAN class="punctuation token">]</SPAN>  <SPAN class="comment token"># use "" or names from input array</SPAN>
    s <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>array<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">.</SPAN>tolist<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dtype<SPAN class="operator token">=</SPAN>np<SPAN class="punctuation token">.</SPAN>unicode_<SPAN class="punctuation token">)</SPAN>
    widths <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>max<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>len<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> s<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">,</SPAN> j<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
              <SPAN class="keyword token">for</SPAN> j <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN>s<SPAN class="punctuation token">.</SPAN>shape<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
    frmt <SPAN class="operator token">=</SPAN> sep<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"%{}s"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> widths<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="comment token"># vals = ", ".join([i[1] for i in a.dtype.descr])</SPAN>
    np<SPAN class="punctuation token">.</SPAN>savetxt<SPAN class="punctuation token">(</SPAN>name<SPAN class="punctuation token">,</SPAN> a<SPAN class="punctuation token">,</SPAN> fmt<SPAN class="operator token">=</SPAN>frmt<SPAN class="punctuation token">,</SPAN> header<SPAN class="operator token">=</SPAN>hdr<SPAN class="punctuation token">,</SPAN> comments<SPAN class="operator token">=</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\nFile saved..."</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

save_txt<SPAN class="punctuation token">(</SPAN>ar<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"c:/temp/arr.txt"</SPAN><SPAN class="punctuation token">,</SPAN> sep<SPAN class="operator token">=</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">,</SPAN> dt_hdr<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

forestknutsen1

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')

DanPatterson_Retired

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

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

dewright_ca

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.