/** @jsxImportSource https://esm.sh/react */ import { parse } from "https://esm.sh/csv-parse/browser/esm/sync"; import React, { useState } from "https://esm.sh/react"; import { createRoot } from "https://esm.sh/react-dom/client"; function App() { const [file, setFile] = useState(null); const [tableName, setTableName] = useState(""); const [message, setMessage] = useState(""); const [isUploading, setIsUploading] = useState(false); const handleFileChange = (event) => { setFile(event.target.files[0]); }; const handleTableNameChange = (event) => { setTableName(event.target.value); }; const handleSubmit = async (event) => { event.preventDefault(); if (!file) { setMessage("Please select a file"); return; } if (!tableName.trim()) { setMessage("Please enter a table name"); return; } const formData = new FormData(); formData.append("file", file); formData.append("tableName", tableName); setIsUploading(true); try { const response = await fetch("/upload", { method: "POST", body: formData, }); const result = await response.json(); setMessage(result.message); } catch (error) { setMessage("An error occurred while uploading the file"); } setIsUploading(false); setFile(null); }; return (

CSV to SQLite Uploader

{message &&

{message}

}

View Source

); } function client() { createRoot(document.getElementById("root")).render(); } if (typeof document !== "undefined") { client(); } export default async function server(request: Request): Promise { const { sqlite } = await import("https://esm.town/v/stevekrouse/sqlite"); const { parse } = await import("https://esm.sh/csv-parse/sync"); const SCHEMA_VERSION = 1; const KEY = new URL(import.meta.url).pathname.split("/").at(-1); function sanitizeColumnName(name: string): string { return name.replace(/[^a-zA-Z0-9_]/g, "_").replace(/^(\d)/, "_$1"); } if (request.method === "POST" && new URL(request.url).pathname === "/upload") { const formData = await request.formData(); const file = formData.get("file"); const customTableName = formData.get("tableName"); let sanitizedColumns: string[]; if (file instanceof File && typeof customTableName === "string") { const content = await file.text(); const records = parse(content, { columns: true, skip_empty_lines: true }); if (records.length > 0) { const tableName = `${customTableName.replace(/[^a-zA-Z0-9_]/g, "_")}_${SCHEMA_VERSION}`; const columns = Object.keys(records[0]); sanitizedColumns = columns.map(sanitizeColumnName); // Create table if not exists await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${tableName} ( id INTEGER PRIMARY KEY AUTOINCREMENT, ${sanitizedColumns.map(col => `${col} TEXT`).join(", ")} ) `); // Insert data for (const record of records) { const placeholders = columns.map(() => "?").join(", "); const values = columns.map(col => record[col]); await sqlite.execute( ` INSERT INTO ${tableName} (${sanitizedColumns.join(", ")}) VALUES (${placeholders}) `, values, ); } return new Response( JSON.stringify({ message: `Successfully uploaded ${records.length} records to the database.` }), { headers: { "Content-Type": "application/json" }, }, ); } else { return new Response(JSON.stringify({ message: "The CSV file is empty or invalid." }), { headers: { "Content-Type": "application/json" }, }); } } else { return new Response(JSON.stringify({ message: "No file was uploaded." }), { headers: { "Content-Type": "application/json" }, }); } } return new Response( ` CSV to SQLite Uploader

`, { headers: { "content-type": "text/html", }, }, ); } const css = ` body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; } h1 { color: #333; } form { margin-bottom: 20px; } input[type="file"] { margin-right: 10px; } input[type="text"] { margin-right: 10px; padding: 5px; } button { background-color: #4CAF50; border: none; color: white; padding: 10px 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 16px; margin: 4px 2px; cursor: pointer; } p { color: #666; } `;

csvToSqliteUploader

Version 4