Importing and Exporting CSV Data

Document the header, field order, delimiter, quote behavior, line endings, encoding, date format, and representation of missing values. CSV can contain quoted commas and newlines, so splitting each line on commas is not a reliable parser. A blank field may mean empty text or NULL depending on the tool and options. Product codes and postal codes can contain leading zeros and should remain text if those zeros matter. Decide whether prices use a dot decimal separator and whether currency appears in a separate column. A header label does not always cause a tool to match columns by name; many bulk commands still use the declared positional column list. Verify the specific tool rather than assuming a spreadsheet export defines everything.

Load raw fields into text columns in a staging table with a job ID and source-row identity where needed. Validate required keys, date and number formats, ranges, duplicates, and parent references before promotion. A direct bulk load into production can fail partway or silently coerce values according to engine and mode. Staging makes rejected rows reviewable and keeps accepted business tables consistent. Use explicit INSERT ... SELECT column lists when promoting data. Decide whether one invalid row cancels the job or only that row. Preserve counts for raw, accepted, rejected, and duplicate records. Protect sensitive staging data and clean it under a documented retention policy.

PostgreSQL COPY with a filename operates from the database server's filesystem and requires the relevant permissions. COPY ... FROM STDIN transfers data through the connection. The psql client command \copy manages client-side files and uses that connection transfer, so it has different file-location and privilege implications. Do not ask for broad server-file roles merely because a developer's laptop CSV is unavailable on the server. Choose FORMAT csv, HEADER behavior, column list, and null policy explicitly. Version-specific error-tolerance options should be verified against the selected PostgreSQL release before use. A successful bulk command still needs business validation if staging contains raw text.

MySQL LOAD DATA INFILE reads a server-side file under its server configuration and privileges. LOAD DATA LOCAL INFILE transfers a client-side file and depends on both client and server policy. LOCAL is not a universal harmless workaround; review its security behavior and enable it only where needed. secure_file_priv and account permissions can restrict server file operations. Set field terminator, enclosure, escaping, line endings, header skip, and character set according to the actual file. Strict modes, warnings, and conversion behavior affect invalid data handling. Check warnings and counts rather than declaring success from a completed command alone. Use a staging design when the source is not already trusted and validated.

Select explicit columns and a deterministic order so the exported format is stable. Apply authorization and tenant filters before export. A report with sensitive rows needs controlled access and may require audit information. PostgreSQL COPY ... TO STDOUT can stream query results through a connection, while server filenames have server-side access rules. MySQL INTO OUTFILE likewise writes on the server under restrictions and generally will not overwrite an existing file. Application-side CSV writing is often useful for downloads and portability. Use a real CSV serializer rather than string joining. Decide whether the export is a live query or a consistent snapshot, especially when it spans a long-running job.

Spreadsheet software can interpret cells beginning with formula markers as executable formulas; ordinary CSV quoting does not necessarily prevent that. Apply a documented spreadsheet-safe text policy when that is the consumer, while preserving exact machine-to-machine exports separately if needed. Use controlled download filenames and avoid server path exposure. Encrypt and access-control sensitive exports under the data policy. Reconcile row counts and representative values after import, including encoding, dates, nulls, and leading zeros. Reruns need a stable key or idempotency design rather than duplicate insertion. Test a quoted comma, multiline field, blank value, malformed date, duplicate code, and wrong column count. These cases reveal whether the contract is truly understood.

Plan a PostgreSQL import from a developer's local products.csv. Use psql \copy into a staging table rather than assuming the database server can read the laptop path. Specify the positional column list and CSV contract. The solution validates codes, decimal prices, nonnegative stock, duplicates, and category references before INSERT ... SELECT promotion. Produce rejected-row counts and a reviewed rerun policy. Then design a browser CSV export using an application serializer and a spreadsheet formula policy for names. Explain how the approach changes for MySQL LOAD DATA and which LOCAL settings and risks must be reviewed. Do not enable broad file privileges just to avoid understanding file location.

Create this staging table in the intended disposable database. Its fields remain text so that malformed quantities can be reviewed before conversion. The example CSV header is code,quantity, followed by rows 0012,0 and 0040,7. Save it as stock.csv on the machine running psql. The client file path is relative to that client's working directory; it is not a database-server path. The role needs appropriate table privileges, and the installed psql must connect to the selected test database.

CREATE TABLE stock_stage (
    code TEXT,
    quantity_text TEXT
);

Run these commands inside psql. Backslash-copy is a psql command rather than generic SQL, and each command should remain on one line.

\copy stock_stage (code, quantity_text) FROM 'stock.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT code, quantity_text FROM stock_stage ORDER BY code) TO 'stock-review.csv' WITH (FORMAT csv, HEADER true)

Expected result: two raw staging rows are imported, and stock-review.csv is written on the client machine with a header and those rows in code order. PostgreSQL's default CSV null rules apply here; agree on a different explicit null representation if your supplier distinguishes blank text from missing values differently. The declared column list is positional even though the file has a header. Review quantities, required codes, duplicates, and category references before promoting rows into a constrained business table. Avoid casting malformed quantity text directly: reject it or use the selected validated conversion strategy. The export is a review file, not proof that every row is accepted. Restrict its access if the actual feed contains confidential data.

Review data cleaning, row changes, and constraints.

See PostgreSQL COPY and MySQL LOAD DATA for dialect-specific file and parsing rules.