MySql into file adding blank entry at last entry

Databases
1

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 ',';
2

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?

3

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

4

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.

5

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

6

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 ','

7

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

8

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

9

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.

10

captain_picard_facepalm.gif

11

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

12

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?

13

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

14

Thank you. Will try later

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.