Member Avatar for leghorn

Hi

I have a linux server with mysql installed.There is some data in mysql tables.
Also.I have a .csv file (not from the aforementioned database)
I need to compare the data in the database nad the .csv and print out the difference.

I tried writing a shell script for that but I was unsuccessful.

I need to ask if there's any way I can export the data from mysql tables to .csv file format and use the diff command to see the comparison of the generated file and the file I had initially.

~thanks

  • 3 Contributors
  • 3 Replies
  • 302 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by leghorn

Dani AI

Generated

A concise summary and practical options (builds on and )

Two robust workflows are practical: (A) export the MySQL table to a clean CSV and diff it against the existing file, or (B) import the CSV into a temporary table and use SQL to find missing or differing rows. correctly warned about transforms; ’s import-into-table approach makes row-level comparisons much easier.

How to run a USE xyz; before a SELECT from a shell

  • Multiple SQL statements can be sent in one client call (semicolon-separated).
  • A here-document is convenient when the SQL is longer.

Example: multiple statements with -e

mysql -umyuser -p -e "USE xyz; SELECT id,name,email FROM person;"

Example: here-document (no password shown)

mysql -umyuser -p <<'SQL'
USE xyz;
SELECT id,name,email FROM person;
SQL

Exporting to a reliable CSV
If server file access is allowed, SELECT ... INTO OUTFILE produces quoted, escaped CSV directly on the server:

SELECT id,name,email
INTO OUTFILE '/tmp/person.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM person;

Note: this writes on the database server and requires FILE privilege.

Comparing data inside MySQL (recommended for correctness)
Import the CSV into a staging table and use queries to find additions, deletions, and mismatches:

-- rows in CSV but not in person
SELECT c.* FROM csv_person c LEFT JOIN person p ON p.id=c.id WHERE p.id IS NULL;

-- rows in person but not in CSV
SELECT p.* FROM person p LEFT JOIN csv_person c ON p.id=c.id WHERE c.id IS NULL;

-- rows with same key but different columns (null-safe)
SELECT p.id, p.col1 AS db_col1, c.col1 AS csv_col1
FROM person p JOIN csv_person c ON p.id=c.id
WHERE NOT (p.col1 <=> c.col1 AND p.col2 <=> c.col2);

Practical tips
Ensure identical column order, normalize/truncate whitespace, and handle NULLs before diffing. When using file diffs, remove headers and sort both files first to avoid noisy differences:

sort db.csv > db.sorted
sort other.csv > other.sorted
diff -u db.sorted other.sorted

Recommended Answers

All 3 Replies

Member Avatar for L7Sqr

Unless your CSV file came from a MySQL database with the same structure it is likely that you will need to write a transform to get the data in a format that is what you expect. Although, if you are going to that length you might as well just write the comparison function as well.

Member Avatar for rch1231

Hello,

I would import the CSV file into the MySQL database as a different table. For example if the table in the current database is customers then make the import table customers1. Then use sql queries to compare the two tables. This type of query is what MySQL is designed for.

MySQL has an import function called mysqlimport that can be run form the command line and is designed to import text files like csv files. If you have any type of GUI interface (like phpmyadmin or mysqlworkbench ) most of them have built in options to load a csv file.

The a query to find matching records or records in table1 not in table2 is easy to write.

Hope this helps.

Member Avatar for leghorn

thanks
I found one ..

mysql -uexampleuser -pletmein exampledb -B -e "select * from `person`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

but before select statement could begin,I need to write another statement
use xyz;

how do I induce this ( use xyz; ) in the shell code ???? .. piping isnt helping

~thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.