Introduction
In this guide, we’ll cover how to import cost data from a CSV file into IC Project using Python. This involves fetching existing cost categories, tax rates, and projects from the IC Project API, creating new entries if necessary, and linking costs to existing projects.
Python Code Breakdown
1. Import Required Libraries
The code starts by importing necessary libraries:
import csv
import requests
from datetime import datetime
2. Fetch Existing Cost Categories
This function retrieves existing cost categories from the IC Project API:
def get_existing_cost_categories(api_url, headers):
response = requests.get(f"{api_url}/finance/cost-categories", headers=headers)
if response.status_code == 200:
return {category['name']: category for category in response.json()}
else:
print(f"Error fetching cost categories: {response.status_code}")
return {}
3. Fetch Existing Tax Rates
Similar to cost categories, this function retrieves existing tax rates:
def get_existing_tax_rates(api_url, headers):
response = requests.get(f"{api_url}/finance/tax-rates", headers=headers)
if response.status_code == 200:
return {tax_rate['name']: tax_rate for tax_rate in response.json()}
else:
print(f"Error fetching tax rates: {response.status_code}")
return {}
4. Fetch Existing Projects
This function retrieves existing projects from the API:
def get_existing_projects(api_url, headers):
response = requests.get(f"{api_url}/projects", headers=headers)
if response.status_code == 200:
return {project['name']: project for project in response.json()}
else:
print(f"Error fetching projects: {response.status_code}")
return {}
5. Create New Cost Category
If a cost category does not exist, this function will create it:
def create_cost_category(name, api_url, headers):
data = {
"name": name,
}
response = requests.post(f"{api_url}/finance/cost-categories", headers=headers, json=data)
if response.status_code == 201:
return response.json()
else:
print(f"Error creating cost category {name}: {response.status_code}, {response.text}")
return None
6. Create New Tax Rate
Similarly, this function creates a new tax rate if it does not already exist:
def create_tax_rate(name, value, api_url, headers):
data = {
"name": name,
"value": value,
"isDefault": False
}
response = requests.post(f"{api_url}/finance/tax-rates", headers=headers, json=data)
if response.status_code == 201:
return response.json()
else:
print(f"Error creating tax rate {name}: {response.status_code}, {response.text}")
return None
7. Convert CSV Data to JSON Format
This function processes the CSV file, checks for existing cost categories, tax rates, and projects, and creates new ones if necessary:
def csv_to_costs(file_path, api_url, headers):
existing_categories = get_existing_cost_categories(api_url, headers)
existing_tax_rates = get_existing_tax_rates(api_url, headers)
existing_projects = get_existing_projects(api_url, headers)
costs = []
with open(file_path, mode='r', encoding='utf-8') as file:
csv_reader = csv.DictReader(file)
for row in csv_reader:
category_name = row['category']
if category_name not in existing_categories:
new_category = create_cost_category(category_name, api_url, headers)
if new_category:
existing_categories[category_name] = new_category
cost_category = existing_categories.get(category_name, {})
tax_rate_name = row['taxRate']
tax_rate_value = float(row['taxRateValue'])
if tax_rate_name not in existing_tax_rates:
new_tax_rate = create_tax_rate(tax_rate_name, tax_rate_value, api_url, headers)
if new_tax_rate:
existing_tax_rates[tax_rate_name] = new_tax_rate
tax_rate = existing_tax_rates.get(tax_rate_name, {})
project_name = row.get('project', '')
project_id = existing_projects.get(project_name, {}).get('id') if project_name else None
cost = {
"name": row['name'],
"description": row['description'],
"priceNet": float(row['priceNet']),
"priceGross": float(row['priceGross']),
"date": row['date'],
"isBilled": row['isBilled'].lower() == 'true',
"isPosted": row['isPosted'].lower() == 'true',
"createdAt": datetime.now().isoformat(),
"updatedAt": datetime.now().isoformat(),
"costCategory": cost_category.get('id'),
"taxRate": tax_rate.get('id'),
"financeProject": project_id
}
costs.append(cost)
return costs
8. Send Cost Data to the API
Finally, this function sends the cost data to the IC Project API:
def send_costs_to_api(costs, api_url, headers):
for cost in costs:
response = requests.post(f"{api_url}/finance/costs", headers=headers, json=cost)
if response.status_code == 201:
print(f"Success: Cost {cost['name']} was sent.")
else:
print(f"Error: Failed to send cost {cost['name']}. Response code: {response.status_code}, error: {response.text}")
Example CSV File Format
Below is an example of the CSV file format that can be used with this script:
name,description,priceNet,priceGross,date,isBilled,isPosted,category,taxRate,taxRateValue,project
Sample Cost 1,Description for cost 1,100.00,123.00,2024-09-15,true,false,Category A,Standard Tax,10,Project X
Sample Cost 2,Description for cost 2,200.00,246.00,2024-09-16,false,true,Category B,Reduced Tax,5,
Example Usage
Here’s how you can use the functions:
csv_file = 'sample-costs.csv' # Path to the CSV file
instance_slug = 'your-instance-slug' # Your instance slug
api_url = f'https://app.icproject.com/api/instance/{instance_slug}' # API endpoint
api_key = 'your-api-key' # Your API key
headers = {
'X-Auth-Token': api_key, # Authorization token
'Content-Type': 'application/json', # Content type for the request
'Accept': 'application/json', # Expected response type
}
costs_data = csv_to_costs(csv_file, api_url, headers)
send_costs_to_api(costs_data, api_url, headers)
Conclusion
This guide demonstrates how to import cost data from a CSV file into IC Project using Python. By following these steps, you can efficiently manage and update your cost data in IC Project, ensuring that your financial records are accurate and up-to-date.
You can find full source code of this example on GitHub
No Comments yet!