Model Builder to Create Python Script for xls to csv Conversion

MagdalenaMartinez · ‎02-12-2019

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!

StephenM · ‎02-14-2019

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.

import arcpy
import pandas as pd

arcpy.env.overwriteOutput = True

# Input Excel file
excel_file = r"C:\Users\Username\Desktop\test_book.xlsx"

# Intermediate converted table
interm_table = r"C:\Users\Username\Documents\ArcGIS\Default.gdb\test_book"

# Convert Excel file to table
arcpy.ExcelToTable_conversion(excel_file, interm_table, "Sheet1")

# Directory and name for the output CSV
output_dir = r"C:\Users\Username\Desktop"
csv_name = "test_book.csv"

# Convert intermediate table to CSV
arcpy.TableToTable_conversion(interm_table, output_dir, csv_name)

# Set full path of the CSV
csv_fullpath = output_dir + "\\" + csv_name

# Read CSV into pandas DataFrame
df = pd.read_csv(csv_fullpath)

# Drop OID column
df.drop('OID', axis=1, inplace=True)

# Write DataFrame to the same CSV as the input
df.to_csv(csv_fullpath, index=False)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

View solution in original post

DanPatterson_Retired · ‎02-12-2019

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

MagdalenaMartinez · ‎02-12-2019

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 · ‎02-12-2019

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

DuncanHornby · ‎02-14-2019

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

JoeBorgione · ‎02-14-2019

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

That should just about do it....

DuncanHornby · ‎02-14-2019

Joe,

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

JoeBorgione · ‎02-14-2019

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.

That should just about do it....

DanPatterson_Retired · ‎02-14-2019

Joe

/blogs/dan_patterson/2018/02/12/table-tools-a-set-of-tools-for-working-with-tabular-data description and tools at

https://www.arcgis.com/home/item.html?id=90d9ca933e8c4b96bf341a20ae1f2514

JoeBorgione · ‎02-14-2019

Beauty!

That should just about do it....