#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0

    Python loop problem


    Hi All, i'm new to Python and I'm having some issues with my script. It's supposed to import all the excel files in a folder and write them into a SQL Server table. The script runs if I just point it to a single excel file, but i'm stumped at setting up an iteration loop that will read through all the excel files in a folder. I need to insert some type of loop inbetween the "if file_to_import.endswith('.XLS'):" & the "column_count=10" lines. Any ideas or suggestions would be very much appreciated!

    Code:
    # Import arcpy module 
    from xlrd import open_workbook ,cellname 
    import arcpy 
    import pyodbc as p 
    import os 
    
    # Database Connection Info 
    server = "Server" 
    database = "DB" 
    connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=' + server + ';DATABASE=' + database + ';' + 'Trusted_Connection=yes') 
    
    # Assign path to Excel files 
    folder_to_import = '\\\\Location\\DATA' 
    l_files_to_import = os.listdir(folder_to_import) 
    for file_to_import in l_files_to_import: 
    if file_to_import.endswith('.XLS'): 
    
    column_count=10 
    
    # Open entire workbook 
    book = open_workbook(file_to_import) 
    
    # Use first sheet 
    sheet = book.sheet_by_index(0) 
    
    # Open connection to SQL Server Table 
    conn = p.connect(connStr) 
    
    # Get cursor 
    cursor = conn.cursor() 
    
    # Assign the query string without values once, outside the loop 
    query = "INSERT INTO HED_EMPLOYEE_DATA (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 
    
    # Iterate through each row 
    
    for row_index in range(1, sheet.nrows): 
    
    row_num = row_index 
    Company = sheet.cell(row_index,0).value
    Contact = sheet.cell(row_index,1).value
    Email = sheet.cell(row_index,2).value
    Name = sheet.cell(row_index,3).value
    Address = sheet.cell(row_index,4).value
    City = sheet.cell(row_index,5).value
    CentralCities = sheet.cell(row_index,6).value
    EnterpriseZones = sheet.cell(row_index,7).value
    NEZ = sheet.cell(row_index,8).value
    CDBG = sheet.cell(row_index,9).value
    
    values = (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) 
    
    cursor.execute(query, values) 
    
    # Close cursor 
    cursor.close()
    
    # Commit transaction 
    conn.commit() 
    
    # Close SQL server connection 
    conn.close()
  2. #2
  3. Commie Mutant Traitor
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jun 2004
    Location
    Norcross, GA (again)
    Posts
    1,804
    Rep Power
    1569
    Unfortunately, the code's indentation somehow got flattened, even with the code tags; it most likely happened when you pasted the code in, that will sometimes happen. I cannot be certain, but I think that the following is the indentation you needed:

    Code:
    # Import arcpy module 
    from xlrd import open_workbook ,cellname 
    import arcpy 
    import pyodbc as p 
    import os 
    
    # Database Connection Info 
    server = "Server" 
    database = "DB" 
    connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=' + server + ';DATABASE=' + database + ';' + 'Trusted_Connection=yes') 
    
    # Assign path to Excel files 
    folder_to_import = '\\\\Location\\DATA' 
    l_files_to_import = os.listdir(folder_to_import) 
    for file_to_import in l_files_to_import: 
        if file_to_import.endswith('.XLS'): 
    
            column_count=10 
    
            # Open entire workbook 
            book = open_workbook(file_to_import) 
    
            # Use first sheet 
            sheet = book.sheet_by_index(0) 
    
            # Open connection to SQL Server Table 
            conn = p.connect(connStr) 
    
            # Get cursor 
            cursor = conn.cursor() 
    
            # Assign the query string without values once, outside the loop 
            query = "INSERT INTO HED_EMPLOYEE_DATA (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 
    
            # Iterate through each row 
    
            for row_index in range(1, sheet.nrows): 
    
                row_num = row_index 
                Company = sheet.cell(row_index,0).value
                Contact = sheet.cell(row_index,1).value
                Email = sheet.cell(row_index,2).value
                Name = sheet.cell(row_index,3).value
                Address = sheet.cell(row_index,4).value
                City = sheet.cell(row_index,5).value
                CentralCities = sheet.cell(row_index,6).value
                EnterpriseZones = sheet.cell(row_index,7).value
                NEZ = sheet.cell(row_index,8).value
                CDBG = sheet.cell(row_index,9).value
    
                values = (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) 
    
                cursor.execute(query, values) 
    
            # Close cursor 
            cursor.close()
    
    # Commit transaction 
    conn.commit() 
    
    # Close SQL server connection 
    conn.close()
    Rev First Speaker Schol-R-LEA;2 JAM LCF ELF KoR KCO BiWM TGIF
    #define KINSEY (rand() % 7) λ Scheme is the Red Pill
    Scheme in Short Understanding the C/C++ Preprocessor
    Taming Python A Highly Opinionated Review of Programming Languages for the Novice, v1.1

    FOR SALE: One ShapeSystem 2300 CMD, extensively modified for human use. Includes s/w for anthro, transgender, sex-appeal enhance, & Gillian Anderson and Jason D. Poit clone forms. Some wear. $4500 obo. tverres@et.ins.gov
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    Hi Schol-R-LEA thanks for the reply!

    When I pasted the code the indentation was indeed ruined.

    Some indentation was wrong in my original code though, which I fixed thanks to your post.

    However I am now getting the error: "IOError: [Errno 2] No such file or directory: 'Report_Test.XLS'"

    The file Report_Test.XLS does exist in the location it's trying to pull from and the fact that it's reading the file name then saying it doesn't exist is confusing.

    Any ideas about that error message?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    Is it possible that the environment is case-sensitive? In most environments "XLS" is not the same as "xls".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    I tried changing everything to lower-case xls, but no luck. I think the problem has something to do with the fact of how these xls files were created. They were exported from a workflow in byte format, so I think maybe it has something to do with that.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    39
    Rep Power
    2
    As you are listing the files in the folder_to_import directory, should you not include that when you try to open the file, as in:
    Code:
    book = open_workbook(folder_to_import + "\\" + file_to_import)
    or even:
    Code:
    book = open_workbook( os.path.join(folder_to_import , file_to_import) )

IMN logo majestic logo threadwatch logo seochat tools logo