Requirements – psql tooling installed locally link
It continues to be outrageously easy to use AI to help create tools that assist in daily tasks. Here I create a simple HTML / Javascript tool that will help me import CSV files into Postgres using the command lines tools.
Some pointers
- Its hard coded to import from the c:/csv directory so create this directory first
- Move your csv to this directory and ensure that the file has headers in it (headless csv will try and define first line values as field names)
- Input the variables and review Create table SQL (watch out for awkward characters in field names you may need to manually adjust
- Run the Create Table SQL once you have confirmed that field names are correct
- Run the import command – from command line as ever.
And for those interested in the code of the page..
HTML
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>CSV to Postgres Table Creator</title>
<style>
body {
background-color: black;
color: white;
font-family: Arial, sans-serif;
margin: 0;
padding: 0;
}
.container {
width: 1000px;
margin: 0 auto;
padding: 20px;
}
.file-input-container {
display: flex;
align-items: center;
justify-content: space-between;
margin-bottom: 10px;
}
.file-input-container > * {
margin-right: 10px;
}
.file-input-container > *:last-child {
margin-right: 0;
}
/* Hide the default file input */
input[type="file"] {
display: none;
}
/* Custom file input label styled like a button */
.file-input-label {
display: inline-block;
height: 42px;
line-height: 42px;
padding: 0 15px;
background-color: white;
color: black;
border: 1px solid #ccc;
cursor: pointer;
font-size: 14px;
}
.connection-params {
display: flex;
flex-direction: column;
align-items: flex-end;
margin-bottom: 20px;
}
.connection-params div {
margin-bottom: 10px;
}
.connection-params label {
margin-right: 5px;
font-size: 14px;
}
.connection-params input {
width: 250px;
background-color: lightgrey;
border: 1px solid #ccc;
padding: 5px;
font-size: 14px;
color: inherit; /* Will be updated via JS when typed */
}
textarea {
width: 100%;
background-color: black;
color: lightgreen; /* Slightly lighter green */
border: 1px solid lightgreen;
font-family: monospace;
padding: 10px;
box-sizing: border-box;
overflow: hidden;
resize: none;
}
.section {
margin-bottom: 20px;
}
button {
padding: 10px 15px;
font-size: 14px;
}
/* Make Generate button text green */
#generateBtn {
color: green;
}
.message {
margin-left: 10px;
font-size: 14px;
color: lightgreen;
}
</style>
</head>
<body>
<div class="container">
<h1>Upload CSV and Generate PostgreSQL SQL & psql Import Command</h1>
<div class="file-input-container">
<button id="clearBtn">Clear</button>
<label for="csvFileInput" class="file-input-label" id="fileInputLabel">Choose File</label>
<input type="file" id="csvFileInput" accept=".csv" />
<button id="generateBtn">Generate SQL & Import Command</button>
</div>
<div class="connection-params">
<div>
<label for="hostInput">Host:</label>
<input type="text" id="hostInput" placeholder="localhost">
</div>
<div>
<label for="portInput">Port:</label>
<input type="text" id="portInput" placeholder="5432">
</div>
<div>
<label for="usernameInput">Username:</label>
<input type="text" id="usernameInput" placeholder="postgres">
</div>
<div>
<label for="dbnameInput">Database Name:</label>
<input type="text" id="dbnameInput" placeholder="dbname">
</div>
</div>
<!-- SQL Section -->
<div class="section">
<button id="copySQLBtn">Copy SQL to Clipboard</button>
<span id="copySQLMsg" class="message"></span>
<textarea id="sqlOutput" readonly placeholder="Generated SQL will appear here..."></textarea>
</div>
<!-- psql Command Section -->
<div class="section">
<button id="copyPsqlBtn">Copy psql Command to Clipboard</button>
<span id="copyPsqlMsg" class="message"></span>
<textarea id="psqlOutput" readonly placeholder="Generated psql command will appear here..."></textarea>
</div>
</div>
<!-- Papa Parse Library -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.3.2/papaparse.min.js"></script>
<script>
// Auto-expand textareas based on content
function autoExpand(textarea) {
textarea.style.height = 'auto';
textarea.style.height = textarea.scrollHeight + 'px';
}
// Function to clear copy messages
function clearCopyMessages() {
document.getElementById('copySQLMsg').textContent = '';
document.getElementById('copyPsqlMsg').textContent = '';
}
// Update the file label text and color when a file is selected
document.getElementById('csvFileInput').addEventListener('change', function(e) {
const label = document.getElementById('fileInputLabel');
if (e.target.files.length) {
label.textContent = e.target.files[0].name;
label.style.color = 'red';
} else {
label.textContent = 'Choose File';
label.style.color = 'black';
}
});
// For connection parameter inputs, show typed text in red.
const connectionInputs = ['hostInput', 'portInput', 'usernameInput', 'dbnameInput'];
connectionInputs.forEach(function(id) {
document.getElementById(id).addEventListener('input', function() {
this.style.color = this.value ? 'red' : 'inherit';
});
});
document.getElementById('generateBtn').addEventListener('click', function() {
clearCopyMessages();
const fileInput = document.getElementById('csvFileInput');
if (fileInput.files.length === 0) {
alert('Please select a CSV file first.');
return;
}
const file = fileInput.files[0];
// Derive table name from the CSV file name:
// - Convert to lower case
// - Remove .csv extension
// - Add prefix "import_"
let tableName = file.name.toLowerCase().replace(/\.csv$/i, '');
tableName = 'import_' + tableName;
// Create a dynamic file path for the psql command.
let csvPath = `c:\\csv\\${file.name.toLowerCase().replace(/\s+/g, '_')}`;
// Retrieve connection parameters (defaulting if not provided)
let host = document.getElementById('hostInput').value || "localhost";
let port = document.getElementById('portInput').value || "5432";
let username = document.getElementById('usernameInput').value || "postgres";
let dbname = document.getElementById('dbnameInput').value || "dbname";
// Parse CSV with header row enabled
Papa.parse(file, {
header: true,
complete: function(results) {
const data = results.data;
if (data.length === 0) {
alert('CSV is empty or invalid.');
return;
}
// Process headers: convert each header to lower case and replace spaces with underscores
const headers = results.meta.fields.map(field => field.toLowerCase().replace(/\s+/g, '_'));
// Build CREATE TABLE statement (includes auto-generated pkid)
let sql = `CREATE TABLE ${tableName} (\n`;
sql += ` pkid INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\n`;
headers.forEach((header) => {
sql += ` ${header} TEXT,\n`;
});
sql += ` updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n`;
sql += ` created TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n`;
sql += ');\n\n';
// Build column list for \COPY command (exclude pkid)
let columnList = headers.join(", ");
// Build psql import command using substituted connection parameters and explicit column list.
let psqlCommand = `psql -h ${host} -p ${port} -U ${username} -d ${dbname} -c "\\COPY ${tableName} (${columnList}) FROM '${csvPath}' DELIMITER ',' CSV HEADER;"`;
// Place the generated SQL and psql command into their respective textareas.
const sqlOutput = document.getElementById('sqlOutput');
const psqlOutput = document.getElementById('psqlOutput');
sqlOutput.value = sql;
psqlOutput.value = psqlCommand;
// Auto-expand both textareas to show all content.
autoExpand(sqlOutput);
autoExpand(psqlOutput);
},
error: function(err) {
alert('Error parsing CSV: ' + err.message);
}
});
});
// Copy to clipboard for SQL
document.getElementById('copySQLBtn').addEventListener('click', function() {
const sqlText = document.getElementById('sqlOutput').value;
if (!sqlText) {
alert('No SQL command to copy. Please generate SQL first.');
return;
}
navigator.clipboard.writeText(sqlText).then(function() {
document.getElementById('copySQLMsg').textContent = "Code copied to clipboard";
document.getElementById('copyPsqlMsg').textContent = "";
}, function(err) {
alert('Failed to copy SQL: ' + err);
});
});
// Copy to clipboard for psql command
document.getElementById('copyPsqlBtn').addEventListener('click', function() {
const psqlText = document.getElementById('psqlOutput').value;
if (!psqlText) {
alert('No psql command to copy. Please generate the command first.');
return;
}
navigator.clipboard.writeText(psqlText).then(function() {
document.getElementById('copyPsqlMsg').textContent = "Code copied to clipboard";
document.getElementById('copySQLMsg').textContent = "";
}, function(err) {
alert('Failed to copy psql command: ' + err);
});
});
// Clear button event listener
document.getElementById('clearBtn').addEventListener('click', function() {
// Clear file input and reset label
document.getElementById('csvFileInput').value = "";
const fileLabel = document.getElementById('fileInputLabel');
fileLabel.textContent = "Choose File";
fileLabel.style.color = "black";
// Clear textareas
document.getElementById('sqlOutput').value = "";
document.getElementById('psqlOutput').value = "";
// Clear connection parameter fields
document.getElementById('hostInput').value = "";
document.getElementById('portInput').value = "";
document.getElementById('usernameInput').value = "";
document.getElementById('dbnameInput').value = "";
// Reset the color of connection inputs
connectionInputs.forEach(function(id) {
document.getElementById(id).style.color = "inherit";
});
// Clear copy messages
clearCopyMessages();
});
</script>
</body>
</html>