top of page

PYTHON

This Python script takes all of the plain text '.txt' files in your source directory and moves them to your destination directory, and each one will be read into a pandas DataFrame and then written to an Excel file in the same destination directory.

# Import necessary libraries
import os
import shutil
import pandas as pd

# Define source and destination directories
src_dir = "/C:/user/kconner/documents"
dst_dir = "/D:/user/kconner/db_uploads"

# Check if destination directory exists, if not create it
if not os.path.exists(dst_dir):
    os.makedirs(dst_dir)

# Get list of all files in source directory
file_names = os.listdir(src_dir)

# Filter out the files that are not .txt files
file_names = [file for file in file_names if file.endswith('.txt')]

for file_name in file_names:
    # Construct full file path
    source = src_dir + "/" + file_name
    destination = dst_dir + "/" + file_name

    try:
        # Moving file from source to destination
        shutil.move(source, destination)
        print(f"Moved {file_name} to destination directory")

        # Read the file using pandas
        data = pd.read_csv(destination, sep="\t", header=None) # data is tab separated

        # Write the data to an Excel file
        # We'll name the excel file the same as the txt file but with a .xlsx extension
        excel_file = destination.replace('.txt', '.xlsx')
        data.to_excel(excel_file, index=False, header=False)
        print(f"Written data from {file_name} to Excel file")

    except Exception as e:
        print(f"Error occurred with file {file_name}: {e}")

​Details of step-by-step execution:

  1. Imports necessary libraries: This script uses os for operating system dependent functionality, shutil for high-level file operations, and pandas for data manipulation and analysis.

  2. Define source and destination directories: These are the directories where your files are currently stored (source directory), and where you want them to be stored after the script is run (destination directory).

  3. Check if the destination directory exists: If the destination directory doesn't already exist, it's created using the os.makedirs function.

  4. List all files in the source directory: The os.listdir function is used to get a list of all files in the source directory.

  5. Filter out non-.txt files: A list comprehension is used to filter out any files in the directory that don't end with the '.txt' extension.

  6. Loop over each .txt file: For each text file in the source directory, the following operations are performed:

    • Construct full file path: The full file path for both the source and destination directories is constructed.

    • Move file from source to destination: The shutil.move function is used to move the file from the source directory to the destination directory.

    • Read the moved file: The moved file is read into a pandas DataFrame. It's tab-separated, so the separator argument (sep) of the pd.read_csv function is set to '\t'. 

    • Write data to an Excel file: The pandas DataFrame is written to an Excel file in the destination directory. The Excel file has the same name as the original text file but with a '.xlsx' extension instead of '.txt'.

  7. Error handling: If an error occurs while processing a file (for example, if a file can't be moved because it's open in another program), an exception will be raised. This exception is caught and a message is printed to the console, but the script will continue to process any remaining files.

bottom of page