In my app I’ve got a page dedicated to CSV Imports. I can upload any CSV, and it’ll then map out the headers taken from the csv file, and give the user to option to place it in a particular table, and then select a column from that table.
My tables (which exist in supabase) are:
lawfirm,
lawyerscontactprofiles,
products,
website.
Every time I attempt to import data from the CSV file, the lawfirm table receives all of the entries – but the other three tables only receive one.
I’m presuming it has something to do with the fact that the “lawfirmname” column, which is foreign key in the other three tables, has something to do with this.
I’ve spent the last couple of days on finding a solution, and have no luck yet.
I can see that the information is passed into the array with no issue but then the object will only contain 1 value (in the three other tables)
+page.svelte
<script>
import { parse } from "csv-parse";
let file;
let headers = [];
let data = [];
let columnMappings = [];
const tableColumns = {
lawfirm: [
"lawfirmname",
"clientstatus",
"websiteurl",
"address1",
"address2",
"city",
"stateregion",
"postalcode",
"country",
"phonenumber",
"emailaddress",
"description",
"numberofemployees",
],
lawyerscontactprofiles: [
"firstname",
"lastname",
"email",
"phone",
"profilepicture",
"position",
"accountemail",
"accountphone",
"addressline1",
"suburb",
"postcode",
"state",
"country",
"website",
"lawfirmname",
],
products: [
"websitedevelopment",
"websitehosting",
"websitemanagement",
"newsletters",
"searchengineoptimisation",
"socialmediamanagement",
"websiteperformance",
"advertising",
"lawfirmname",
],
websites: ["url", "dnsinfo", "theme", "email", "lawfirmname"]
};
function handleFileChange(event) {
file = event.target.files[0];
console.log("File selected:", file);
}
async function handleFileUpload() {
if (!file) {
alert("Please select a file to upload.");
return;
}
const reader = new FileReader();
reader.onload = async (event) => {
const csvData = event.target.result;
parse(
csvData,
{
columns: true,
skip_empty_lines: true,
},
(err, output) => {
if (err) {
console.error("Error parsing CSV:", err);
return;
}
headers = Object.keys(output[0]);
data = output;
columnMappings = headers.map((header) => ({
header,
table: "",
column: "",
}));
console.log("Headers:", headers);
console.log("Data:", data);
console.log("Column Mappings:", columnMappings);
},
);
};
reader.readAsText(file);
}
async function handleDataInsert() {
const formattedData = {
lawfirm: [],
lawyerscontactprofiles: [],
products: [],
websites: [],
};
// Step 1: Process each row and propagate lawfirmname where necessary
data.forEach((row, rowIndex) => {
console.log(`Processing row ${rowIndex}:`, row); // Debugging row content
let lawfirmname = "";
const lawfirmObj = {};
const lawyerscontactprofilesObj = {};
const productsObj = {};
const websitesObj = {};
columnMappings.forEach(({ header, table, column }) => {
const value = row[header] ? row[header].trim() : "";
console.log(`Mapping column: ${header} -> table: ${table}, column: ${column}, value: ${value}`); // Debugging column mapping
// Step 2: Check and process each table
if (table === "lawfirm") {
if (column === "lawfirmname") {
let lawfirmNameHeader = columnMappings.find(
(mapping) => mapping.table === "lawfirm" && mapping.column === "lawfirmname"
)?.header;
if (lawfirmNameHeader) {
lawfirmname = row[lawfirmNameHeader]?.trim() || "";
}
}
lawfirmObj[column] = value;
} else if (table === "lawyerscontactprofiles") {
lawyerscontactprofilesObj[column] = value;
} else if (table === "products") {
productsObj[column] = value;
} else if (table === "websites") {
websitesObj[column] = value;
}
});
// Step 3: Propagate the lawfirmname to all related tables if it's available
if (lawfirmname) {
console.log(`Propagating lawfirmname: ${lawfirmname}`);
// Ensure that lawfirmname is filled in the other tables
if (!lawyerscontactprofilesObj.lawfirmname) {
lawyerscontactprofilesObj.lawfirmname = lawfirmname;
}
if (!productsObj.lawfirmname) {
productsObj.lawfirmname = lawfirmname;
}
if (!websitesObj.lawfirmname) {
websitesObj.lawfirmname = lawfirmname;
}
}
// Step 4: Push the data into the formattedData object for each table
if (Object.keys(lawfirmObj).length && lawfirmname) {
console.log("Adding lawfirm object to formatted data:", lawfirmObj);
formattedData.lawfirm.push(lawfirmObj);
}
if (Object.keys(lawyerscontactprofilesObj).length) {
console.log("Adding lawyerscontactprofiles object to formatted data:", lawyerscontactprofilesObj);
formattedData.lawyerscontactprofiles.push(lawyerscontactprofilesObj);
}
if (Object.keys(productsObj).length) {
console.log("Adding products object to formatted data:", productsObj);
formattedData.products.push(productsObj);
}
if (Object.keys(websitesObj).length) {
console.log("Adding websites object to formatted data:", websitesObj);
formattedData.websites.push(websitesObj);
}
});
console.log("Formatted Data:", formattedData);
// Step 5: Remove duplicates for the data being sent
formattedData.lawfirm = removeDuplicates(formattedData.lawfirm, "lawfirmname");
formattedData.lawyerscontactprofiles = removeDuplicates(formattedData.lawyerscontactprofiles, "email");
formattedData.products = removeDuplicates(formattedData.products, "lawfirmname");
formattedData.websites = removeDuplicates(formattedData.websites, "url");
const formData = new FormData();
formData.append("data", JSON.stringify(formattedData));
try {
function getCookie(name) {
const value = `; ${document.cookie}`;
const parts = value.split(`; ${name}=`);
if (parts.length === 2) return parts.pop().split(';').shift();
}
// Get the access token from the cookie
const accessToken = getCookie('supabase-auth-token');
const response = await fetch("/upload", {
method: "POST",
headers: {
"Authorization": `Bearer ${accessToken}`,
},
body: formData,
});
if (!response.ok) {
const errorText = await response.text();
console.error("HTTP error! status:", response.status, "Response text:", errorText);
return;
}
const contentType = response.headers.get("content-type");
if (!contentType || !contentType.includes("application/json")) {
const errorText = await response.text();
console.error("Error: Non-JSON response:", errorText);
return;
}
const result = await response.json();
if (result.success) {
console.log(result.message);
} else {
console.error(result.error);
}
} catch (error) {
console.error("Error uploading data:", error);
}
}
function removeDuplicates(data, uniqueColumn) {
const seen = new Set();
return data.filter((item) => {
const value = item[uniqueColumn];
if (seen.has(value)) {
return false;
}
seen.add(value);
return true;
});
}
</script>
<div class="homeBanner">
<h1 class="leftAlign">Upload CSV</h1>
<div class="searchAndAdd">
<input type="file" accept=".csv" on:change={handleFileChange} />
<button on:click={handleFileUpload}>Import CSV</button>
</div>
</div>
{#if headers.length}
<div class="mappingSection">
{#each columnMappings as mapping, index}
<div class="mappingRow">
<label for="table-{index}">{mapping.header}</label>
<select id="table-{index}" bind:value={mapping.table}>
<option value="">Select table</option>
{#each Object.keys(tableColumns) as table}
<option value={table}>{table}</option>
{/each}
</select>
{#if mapping.table}
<select id="column-{index}" bind:value={mapping.column}>
<option value="">Select column</option>
{#each tableColumns[mapping.table] as column}
<option value={column}>{column}</option>
{/each}
</select>
{/if}
</div>
{/each}
<button class="insertButton" on:click={handleDataInsert}>Insert Data</button>
</div>
{/if}
