Python Reading and writing csv files

CSV is an abbreviation for comma separated values. CSV files are the most often used format for data imports and exports to spreadsheets and databases.

Comma-Separated Values (CSV) is a popular and widely used file format for storing and exchanging tabular data. It consists of plain text data where each line of the file represents a row of the table, and the values in each row are separated by commas (or other delimiters such as semicolons or tabs). Each row typically corresponds to a record, and each field (or value) within a row represents a specific attribute or column of the data.

Common uses of CSV files and reasons why CSV remains widely used in various applications:

Data Exchange: CSV is a universal format that can be read and written by virtually any data processing tool or programming language. This makes it an excellent choice for exchanging data between different systems or applications.

Spreadsheet Compatibility: CSV files can be easily imported and exported by popular spreadsheet software like Microsoft Excel, Google Sheets, and LibreOffice Calc. This allows users to work with the data in familiar environments.

Database Import/Export: Many database management systems (DBMS) and data analysis tools support CSV as a standard import and export format. You can use CSV files to transfer data between databases, which can be especially useful for data migration or integration.

Simplicity: CSV files are easy to create and read since they have a straightforward structure. This simplicity makes them a suitable choice for small to medium-sized datasets that don't require complex data structures.

Human-Readable: Unlike binary file formats, CSV files are human-readable, making them easy to inspect and edit with a text editor. This is helpful for troubleshooting and data validation.

Lightweight: CSV files are relatively lightweight and do not consume a lot of storage space. This is advantageous for situations where file size is a concern, such as email attachments or web downloads.

Language Agnostic: CSV can be processed by a wide range of programming languages, which makes it a versatile choice for developers. Libraries and modules for reading and writing CSV are readily available for many programming languages.

Historical Usage: CSV has been in use for decades, and many legacy systems and applications still rely on it. This historical precedent contributes to its continued popularity.

While CSV is versatile and widely used, it may not be suitable for all data storage and exchange needs. It lacks support for complex data structures, data types, and metadata, which might be essential in some applications. In such cases, more advanced data formats like JSON, XML, or binary formats may be preferred. However, for its simplicity, compatibility, and versatility, CSV remains a popular choice for many data-related tasks.

Export SQL Server table data to CSV

Below sample code connects to sqlserver , read data from student table and create csv file. based on the requirments, you change the name of the table / or read query.

import os
os.system('cls')

import pyodbc
import csv
import datetime


# Define the connection parameters
server = 'localhost\SQLExpress'
database = 'testDB'
username = 'sa'
password = '*****'

# Create a connection string
connection_string = f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Record the start time
start_time = datetime.datetime.now()

# SQL query to retrieve data
query = 'SELECT * FROM Students'

# CSV file path
csv_file_path = 'files\sqlserver\output.csv'

try:
    # Establish a database connection
    conn = pyodbc.connect(connection_string)

    # Create a cursor
    cursor = conn.cursor()

    # Execute the SQL query
    cursor.execute(query)

    print("Connection to database is established")
    print("Data fetch started")
    
    # Fetch all the data
    data = cursor.fetchall()
    print("Data fetch completed")

    # Get column names
    column_names = [column[0] for column in cursor.description]

    # Record the end time
    end_time = datetime.datetime.now()
    elapsed_time = end_time - start_time
    print(f"Total records fetched{ cursor.rowcount}, Total time taken {elapsed_time}")

    print("Pushing data to CSV")
    # Write data to a CSV file
    with open(csv_file_path, 'w', newline='') as csv_file:
        csv_writer = csv.writer(csv_file)
        csv_writer.writerow(column_names)  # Write column names as headers
        csv_writer.writerows(data)

    print(f'Data exported completed, CSV path = {csv_file_path}')

except Exception as e:
    print(f'An error occurred: {str(e)}')
finally:
    # Close the database connection
    conn.close()


# Record the end time
end_time = datetime.datetime.now()
elapsed_time = end_time - start_time
print(f"Total time taken {elapsed_time}")

Import CSV data into SQLite

Create the required table in the SQLite database, based on your requirments, make the necessary changes in the below code

import os
os.system('cls')

import csv
import sqlite3
import datetime


# Define your CSV file and SQLite database paths
csv_file_path = 'files\sqlserver\output.csv'
connection_string = "files/sqlite/my_database.db"

connection = sqlite3.connect(connection_string )
cursor = connection.cursor()

# Record the start time
start_time = datetime.datetime.now()

# total record read from CSV
record_counts=0

# Open the CSV file and read its data
with open(csv_file_path , 'r') as csv_file:
    csv_reader = csv.reader(csv_file)
    
    # Skip the header row if it exists
    next(csv_reader)  
    for row in csv_reader:
        # Adjust this to match your CSV columns#
        column1, column2 = row   
        # Insert the data into the database
        cursor.execute('INSERT INTO my_table (column1, column2) VALUES (?, ?)', (column1, column2))
        # increment the counts
        record_counts=record_counts+1

# Commit the changes to the database and close the connection
connection.commit()
connection.close()

print('Data imported from CSV to SQLite database.')

# Record the end time
end_time = datetime.datetime.now()
elapsed_time = end_time - start_time

print(f"Total records inserted {record_counts}, Total time taken {elapsed_time}")