MySql into file adding blank entry at last entry

I am learning MySql and need help with ‘INTO FILE’. I have managed to terminate with commas but the last entry in the file also contains a comma which means when imported treats it as a blank entry.

Could someone show me the corret way to use ‘INTO FILE’ by way of an example. I have posted my query if it helps but it is almost certainly incorrect. Many thanks

Select
    boxes.custref
From
    boxes
Where
    boxes.customer = 'demo' 
AND department = 'demo'
INTO OUTFILE '/temp/demodept.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',';

The correct way to use INTO FILE is to have a file that is correctly formatted.

Does your file have a blank line at the end of it?

Hi. No there is no blank line. The last entry contains data. Thanks

And your last line ends with a comma. Which means your file is telling the engine that the line has terminated, and a new line starts…so yes, there is a blank line at the end of your file.

Why are you terminating your lines with the same delimiter as your fields? Terminate with a line break ('\n'), and then check the outfile for a blank line at the end of the file; if one exists, remove it before importing the file again, and you wont end up with a blank entry.

I have double checked the query in phpmyadmin and this blank entry is only there when i run export function. Do you have example of the correct way to code the into file? Thanks

it seems you are returning a single column

hence i question the need for FIELDS TERMINATED BY ','

i also wonder if you need LINES TERMINATED BY ','

Thanks for reply. Will that give me comma seperated file.

commas separating what? there’s only one value per row!

except because he’s also said the line separator is a comma, it’s also all one row :wink:

not so much a CSV, more of a… list.

captain_picard_facepalm.gif

I need to use comma seperated for the csv for inclusion into db. Thanks

a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p

How many rows of data do i have? How many fields do I have?

here’s the example from da manual

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

check the LINES TERMINATED BY character

Thank you. Will try later