File sharing lock count exceeded // python import to access table.
If anyone can assist with this you’ll be a life saver. I have a script in python that is attempting to automate a manual process in which a user imports a .txt with 1,500,000 rows into an access table. In writing the python, I’ve landed on using a bulk insert which basically takes a data frame, and then splits it into .csv’s with some row size like 50,000 and then I insert into the access table from the individual .csv’s.
The problem is it’s a company PC and I can’t increase the MaxLocksPerFile default value of 9500. I’m doing 5,000 row .csv files and committing every 10 batches. So that’s inserting 5,000 rows until it hits 50,000 then it’s committing. It does about 350,000 before throwing the ‘File Sharing Lock Count Exceeded’ error.
I’ve tried every combination of batch size and commit interval one can conceive. I’ve tried executemany to execute one sql statement many times, I’ve tried execute to load 1.5M rows and then commit them. Everything has failed.
Has anyone done something like this in Access? Also, before you say to use a more robust DB, I would if I could. My director uses Access still so at this point, I’m stuck with it. I would use Sql server if I could.
Code here: function to import data
def bulk_insert_to_access(file_path, table_name, temp_file_path=None, chunk_size=5000, commit_interval=100):
"""
Inserts data from a dataframe to Access using an optimized bulk insert.
This method works by saving data to a temporary CSV file and then using an SQL SELECT INTO command.
"""
# Step 1: Read the .txt file and prepare the dataframe
df = pd.read_csv(file_path, delimiter="\t", encoding="utf-8")
# Adjust your data as needed (like column cleaning, etc.)
column_specs = get_column_specs(table_name)
df_adjusted = adjust_data_to_specs(df, column_specs)
if not temp_file_path:
temp_file_path = c for os.path.dirname(file_path)
# Break DF into chunks
chunks = [df_adjusted.iloc[i:i + chunk_size] for i in range(0, len(df_adjusted), chunk_size)]
# List to keep track of temporary files for cleanup later
chunk_file_paths = []
# Step 2: Perform the bulk insert via SQL SELECT INTO method
conn = pyodbc.connect(connect_str)
cursor = conn.cursor()
try:
for idx, chunk in enumerate(chunks):
# Save the chunk to a temporary CSV file
chunk_file_path = os.path.join(temp_file_path, f"temp_data_chunk_{idx}.csv")
chunk.to_csv(chunk_file_path, index=False, header=False) # Save to temporary file
chunk_file_paths.append(chunk_file_path) # Track file for later cleanup
# Perform the bulk insert for each chunk
sql = f"""
INSERT INTO [{table_name}]
SELECT * FROM [Text;FMT=TabDelimited;HDR=NO;DATABASE={os.path.dirname(chunk_file_path)}].[{os.path.basename(chunk_file_path)}]
"""
try:
# Execute SQL statement to insert data from chunked file
start_time = time.time()
cursor.execute(sql)
# Commit after every `commit_interval` chunks
if (idx + 1) % commit_interval == 0:
conn.commit()
time.sleep(1) # Add a small delay after commit to release locks
elapsed_time = time.time() - start_time
print(f"Bulk insert for chunk {idx} completed in {elapsed_time:.2f} seconds")
except pyodbc.Error as e:
print(f"Error during bulk insert for chunk {idx}: {e}")
conn.rollback() # Rollback only the current chunk
# Commit after the last chunk if not already committed
conn.commit()
except pyodbc.Error as e:
print(f"Error during the bulk insert process: {e}")
conn.rollback()
finally:
# Cleanup temporary files
for file_path in chunk_file_paths:
if os.path.exists(file_path):
try:
os.remove(file_path)
except OSError as e:
print(f"Error deleting temporary file {file_path}: {e}")
cursor.close()
conn.close()
