How To Validate CSV File Python

When working with data, CSV (Comma Separated Values) files are a common file format to store, share and manipulate data. It is important to validate the CSV data before processing or importing it for further manipulation.

This article will walk you through a step-by-step tutorial on how to validate a CSV file using Python. The main focus of this tutorial will be on checking for the correct number of columns, and appropriate data types, and ensuring the data adheres to any specified constraints.

Step 1: Install Necessary Packages

To complete this tutorial, you will need the Pandas and numpy packages. You can install these using pip:

1
pip install Pandas numpy

Step 2: Prepare CSV File

For this tutorial, let’s consider the following example CSV file with three columns: id, name, and age. Save the content below as sample.csv.

id,name,age
1,Alice,25
2,Bob,26
3,Carol,23

Step 3: Read CSV File Using Pandas

First, we need to import Pandas and read the CSV file into a DataFrame object. The read_csv function from Pandas will be used in this case.

1
2
3
4
5
6
7
import pandas as pd
 
# Read the CSV file
csv_data = pd.read_csv('sample.csv')
 
# Display the data
print(csv_data)

Step 4: Define Validation Rules

For this tutorial, let’s assume we have the following validation rules for our CSV data:

– The id column should be integers greater than 0.
– The name column should be strings with a length of 2 to 10 characters.
– The age column should be integers between 18 and 60.

We will need the numpy library imported as np.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import numpy as np
 
def validate_csv_data(data):
    # Check id column
    if not np.all(data["id"].apply(lambda x: isinstance(x, int) and x > 0)):
        return False
 
    # Check name column
    if not np.all(data["name"].apply(lambda x: isinstance(x, str) and 2 <= len(x) <= 10)):
        return False
 
    # Check age column
    if not np.all(data["age"].apply(lambda x: isinstance(x, int) and 18 <= x <= 60)):
        return False
 
    return True

Step 5: Validate CSV Data

Now you can use the validate_csv_data function to validate your CSV data and decide whether the data is valid for further processing.

1
2
3
4
if validate_csv_data(csv_data):
    print("CSV data is valid.")
else:
    print("CSV data is invalid.")

Full Code

Here’s the complete code for this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import pandas as pd
import numpy as np
 
# Read the CSV file
csv_data = pd.read_csv('sample.csv')
 
# Display the data
print(csv_data)
 
def validate_csv_data(data):
    # Check id column
    if not np.all(data["id"].apply(lambda x: isinstance(x, int) and x > 0)):
        return False
 
    # Check name column
    if not np.all(data["name"].apply(lambda x: isinstance(x, str) and 2 <= len(x) <= 10)):
        return False
 
    # Check age column
    if not np.all(data["age"].apply(lambda x: isinstance(x, int) and 18 <= x <= 60)):
        return False
 
    return True
 
if validate_csv_data(csv_data):
    print("CSV data is valid.")
else:
    print("CSV data is invalid.")
 
  id   name  age
0   1  Alice   25
1   2    Bob   26
2   3  Carol   23
CSV data is valid.

Conclusion

In this tutorial, you learned how to validate a CSV file using Python with the help of the Pandas and numpy packages. You learned how to read a CSV file, define validation rules, and check your data according to these rules. You can modify the code provided above to create custom validation rules depending on your specific data needs. This method will help ensure data quality and integrity before further processing or importing.