topic Re: Issues to work with Excel files with arcPy!! in Python Questions

Issues to work with Excel files with arcPy!!

SaidAkif — Tue, 22 Sep 2015 15:05:31 GMT

Hi all

I tried to convert excel sheets to table. I did the folowing:

the problem that I got a message 000732 that imput table table .....\file.xlsx\'ATL...$' does not exist or not supported (makeTableView).

the list (Listxls) is well populated. Moreover, I tried the same work at home and it work fine. I really don't know what happen?

Any help please?

By the way, I hve lot of trouble to use this webside. I can't copy/paste anything. My interface is :

Thanks in advance

Re: Issues to work with Excel files with arcPy!!

SaidAkif — Tue, 22 Sep 2015 16:00:54 GMT

Hi all

I realize just right now that in the message error, the name of the file contain 2 times the Excel file name which is i believe the source of the error.

I Tried all combinaison, but the error happen all the time

Any Idea

Thanks

Re: Issues to work with Excel files with arcPy!!

RebeccaStrauch__GISP — Tue, 22 Sep 2015 16:30:08 GMT

Said, In 10.2.2/10.3 I have been successful

arcpy.ExcelToTable_conversion(FileXLS, outFGDBtable)

But my process is a little different in that I'm also creating the Excel file from a .csv to start with, so only one tab. But I would look more into that command to see if it would work

Excel To Table—Help | ArcGIS for Desktop

Re: Issues to work with Excel files with arcPy!!

SaidAkif — Tue, 22 Sep 2015 16:38:09 GMT

Unfortunaitly, I work with 10.1 version

Re: Issues to work with Excel files with arcPy!!

RebeccaStrauch__GISP — Wed, 23 Sep 2015 00:43:21 GMT

have you tried TableToTable?

ArcGIS Help 10.1

Syntax

TableToTable_conversion (in_rows, out_path, out_name, {where_clause}, {field_mapping}, {config_keyword})

Re: Issues to work with Excel files with arcPy!!

SaidAkif — Wed, 23 Sep 2015 14:38:44 GMT

Thanks I used this TableToTable as you mention (the same way). but I still having the same error. As I mention before, the problem is the duplication of the Excel file name (...xlsx) in the valeur variable (please see my first message)

Thanks

Re: Issues to work with Excel files with arcPy!!

JamesCrandall — Wed, 23 Sep 2015 19:48:46 GMT

What are you expecting the output "table(s)" to be exactly?

I see you set an "outGDB" variable, but that is only pointing to the folder not an actual Geodatabase.

"tablename" is going to output what type of file exactly? I don't see where you are specifying any file extension anywhere. The source doc says you would be just creating INFO table outputs without any extensions supplied.

http://resources.arcgis.com/EN/HELP/MAIN/10.1/index.html#//001200000027000000

Re: Issues to work with Excel files with arcPy!!

SaidAkif — Fri, 25 Sep 2015 13:24:32 GMT

Hi all,

Sorry I use the image link because I have trouble to copy/paste in this website. So here is the code and th message erro. You can see that the priblem is in the input table.

Thanks for your help

Re: Issues to work with Excel files with arcPy!!

WesMiller — Fri, 25 Sep 2015 14:20:01 GMT

MakeTableView_management (in_table, out_view, {where_clause}, {workspace}, {field_info})

arcpy.MakeTableView_management(valeur,os.path.join(outGDB,tableName,".dbf"))

Re: Issues to work with Excel files with arcPy!!

SaidAkif — Fri, 25 Sep 2015 15:15:13 GMT

I try this but the same Error. The problem is in the duplication Excel table name in the input table as showen in the ERROR message. I don't know why this error happen

Thanks

Re: Issues to work with Excel files with arcPy!!

WesMiller — Fri, 25 Sep 2015 15:43:29 GMT

This happens when you run a program a second time and you don't use "arcpy.env.overwriteOutput = True". If this a repeat add the line of code and rerun, I would suggest if this is not a test environment meaning you don't need the data in the geodatabase, I would make a back up copy.

Re: Issues to work with Excel files with arcPy!!

BlakeTerhune — Sat, 11 Dec 2021 06:31:45 GMT

When using TableToTable_conversion() from Excel to GDB, you need to specify the sheet name suffixed with a $. Something like C:/temp/MyExcelFile.xlsx/mySheetName$

Using the Esri code samples in the Excel To Table link provided by Rebecca Strauch, GISP, you can use the xlrd module for reading the sheets in an Excel file.

import os
import xlrd
import arcpy

def main():
    # Local variables
    in_excel = r"C:\temp\MyExcelFile.xlsx"
    out_gdb = r"C:\temp\MyGeodatabase.gdb"

    # Read Sheets in Excel Workbook
    with xlrd.open_workbook(in_excel) as workbook:
        sheets = [sheet.name for sheet in workbook.sheets()]

        # Export each sheet to geodatabase table
        for sheet in sheets:
            in_rows = os.path.join(in_excel,str(sheet)+"$")
            ## The out_name is based on the input excel file name
            ## an underscore (_) separator followed by the sheet name
            baseName = os.path.basename(in_excel)
            out_name = arcpy.ValidateTableName(
                "{0}_{1}".format(os.path.splitext(baseName)[0], sheet),
                out_gdb
            )
            arcpy.TableToTable_conversion(in_rows, out_gdb, out_name,)
            print arcpy.GetMessages(), "\n"


if __name__ == '__main__':
    main()

Re: Issues to work with Excel files with arcPy!!

RebeccaStrauch__GISP — Fri, 25 Sep 2015 17:51:38 GMT

Said, as a side note on the copy-paste in Geonet, have you seen Posting Code blocks in the new GeoNet ? Main things to remember is the "advanced editor" tab won't show if you are replying within you Geonet inbox....make sure to right-click on the header/title of you question and "open in another tab" .... Opening from you system inbox may work to.

my opologies if you had already seen that post....posting code isn't always consistent.

Re: Issues to work with Excel files with arcPy!!

StevenEnsing — Fri, 25 Sep 2015 18:44:06 GMT

As another approach, for work with pre-10.2, you might find the Excel and CSV conversion toolbox download from Esri useful: similar tools to what's included in 10.2, plus a csv to table tool.

http://www.arcgis.com/home/item.html?id=f3d91b8f852042e289e09a7ec8342431