2

I'm exporting a database report with a shell file. If I run the query in PHPMyAdmin the file comes out fine, new lines at the end of each row in the database only.

However when I run the query in my shell script using outfile to generate the file I get /n, /r and /r/n in some of the columns content. I can't work out what causes this or how to avoid it.

The issue only seems to be caused in the colour column which is the third in the example export.

Query:

mysql $MYSQLOPTS << EOFMYSQL
SELECT Product_Name, Item_Size, Item_Colour, Item_Price, Current_Stock, Item_Price * Current_Stock AS Stock_Value
FROM Items
ORDER BY Product_Name
INTO OUTFILE '$FILE'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
EOFMYSQL

Example result:

"Scarf_in_Peach","ONE SIZE","12/04-B2B2 ",10.00,3,30.00
"Scarf_in_Pink","ONE SIZE ","11/06-odds-C1C12100",10.00,0,0.00
"Scarf_in_Red","ONE SIZE ","11/06-B7B2-C1C12100",10.00,0,0.00
"Scarf_in_Sand_","ONE SIZE","11/06-B1I3-C1C12100
",10.00,0,0.00
"Scarf_in_Sand_/_Blue_Flowers","ONE SIZE","12/04-B2E2-C1C12100 ",10.00,4,40.00
"Scarf_in_Teal","ONE SIZE","11/06-B5G1-C1C12100
",10.00,0,0.00
"Scarf_in_Teal_/_Red_Flowers","ONE SIZE","12/04 - B2B2 ",10.00,1,10.00
"Sunrise_Skinnies","16","ODD-R1S009-1-BLUE",20.00,0,0.00
"Sunrise_Skinnies","8","ODD-R1S009-1

BLUE",20.00,0,0.00
davejal
  • 5,431
  • 10
  • 33
  • 73
Chris Morris
  • 878
  • 2
  • 15
  • 32
  • Did you test your data to see if you actually have spaces (i.e. \n) in your field? I'm asking because at the end I see spaces of some of the records, maybe you also have `\n` – davejal Jan 27 '16 at 11:47
  • In the database some of them do have line breaks, however when i export to CSV from PHPmyAdmin I don't. Even when I don't select "Remove carriage return/line feed characters within columns" They are fine. – Chris Morris Jan 27 '16 at 11:54
  • 1
    In your query you could replace carriage return and line feed characters with empty string. – Shadow Jan 27 '16 at 11:55
  • Could you check out [this question](http://stackoverflow.com/q/356578/3664960) – davejal Jan 27 '16 at 11:58
  • @davejal I can't see anything in that question relating to extra line breaks in column content. – Chris Morris Jan 27 '16 at 12:03
  • 1
    @Shadow that does seem to work if i change the colour column in the select to `REPLACE(REPLACE(Item_Colour, CHAR(13), ' '), CHAR(10), ' ')` Though a way to escape all columns without the long string would be ideal but it will suffice for this script. Thank you. – Chris Morris Jan 27 '16 at 12:04
  • So that answer helped you? Would a simple trim over each column help? – davejal Jan 27 '16 at 12:09

2 Answers2

0

You have 2 options:

  1. Replace carriage return and line feed characters with empty string within your query. Pro: it is completely up to you what characters you filter out and from which fields. Con: you have to create expression for each affected field manually.

  2. Use FIELDS ESCAPED BY character option of the SELECT ... INTO OUTFILE ... command:

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used when necessary to avoid ambiguity as a prefix that precedes following characters on output:

  • The FIELDS ESCAPED BY character

  • The FIELDS [OPTIONALLY] ENCLOSED BY character

  • The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

  • ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII “0”, not a zero-valued byte)

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

Pro: this is a fast and standard approach, that you can easily apply to all export functionality using this approach. Con: less flexible. For example, if the lines terminated by option is set to \n, then \r is not going to be escaped, which can still cause some issues on some systems.

Shadow
  • 30,859
  • 10
  • 44
  • 56
0

The problem is that in your data you have line breaks in the field itself, phpmyadmin handles this for you automatically. You should execute the query by trying to remove the extra line breaks from the fields.

davejal
  • 5,431
  • 10
  • 33
  • 73