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; } } }

Model Builder to Create Python Script for xls to csv Conversion

MagdalenaMartinez

I am currently trying to create a python script within model builder to convert an xls file to csv.

I created one already using the "Excel to Table" tool which was successful. However, I tried to add data to the xls file and run the script again and it choked on the "csv already exists". I tried adding "Application.DisplayAlerts = False" to overwrite the csv already existing, but it still choked when I tried to run it again.

My ultimate goal is to create a script I can set up in task scheduler to run on it's own everyday to convert an xls to csv, without using a script that requires macros to be enabled in the xls.

I am at the beginner beginner level when it comes to python script. Any help would be much appreciated!

Najděte více příspěvků označených jako

Accepted answers

StephenM

To overwrite outputs, set arcpy.env.overwriteOutput = True.

overwriteOutput

(Read and Write)

Controls whether tools will automatically overwrite any existing output when run. When set to True, tools will execute and overwrite the output dataset. When set to False, existing outputs will not be overwritten, and the tool will return an error.

Boolean

env—Help | ArcGIS Desktop

You could do this using Excel To Table to convert the Excel sheet to a table, then Table To Table to convert the table to a CSV.

In my testing the output CSV always had an OID field added to it, so I used the pandas library to drop the OID field.

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>

<SPAN class="comment token"># Input Excel file</SPAN>
excel_file <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Desktop\test_book.xlsx"</SPAN>

<SPAN class="comment token"># Intermediate converted table</SPAN>
interm_table <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Documents\ArcGIS\Default.gdb\test_book"</SPAN>

<SPAN class="comment token"># Convert Excel file to table</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>ExcelToTable_conversion<SPAN class="punctuation token">(</SPAN>excel_file<SPAN class="punctuation token">,</SPAN> interm_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Sheet1"</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Directory and name for the output CSV</SPAN>
output_dir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Desktop"</SPAN>
csv_name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"test_book.csv"</SPAN>

<SPAN class="comment token"># Convert intermediate table to CSV</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>TableToTable_conversion<SPAN class="punctuation token">(</SPAN>interm_table<SPAN class="punctuation token">,</SPAN> output_dir<SPAN class="punctuation token">,</SPAN> csv_name<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Set full path of the CSV</SPAN>
csv_fullpath <SPAN class="operator token">=</SPAN> output_dir <SPAN class="operator token">+</SPAN> <SPAN class="string token">"\\"</SPAN> <SPAN class="operator token">+</SPAN> csv_name

<SPAN class="comment token"># Read CSV into pandas DataFrame</SPAN>
df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>read_csv<SPAN class="punctuation token">(</SPAN>csv_fullpath<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Drop OID column</SPAN>
df<SPAN class="punctuation token">.</SPAN>drop<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'OID'</SPAN><SPAN class="punctuation token">,</SPAN> axis<SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> inplace<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Write DataFrame to the same CSV as the input</SPAN>
df<SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN>csv_fullpath<SPAN class="punctuation token">,</SPAN> index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

pandas.read_csv — pandas 0.24.1 documentation

pandas.DataFrame.drop — pandas 0.24.1 documentation

pandas.DataFrame.to_csv — pandas 0.24.1 documentation

All comments

MesfinLenth17

Thank you for this resource! The script worked in my case.

But I also tried to export to CSV straight from Model Builder and that seems to be working (I have ArcGIS Pro version 3.3.1).

You can use Export Table (aka Table to Table) geoprocessing tool within your Model Builder. Enter your Input File Path, and for the Output Table, specify the file name or file path ends in .csv (filepath.csv). Finally drop any field using Field Map and schedule your model to run on a needed basis.

MichaelBruening

Just wanted to say thank you for posting this solution as I was able to use some of the code to help with a similar process of dropping the "OID" field from a CSV file that was exported from the "Table to Table" tool.

StephenM

Yeah, I tried using FieldMappings and also tried using table view FieldInfo. With field mapping, the ObjectID field didn't seem to be accessible using the fields property of the FieldMappings object. With FieldInfo, even if I hid the ObjectID field it still showed up in the output CSV. Hiding fields worked with all the other fields, just not ObjectID.

curtvprice

The OP asked about Model Builder. I think pandas is a little more than they were interested in... did you try simply modifying the field map on the Table to Table tool to drop the OID? Seems like that would be less trouble (especially if using Model Builder).

JoeBorgione

I'll download and check it out; I didn't realize those tools were written so long ago; a couple of them are now bundled with the core release.

D_Hornby_ddh_uni

Joe,

Not sure , never tried. If they don't I can't imagine it would take too much editing to get them working?

JoeBorgione

Duncan Hornby‌ Can this toolbox and the four tools be used in ArcGIS Pr0 2.3?

StephenM

To overwrite outputs, set arcpy.env.overwriteOutput = True.

overwriteOutput

(Read and Write)

Boolean

env—Help | ArcGIS Desktop

You could do this using Excel To Table to convert the Excel sheet to a table, then Table To Table to convert the table to a CSV.

In my testing the output CSV always had an OID field added to it, so I used the pandas library to drop the OID field.

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>

<SPAN class="comment token"># Input Excel file</SPAN>
excel_file <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Desktop\test_book.xlsx"</SPAN>

<SPAN class="comment token"># Intermediate converted table</SPAN>
interm_table <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Documents\ArcGIS\Default.gdb\test_book"</SPAN>

<SPAN class="comment token"># Convert Excel file to table</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>ExcelToTable_conversion<SPAN class="punctuation token">(</SPAN>excel_file<SPAN class="punctuation token">,</SPAN> interm_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Sheet1"</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Directory and name for the output CSV</SPAN>
output_dir <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Username\Desktop"</SPAN>
csv_name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"test_book.csv"</SPAN>

<SPAN class="comment token"># Convert intermediate table to CSV</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>TableToTable_conversion<SPAN class="punctuation token">(</SPAN>interm_table<SPAN class="punctuation token">,</SPAN> output_dir<SPAN class="punctuation token">,</SPAN> csv_name<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Set full path of the CSV</SPAN>
csv_fullpath <SPAN class="operator token">=</SPAN> output_dir <SPAN class="operator token">+</SPAN> <SPAN class="string token">"\\"</SPAN> <SPAN class="operator token">+</SPAN> csv_name

<SPAN class="comment token"># Read CSV into pandas DataFrame</SPAN>
df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>read_csv<SPAN class="punctuation token">(</SPAN>csv_fullpath<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Drop OID column</SPAN>
df<SPAN class="punctuation token">.</SPAN>drop<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'OID'</SPAN><SPAN class="punctuation token">,</SPAN> axis<SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> inplace<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Write DataFrame to the same CSV as the input</SPAN>
df<SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN>csv_fullpath<SPAN class="punctuation token">,</SPAN> index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

pandas.read_csv — pandas 0.24.1 documentation

pandas.DataFrame.drop — pandas 0.24.1 documentation

pandas.DataFrame.to_csv — pandas 0.24.1 documentation

D_Hornby_ddh_uni

Have a look at this tool box on the ESRI Code Sharing website before you invest any more time on this problem...

DanPatterson_Retired

using File Save As would be the excel route, but the person would have to know it.

Perhaps a picture

pick a folder, specify a filename and extension, then click on the dropdown and select CSV.

Not elegant, but beats the Excel to Table tool, then export it out to csv/text format

MagdalenaMartinez

I'm trying to have it set to convert itself each day so that a person does not have to manually do it everyday. Is there a way to set this within excel?

DanPatterson_Retired

You have ruled out just saving the excel file to a csv from within excel? It would actually take less steps.

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

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