Double quotes when exporting files

Question

Can you ask me a question regarding the export of files in Mysql? , I have already given a search on but found no answer...

Next, I have a table that stores the NAME of some clients, I need to put this column in a certain layout to import in another CRM.

When I export this data the NAME column receives double quotes and this cannot happen because it will cause error when importing in CRM.

Here is the Load that I do to enter the data in tb teste2.

Load data local infile 'C:/sql/31.03/Nao_part02.txt'
INTO TABLE teste2
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(nome,@dt_imp)
SET
dt_imp= date(now())
;

All files imported for tb teste2 have no double quote.

Does anyone have any idea why this happens ?

obg!

Answer 1

Does anyone have any idea why this happens ?

I found that this problem happens because in the source file there are two characters that we are not seeing at first, but are present. To see these characters, you can open the file with the Notepad++ application and you will see something like the following:

If you don’t see the characters in the Notepad++ app, I’ll show you how to enable this view at the end of the reply.

Note that at the end of the lines, except for the last, we have CR and LF who are also known as \r and \n respectively.

In computing, line feed (LF), new line or line break is a control character that indicates that a line must be added. Already the Carriage Return (CR, a command that would return the printer head to the leftmost position).

Learn a little more about special characters

LF (character: n, Unicode: U + 000A, ASCII: 10, Hex: 0x0a): This is simply the character ' n' that we all know from the first programming days. This character is commonly known as 'Line feed' or 'New line character'.

CR (character: r, Unicode: U + 000D, ASCII: 13, Hex: 0x0d): This is simply the character ' r'. This character is commonly known as carriage return or 'Return by car'.

The Import Problem

The problem is that at the time of import, is being informed LINES TERMINATED BY '\n' when you really should be informing LINES TERMINATED BY '\r\n'.

Solution to fix the import:

1. Change the line LINES TERMINATED BY '\n' for LINES TERMINATED BY '\r\n'
2. Check that the last line has been inserted correctly, in case otherwise, just add a blank line at the end, but probably won’t be necessary. I say this, as we can observe that in the last line there are no markup characters CR and LF.

Correcting existing data in the Database

Run the query in the table teste2 :

SELECT replace(nome,char(13),'***') FROM teste2;

Since its initial import considered only the \n, means that the \r was included in the database, the above query will show this character as image below:

Solution for existing data:

I suggest holding a UPDATE updating the records that have the \r as follows:

UPDATE teste2
SET 
    nome = REPLACE(nome, CHAR(13), '')
WHERE
    nome LIKE '%\r'

If you don’t see the characters in the Notepad app++: Enable "Show End of Line" option as shown below.

Font searched for characters: EOL or End of Line or newline ascii Character