MySQL을 outfile로 내보내기 : CSV 이스케이프 문자

MySQL을 outfile로 내보내기 : CSV 이스케이프 문자

일반적인 장점과 함께 타임시트 데이터베이스 테이블이 있습니다.

id, client_id, project_id, task_id, description, time, date 

더 있지만 그것의 요지는 그것이다.

이 테이블에서 하룻밤 사이에 CSV 파일로 내보내서 사용자에게 데이터 백업을 제공합니다.또한 일부 사용자 정의 리포트와 함께 매크로 Excel 파일의 데이터 Import로도 사용됩니다.

이 모든 것은 제가 php를 사용하여 타임시트를 루프하고 행을 파일로 인쇄하는 것과 함께 작동합니다.

문제는 대용량 데이터베이스를 실행하는 데 몇 시간이 걸릴 수 있으며, 이는 허용되지 않습니다.그래서 MySQL로 다시 썼습니다.INTO OUTFILE실행 시간을 몇 초로 줄였습니다.그것은 훌륭했습니다.

문제는 설명 필드의 새 줄 문자 등을 모두 이스케이프할 수 없다는 것입니다.실제로 사용자는 캐리지 리턴/새 행을 포함하여 여기에 임의의 문자 조합을 입력할 수 있습니다.

이것은 MySQL 코드의 일부입니다.

SELECT id, 
       client,
       project,
       task,
       REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description, 
       time,
       date  
      INTO OUTFILE '/path/to/file.csv'
      FIELDS ESCAPED BY '""'
      TERMINATED BY ',' ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
      FROM ....

그렇지만.....

출력 파일의 소스를 확인하려고 하면 파일에 아직 새로운 행이 존재하기 때문에 Excel용 CSV Import는 Excel 마법사가 작성한 고급 매크로와 피벗 테이블을 모두 파괴합니다.

최선의 행동 방침에 대한 생각은?

당신의 진술은 다음과 같아야 한다고 생각합니다.

SELECT id, 
   client,
   project,
   task,
   description, 
   time,
   date  
  INTO OUTFILE '/path/to/file.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM ts

주로 이 기능을 사용하지 않습니다.FIELDS ESCAPED BY '""'선택,OPTIONALLY ENCLOSED BY '"'설명 필드 등에 대한 트릭을 실행하고 숫자는 Excel 내의 숫자로 처리됩니다(숫자로 구성된 문자열이 아님).

또, 다음과 같이 전화도 시험해 주세요.

SET NAMES utf8;

outfile을 선택하기 전에 문자 인코딩을 인라인으로 하는 데 도움이 될 수 있습니다(모든 UTF8).

어떻게 지내는지 알려주세요.

다음은 Excel 2003 시뮬레이션 (CSV 형식으로 저장)

SELECT 
REPLACE( IFNULL(notes, ''), '\r\n' , '\n' )   AS notes
FROM sometables
INTO OUTFILE '/tmp/test.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

1. Excel은 행 구분 기호용으로 \r\n을 저장합니다.
2. Excel은 열 데이터 내의 줄바꿈 문자를 \n 저장합니다.
3. 먼저 데이터 내의 \r\n을 치환해야 합니다.그렇지 않으면 Excel은 다음 행의 시작이라고 간주합니다.

다음을 시도하면 어떻게 됩니까?

네 짝꿍 대신REPLACE스테이트먼트, 시행:

REPLACE(IFNULL(ts.description, ''),'\r\n', '\n')

그리고, 저는 그게 더 좋을 것 같아요.LINES TERMINATED BY '\r\n'뿐만 아니라'\n'

사실 확인을 위해 출력파일을 보지 않고, 제 추측으로는 그 파일을 없애야 할 것 같습니다.FIELDS ESCAPED BY가치.

MySQL의FIELDS ESCAPED BY는 아마 기대하지 않았던2가지 방법으로 동작하고 있을 것입니다. (1) 1개의 문자만을 의도하고 있기 때문에, 이 경우 따옴표는 아마1개뿐일 것입니다.2) MySQL에서 탈출이 필요하다고 생각되는 각 문자 앞에 사용됩니다.FIELDS TERMINATED BY ★★★★★★★★★★★★★★★★★」LINES TERMINATED BY 세계에서는 이 탈출하는.이는 대부분의 컴퓨팅 세계에서는 타당하지만 Excel이 탈출하는 방식은 아닙니다.

의 더블이라고 합니다.REPLACE가 동작하고 있어 리터럴의 줄바꿈을 공백(Windows 스타일의 줄바꿈의 경우 공백 2개)으로 올바르게 치환하고 있는 것을 확인합니다.그러나 데이터에 쉼표(필드 구분자가 아닌 리터럴)가 있는 경우 Excel은 MySQL과 크게 다르게 취급합니다.그렇다면 Excel에 문제가 있는 잘못된 줄바꿈은 MySQL이 줄바꿈으로 의도한 줄바꿈입니다.

도움이 되지 않을 수도 있지만 다음 내용으로 CSV 테이블을 만들어 볼 수도 있습니다.

DROP TABLE IF EXISTS foo_export;
CREATE TABLE foo_export LIKE foo;
ALTER TABLE foo_export ENGINE=CSV;
INSERT INTO foo_export SELECT id, 
   client,
   project,
   task,
   REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description, 
   time,
   date
  FROM ....

다음 절차는 모든 이스케이프 문제를 해결하고 일반적인 유틸리티를 사용하는 데 도움이 되었습니다.

CREATE PROCEDURE `export_table`(
IN tab_name varchar(50), 
IN select_columns varchar(1000),
IN filename varchar(100),
IN where_clause varchar(1000),
IN header_row varchar(2000))

BEGIN
INSERT INTO impl_log_activities(TABLE_NAME, LOG_MESSAGE,CREATED_TS) values(tab_name, where_clause,sysdate());
COMMIT;
SELECT CONCAT( "SELECT ", header_row,
    " UNION ALL ",
    "SELECT ", select_columns, 
    " INTO OUTFILE ", "'",filename,"'"
    " FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '""' ",
    " LINES TERMINATED BY '\n'"
    " FROM ", tab_name, " ",
    (case when where_clause is null then "" else where_clause end)
) INTO @SQL_QUERY;

INSERT INTO impl_log_activities(TABLE_NAME, LOG_MESSAGE,CREATED_TS) values(tab_name, @SQL_QUERY, sysdate());
COMMIT;

PREPARE stmt FROM @SQL_QUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

끝.

언급URL : https://stackoverflow.com/questions/1119312/mysql-export-into-outfile-csv-escaping-chars