59 lines
2.5 KiB
Python
59 lines
2.5 KiB
Python
import re
|
|
import shutil
|
|
import pandas as pd
|
|
from openpyxl import load_workbook
|
|
|
|
|
|
def extract_coord(url, param):
|
|
match = re.search(rf'[?&]{param}=([-\d.]+)', str(url))
|
|
return match.group(1) if match else None
|
|
|
|
|
|
def process_data(source_file_path, destination_template_path, output_path):
|
|
# Read source CSV (semicolon-delimited)
|
|
src = pd.read_csv(source_file_path, sep=';')
|
|
src.columns = src.columns.str.strip()
|
|
|
|
def build_address(row):
|
|
parts = []
|
|
if pd.notna(row.get('civique')):
|
|
parts.append(str(int(row['civique'])))
|
|
if pd.notna(row.get('appartement')):
|
|
parts.append(f"App. {row['appartement']}")
|
|
if pd.notna(row.get('rue')):
|
|
parts.append(str(row['rue']).strip())
|
|
return ' '.join(parts)
|
|
|
|
# Copy the template so we preserve its formatting/styles
|
|
shutil.copy(destination_template_path, output_path)
|
|
wb = load_workbook(output_path)
|
|
ws = wb.active
|
|
|
|
# Map column names to their Excel column index (based on header row 1)
|
|
header = {cell.value: cell.column for cell in ws[1]}
|
|
|
|
# Write each row starting at row 2
|
|
for i, row in src.iterrows():
|
|
excel_row = i + 2 # row 1 is the header
|
|
|
|
ws.cell(excel_row, header['ID de Ligne']) .value = (i + 1) * 100
|
|
ws.cell(excel_row, header['Province']) .value = 'PQ'
|
|
ws.cell(excel_row, header['Code barre']) .value = str(row['codeBar']).strip()
|
|
ws.cell(excel_row, header['Latitude']) .value = extract_coord(row['lien map targo'], 'y')
|
|
ws.cell(excel_row, header['Longitude']) .value = extract_coord(row['lien map targo'], 'x')
|
|
ws.cell(excel_row, header['Numéro Électrique']) .value = str(row['tagHydro']).strip()
|
|
ws.cell(excel_row, header['Adresse']) .value = build_address(row)
|
|
ws.cell(excel_row, header['Ville']) .value = str(row['ville']).strip()
|
|
ws.cell(excel_row, header['Traitement de poteau']) .value = 'non traitée'
|
|
|
|
wb.save(output_path)
|
|
print(f"Done! {len(src)} row(s) written into {output_path}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
SOURCE_FILE = r"C:\Users\Antoine\PycharmProjects\excel_import\selectionElements.csv"
|
|
TEMPLATE_FILE = r"C:\Users\Antoine\PycharmProjects\excel_import\CAPermitPoteauxImportation_Gabarit_fr-CA.xlsx"
|
|
OUTPUT_FILE = r"C:\Users\Antoine\PycharmProjects\excel_import\CAPermit_Final_Output.xlsx"
|
|
|
|
process_data(SOURCE_FILE, TEMPLATE_FILE, OUTPUT_FILE)
|