MySQL – Output to CSV with Column Names at the Bottom

csvduplicationMySQL

I'm trying to export the results of the query listed below to csv and have the column names in the first row. However, when I run the code and open up the csv the header is listed as the last row. I have no idea why. What am I missing?

SELECT 'id','state','First_Name', 'Last_Name', 'Phone','County'
UNION ALL
SELECT id, state, First_Name, Last_Name, Phone, County
FROM tx.students
GROUP BY First_Name, Last_Name, Phone,
HAVING COUNT(*)>1
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/_students_dupes_test3.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Best Answer

The records order is defined by your ORDER BY clause expression. All id field values are converted to string type due to the type of the base (first) subquery field type. Of course the digits are first and letters last.

Replace it with

ORDER BY id='id' DESC, id

Related Solutions

MySQL – CSV UPDATE (not INSERT) into existing table

REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.

Here is something else you can do.

Suppose your table is called name_city and it looks like this:

CREATE TABLE name_city
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

and you want to do the LOAD DATA INFILE.

Here are your steps to update the city based on id:

Step 01) Create another table to use for the import

CREATE TABLE name_city_import LIKE name_city;

Step 02) Drop the name column from the import table

ALTER TABLE name_city_import DROP COLUMN name;

Step 03) Perform the import into name_city_import

LOAD DATA INFILE 'somefile.csv' INTO TABLE name_city_import ...

Step 04) Perform an UPDATE JOIN

UPDATE name_city_import B
INNER JOIN name_city A USING (id)
SET A.city = B.city;

Step 05) Remove the import table

DROP TABLE name_city_import;

That's it.

Give it a Try !!!

MySQL Import CSV – Only Half the Lines Imported

You might have to check the last character of each line

Instead of this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\n';

Try this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\r\n';

Best Answer

Related Solutions

MySQL – CSV UPDATE (not INSERT) into existing table

MySQL Import CSV – Only Half the Lines Imported

Related Question