2

We have to restore a database in MySQL but some columns have content enclosed by " and another not like in this sample:

00.168.93.70,null,ES,0,htc,33a48a1515b20691,"htc;HTC One (REL4.4.2)",m7,53e4b0f1e733a4a95e,-Free-2.1.0.6.f,4.4.2,unknown,13,"Internet app",com.app.android.free.debug,null,"HTC One",test@gmail.com,"31.5000,34.7500",APA91bE1E-7-tO1fgbYCs5kbiUapri1zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qevehFmhCmGXwArSkCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm_,"en_EN,zh_CN"
unknown,unknown,unknown,0,htc,33a48515b20691,"htc;HTC One (REL4.4.2)",m7,5385a31e733a4a95f,Free-2.1.0.6.f,4.4.2,unknown,0,unknown,com.app.android.free.debug,unknown,"HTC One",test@gmail.com,unknown,APA9zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qeveAtIxxi0X3mCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm,

How Can I restore it properly ?

0

Is there a problem? The quoting is usually "optional". Or did you need the quotes to be kept with the columns?

You can control which case you have by including/excluding the ENCLOSED BY or OPTIONALLY ENCLOSED BY clause in LOAD DATA.

If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY option.

If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character.

-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html

0
LOAD DATA INFILE 'x:/folder/filename.csv'
INTO TABLE tablename ( field1,    -- load value into the field directly
                       field2,    -- when it is enclosed, d-quotes are saved
                       ... , 
                       @dummyN,   -- load value with wrapping into UDV
                       ...)
SET fieldN = TRIM(BOTH '"' FROM @dummyN) -- extract value from UDV and store into the field

This allows to remove d-quote chars when they are wrappers and store when they're a part of value, if this occures at the same time (for different fields).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.