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

Create shapefile from .xls file with a loop -- error 000732 after 64 iterations

ChloeLamy

Hello there,

I am experimenting an issue with a script I wrote. I have .xls files named after a date (2020_01_01.xls) composed of two sheets (\ET$ and \P$).
In order to create shapefiles from these .xls files I made a loop over my .xls files which works fine for the 63 first iterations, but for my 64th files (2020_03_05.xls) I have an error 000732 - File doesn't exist or is not supported. However it can't be the first reason (doen't exist) because previous files doesn't bring problems. Thus I supposed it is the "not supported" reason... but I can't understand why this error happens.

I hope someone here can help me.

Koloeii

Here is a short version of my script :
#import
import sys
import os
import arcpy
from arcpy import env
from arcpy.sa import *

# Check out any necessary licenses
arcpy.CheckOutExtension("spatial")

#choice of model
model = "reticB1"

#define time period
byear = "2020"
eyear = "2099"

dirfiles = r"F:\Documents\journalier"
pathfiles = dirfiles+'\\'+model+'\\'
arcpy.env.workspace = pathfiles
arcpy.env.overwriteOutput = True

#list of .xls files in my directory
x=0
listFi = []
liste=os.listdir(pathfiles)
y=len(liste)
while x<y :
    element=liste
    if element[-3:]=='xls':
        listFi.append(element)
    x=x+1
print (listFi)

#loop over .xls files
for Fi in listFi :
    date = Fi[7:17]
    in_ET=pathfiles+model+date+'.xls\\ET$'
    in_P=pathfiles+model+date+'.xls\\P$'
    x_coords = "coordoneeX"
    y_coords = "coordoneeY"
    out_ET = "ET"+date+".dbf"
    out_P = "P"+date+".dbf"
    if os.path.exists(pathfiles+model+'_ET'+date+'.shp'):
        arcpy.Delete_management(pathfiles+model+'_ET'+date+'.shp')
    if os.path.exists(pathfiles+model+'_P'+date+'.shp'):
        arcpy.Delete_management(pathfiles+model+'_P'+date+'.shp')
    saved_ET = pathfiles+model+'_ET'+date+'.shp'
    saved_P = pathfiles+model+'_P'+date+'.shp'
    spRef = r"C:\Program Files (x86)\ArcGIS\Desktop10.0\Coordinate Systems\Projected Coordinate Systems\National Grids\France\NTF France II (degrees).prj"
    print (in_ET, x_coords, y_coords, out_ET, spRef)
#starting from here I tried various arcpy data management or conversion - all have error 000732 at 64th iteration
    arcpy.MakeTableView_management (in_ET, "ET"+date+"view")
    arcpy.MakeTableView_management (in_P, "P"+date+"view")
    arcpy.TableToTable_conversion("ET"+date+"view", chemin, out_ET)
    arcpy.TableToTable_conversion("P"+date+"view", chemin, out_P)
    arcpy.MakeXYEventLayer_management("ET"+date+"view", x_coords, y_coords, out_ET, spRef)
    arcpy.MakeXYEventLayer_management("P"+date+"view", x_coords, y_coords, out_P, spRef)
    arcpy.SaveToLayerFile_management(out_ET, saved_ET)
    arcpy.SaveToLayerFile_management(out_P, saved_P)

Find more posts tagged with

Accepted answers

All comments

Anonymous User

This is not possible because I'm using ArcGIS at my office which doesn't have enough money to buy a newer version.

The service packs are free to download if you have an existing ArcGIS license.

ChloeLamy

The first thing you'll want to do is install the latest service pack. There are a host of issues that have been resolved and this could very possibly be one.

http://support.esri.com/en/downloads/patches-servicepacks/view/productid/160/metaid/1876

This is not possible because I'm using ArcGIS at my office which doesn't have enough money to buy a newer version. As it has been written in previous answers that the problem might be in my .xls files I'm currently working on that with pywin32...
If someone has any ideas to help, it will be very nice. I will tell here if I succeed on my own.

K.

MathewCoyle

1. I'm using ArcGIS 10.0 SP0

The first thing you'll want to do is install the latest service pack. There are a host of issues that have been resolved and this could very possibly be one.

http://support.esri.com/en/downloads/patches-servicepacks/view/productid/160/metaid/1876

ChloeLamy

1. I'm using ArcGIS 10.0 SP0
2. When I move my xls files into a new directory, the errors happens at the first iteration, so for the file which didn't bring issue at first (2020_03_04.xls)

I don't know if it's relevant but the .xls files i'm using are created with a python script from a .csv file. As the .csv files containing all my data was too big, I decided to cut it into .xls files, each of them containing 4 columns : ID point, coord X, coord Y and value.
Here is the script I made to do so :

#import
import sys
import os
import math
import csv
from xlwt import Workbook
from decimal import Decimal
import arcpy
from arcpy import env
from arcpy.sa import *
    
# Check out any necessary licenses
arcpy.CheckOutExtension("spatial")

#choice of model
model = "reticB1"

#defining time period
byear = "2020" 
eyear = "2099"

#read global data from .csv file
filePath = "F:/Documents/projections_climatiques/"
fileEvapn = "/evapn/"
filePrtot = "/prtot/"
nomEvapn = model + "_evapn_output.csv"
nomPrtot = model + "_prtot_output.csv"
#path for recording directory for shapefile and .xls
dirfiles = "F:\\Documents\\these2009-2010\\BHspatialise\\avecCLC\\journalier\\"
pathfiles = dirfiles+model+'\\'
if os.path.exists(pathfiles):
    arcpy.env.workspace = pathfiles
    os.chdir(pathfiles)
else:
    print("Directory %s do not exist" % pathfiles)
    raise

#counting days to manage
nbday=0
for year in range (2001, int(byear)):
    if (year%4 == 0 and year%100 != 0 or year%400 == 0):
        count_day=366
        nbday=nbday+count_day
    else :
        count_day=365
        nbday=nbday+count_day

nbday=str(nbday)
print ("number of days before studied period : "+nbday)

an_bi=[]
for year_p in range (int(byear), int(eyear)+1):
    if (year_p%4 == 0 and year_p%100 != 0 or year_p%400 == 0):
        an_bi.append(year_p)

print ("bissextiles years during period: ")
print (an_bi[:])
nbAnneesBi = len(an_bi)
print (nbAnneesBi)

nbday=int(nbday) #number of days before studied period
nbcol=3+nbday #number of columns before studied period
byear=int(byear)
eyear=int(eyear)
nbcolfin=3+nbday+nbAnneesBi*366+(eyear-byear-nbAnneesBi+1)*365-1

dataFiPath1 = filePath+model+fileEvapn+nomEvapn
dataFiPath2 = filePath+model+filePrtot+nomPrtot

csv.field_size_limit(1000000000)
csv.field_size_limit()
x=0

for col in range (nbcol, nbcolfin, 1):
    dataFichier1=open(dataFiPath1,"rb")
    dataFichier2=open(dataFiPath2,"rb")
    book = Workbook()
    sheet1 = book.add_sheet('ET', cell_overwrite_ok=True)
    sheet2 = book.add_sheet('P', cell_overwrite_ok=True)    
    ET=dataFichier1.readline()
    P=dataFichier2.readline()
    x=0
    while ET and P:
        P=P.split(';')
        ET=ET.split(';')
        ET1=ET[0]
        ET2=ET[1]
        ET3=ET[2]
        ETval=ET[col]
        P1=P[0]
        P2=P[1]
        P3=P[2]
        Pval=P[col]
        row1=sheet1.row(x)
        row2=sheet2.row(x)
        if x!=0:
            row1.write(0,ET1)
            row1.write(1,Decimal(ET2))
            row1.write(2,Decimal(ET3))
            row1.write(3,Decimal(ETval))
            row2.write(0,P1)
            row2.write(1,Decimal(P2))
            row2.write(2,Decimal(P3))
            row2.write(3,Decimal(Pval))
        else :
            row1.write(0,ET1)
            row1.write(1,ET2)
            row1.write(2,ET3)
            date2 = ETval
            date = date2.replace(" ","_")
            row1.write(3,ETval)
            row2.write(0,P1)
            row2.write(1,P2)
            row2.write(2,P3)
            row2.write(3,Pval)
        ET=dataFichier1.readline()
        P=dataFichier2.readline()
        x=x+1
    book.save(pathfiles+model+date+'.xls')
    dataFichier1.close()
    dataFichier2.close()
    month = date[5:7]
    year = date[0:4]
    day = date[8:10]

Thanks for helping,

K.

jskinner

Thanks for the data, but I could not reproduce this error.

1. What version of ArcGIS, including Service Packs are you running? i.e. ArcGIS 10.1 SP1

2. If you copy these two excel files to a new directory, so the code is only iterating through these two files, do you still receive the error?

ChloeLamy

Thanks for your answer.
I attached two files to this message. One works fine : reticB12020_03_04.xls, and the other one : reticB12020_03_05.xls is the one leading to the error 000732.

Otherwise, if I have to write my script again I will pay attention to use '#' but I don't think there are issues with indentation because my script runs fine (except for the error). But I won't mind to paste it again if you think it might be a cause of error.

K.

reticB12020_03_05.xls

reticB12020_03_04.xls

jskinner

This is most likely a problem with the XLS file. Would you be able to upload the file, or a portion of it if it's too large?

Also, when posting code, be sure to wrap it in

tags using the # tool above. This will preserve the format/identation.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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