CSV를 SQL로: 견적 버그 없이 스프레드시트를 INSERT 문으로 전환합니다

마침내 이것을 만들게 한 작업은 지루했고,제가 갖지 못한 한 시간이 걸렸습니다. 한 클라이언트가 1,800 개의 제품 행이 담긴 스프레드시트를 건네주며 다음날 아침 데모 전에 준비 데이터베이스에 로드하라고 했습니다. API 도 없고,가져오기 화면도 없고,그냥 CSV 와 MySQL 테이블이 기다리고 있었습니다. 저는 모두가 처음으로 하는 일을 했습니다: 파일을 열고,모양을 맞추기 위해 손으로 INSERT 문 몇 개를 작성하고,값 복사를 시작했습니다. 세 행 후에 아포스트로피가 들어간 제품 이름, O'Brien's Tools, 전체 문을 깼습니다, 왜냐하면 작은 따옴표가 문자열을 일찍 닫았고 MySQL이 나머지에 질식했기 때문입니다, 즉 스프레드시트에서 SQL을 손으로 쓰는 것이 5 분 작업이 아니라 1,800 번의 잘못된 단계 기회가 있는 지뢰밭을 인용하는 것을 깨닫는 순간입니다 그래서 저는 그 문을 만들었습니다 CSV 에서 SQL 변환기그리고 이 가이드는 그 안에 있는 모든 옵션의 배경이 되는 추론입니다.

TL;DR: CSV-SQL 변환기는 CSV 파일을 읽고 다음을 생성합니다 CREATE TABLE 그리고 INSERT 데이터베이스가 저장해야 하는 문. Toolz 도구는 RFC 4180 상태 머신으로 파일을 구문 분석하고,각 열이 정수,소수, 부울 또는 텍스트인지 추론하고,MySQL, PostgreSQL 또는 SQLite 에 대한 식별자를 인용하고,모든 값을 올바르게 이스케이프하고,빈 셀을 로 바꿉니다 NULL니다. CSV 를 붙여넣기,방언을 고르고,바로 실행할 수 있는 스크립트를 복사합니다. 모든 것이 클라이언트 측에서 실행됩니다: 업로드 없음,가입 없음,오프라인으로 작동합니다.

나는 Laravel 및 React 에 SaaS 제품을 구축하고 나는 워드 프레스 플러그인을 배송,그래서 데이터베이스에 테이블 형식의 데이터를로드하는 것은 일회성보다는 주간 잡일이다. 때로는 클라이언트 내보내기,때로는 신선한 환경을위한 시드 파일,때로는 테스트를위한 빠른 정착물이다. CSV 는 항상 도착 형식이며,SQL 은 항상 그것이 끝날 필요가있는 곳이다. 손으로 하나에서 다른 하나를 얻는 것은 정확히 브라우저 도구가 지워야하는 반복적이고 오류가 발생하기 쉬운 작업의 종류입니다,그래서이 내가 그 아포스트로피가 내 수입을 깰 때 내가 가지고 있었으면 좋겠다 가이드입니다.

CSV to SQL 변환기란 무엇입니까?

CSV-SQL 변환기는 쉼표로 구분된 값의 행을 가져와 테이블을 생성하고 데이터를 로드하는 SQL 문을 생성합니다. CSV 자체는 느슨하게 표준화되어 있습니다 RFC 4180 일반적인 형태를 설명하고,기존의 관행을 받아쓰기보다는 문서화한다는 것을 명시한다. 첫 번째 행을 열 이름으로 읽고,다음 행을 모두 레코드로 취급하며,두 가지를 방출한다: a CREATE TABLE 합리적인 유형과 집합으로 열을 정의하는 명령문입니다 INSERT 값을 전달하는 문. 출력은 TablePlus 또는 DBeaver 와 같은 데이터베이스 클라이언트에 붙여넣거나 마이그레이션에 드롭하거나 파이프에 넣을 수 있는 일반 SQL 스크립트입니다 mysql, psql, 또는 sqlite3 한 단계로 테이블을 빌드하고 채우는 명령줄입니다.

카테고리가 존재하는 이유는 번역이 과소평가하기 쉬운 방식으로 까다롭기 때문입니다. 모든 텍스트 값은 작은 따옴표로 감싸야 합니다. 값 내부의 모든 작은 따옴표는 문자열을 종료하지 않도록 두 배로 늘려야 합니다. 숫자와 부울은 인용되지 않은 상태로 두어야 하며 그렇지 않으면 데이터베이스가 텍스트와 귀하의 것으로 저장됩니다 WHERE price > 100 이상하게 행동할 것이다.빈 세포는 보통 될 필요가 있다 NULL 빈 문자열이 아니라. 공백이나 예약 단어가 있는 열 이름은 데이터베이스에 맞는 문자로 인용해야 합니다. 수천 개의 행에서 이들 중 하나를 놓치면 전체 배치가 실패하고 종종 잘못된 선을 가리키는 오류가 발생합니다. 변환기는 이러한 모든 규칙을 매번 같은 방식으로 적용하는데,이것이 전체 값 제안입니다.

도구는 어떻게 각 column's 유형을 결정합니까?

유용한 변환기와 멍청한 변환기를 구분하는 부분입니다. 순진한 도구가 모든 열을 만듭니다 TEXT 그리고 기술적으로 작동하지만 숫자가 문자열로 정렬되고 캐스팅 없이는 산술을 할 수 없는 테이블을 제공하는 완료 호출합니다. The Toolz CSV를 SQL 도구로 변환합니다 대신 열의 모든 값을 스캔하고 모든 값에 맞는 가장 좁은 유형을 선택합니다.

일단 보면 논리는 간단합니다. 열에 비어 있지 않은 모든 값이 정수이면 열은 정수형이 됩니다. 모든 값이 숫자이지만 소수점이 있는 경우 부동형이 됩니다. 모든 값이 단어이면 true 또는 false을, 부울이 됩니다. 혼합 내용이나 글자가 있는 모든 것을 포함한 다른 것은 텍스트로 돌아갑니다. 좋은 방법으로 사람들을 사로잡는 고의적인 예외가 하나 있습니다: 같은 값입니다 007 또는 00123 정수가 아닌 텍스트로 유지, 선행 제로는 거의 항상 열이 ID, 우편 번호, 또는 제로가 중요한 전화 번호이며 숫자로 저장하면 손실 될 것을 의미하기 때문에 그 단일 규칙은 하나 이상의 제품 SKU 열을 조용히 손상에서 저를 저장했습니다.

모든 열이 텍스트가 되기를 원하는 경우 추론을 끌 수 있습니다. 이는 로드 후 직접 유형을 변경할 계획일 때 안전한 선택입니다. 그러나 일반적인 경우 도구를 유형 추론하도록 하는 것은 생성된 것을 의미합니다 CREATE TABLE 데이터를 평평하게 만드는 대신 데이터와 일치시키며 테이블은 존재하는 순간 사용할 수 있습니다.

어떤 SQL 방언을 선택해야합니까?

SQL 은 표준입니다 영어 철자가 표준인 방식,즉 모든 데이터베이스가 고유한 악센트를 가지고 있다는 것을 의미합니다. 여러분이 고른 방언은 출력에서 세 가지를 변경합니다: 식별자가 인용되는 방식,유형 이름이 호출되는 방식,참과 거짓이 쓰여지는 방식 네 가지 옵션이 어떻게 다른지입니다.

우려	MySQL	PostgreSQL	SQLite	표준 SQL
식별자 인용	백틱 `col`	큰따옴표 "col"	큰따옴표 "col"	큰따옴표 "col"
정수형	INT	INTEGER	INTEGER	INTEGER
소수 유형	DOUBLE	DOUBLE PRECISION	REAL	REAL
부울 유형	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
텍스트 유형	VARCHAR(255)	TEXT	TEXT	TEXT
부울 값	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

실용적인 지침은 방언을 실제로 로드하는 데이터베이스에 일치시키는 것입니다. 왜냐하면 차이점은 외관상 차이가 아니기 때문입니다. MySQL 은 ANSI 모드에 있지 않는 한 PostgreSQL 이 원하는 이중 인용 식별자를 거부하며 PostgreSQL 은 없습니다 TINYINT. SQLite 에는 실제 부울 유형이 전혀 없으므로 부울은 정수가되므로 도구가 작성합니다 1 그리고 0 mysql과 SQLite 모두에 해당되지만 TRUE 그리고 FALSE postgresql 및 표준 SQL 의 경우. 확실하지 않거나 이식 가능한 것을 작성하는 경우 표준 SQL 이 가장 보수적 인 선택입니다. 일단 문이 생성되면 항상 다음을 통해 실행할 수 있습니다 SQL 포맷터 출력이 마이그레이션 파일로 들어가기 전에 미리 인쇄합니다.

도구를 사용하여 CSV를 SQL로 변환하려면 어떻게 해야 합니까?

흐름이 의도적으로 짧습니다. CSV 를 입력 상자에 붙여 넣거나 샘플 로드 를 클릭하여 id,이름, 역할,부울 및 급여 열이 포함된 작업 예제를 확인합니다.

테이블 이름을 대상 테이블을 호출해야 하는 대로 설정하고 SQL 방언을 선택합니다. 그런 다음 출력 옵션을 결정합니다. Leave "Include CREATE TABLE" on 테이블이 아직 존재하지 않거나,만 생성하려면 끕니다 INSERT 이미 있는 테이블에 로드할 때 명령문. Keep "Infer column types" on 입력된 테이블의 경우 또는 off 모든 것을 텍스트로 만듭니다. "Multi-row INSERT" for one compact statement with many value tuples,which loads fastest,or turn it off to get one 을 선택합니다 INSERT 버전 제어에 더 친숙하고 개별적으로 행을 실행할 수 있는 행당. "빈 셀을 NULL"로 남겨두세요. 특별히 빈 문자열을 저장하기를 원하지 않는 한.

구분 기호는 자동으로 감지하거나 손으로 설정할 수 있습니다. 자동 감지는 각각 처음 여러 줄을 동일한 열 수로 얼마나 일관되게 분할하는지에 따라 쉼표,세미콜론, 탭 및 파이프 점수를 매깁니다. 이 열은 유럽 세미콜론 파일 및 탭으로 구분된 내보내기를 올바르게 처리합니다. SQL 로 변환을 클릭하면 출력이 행과 열의 개수와 함께 나타나며 중복된 열 이름이나 누더기 행에 대한 경고도 함께 복사하여 실행합니다. 헤더 줄 없이 내보내기가 도착한 경우 "첫 번째 행은 header"이며 도구는 열의 이름을 지정합니다 column_1, column_2그런 다음 모든 행을 데이터로 처리합니다.

왜 올바른 가치 탈출이 그렇게 중요한가요?

잘못된 이스케이프는 단순한 버그가 아니기 때문에 보안 취약점의 클래스입니다. 내 첫 번째 가져오기를 손으로 깨뜨린 아포스트로피는 SQL 삽입 뒤에 있는 동일한 메커니즘입니다: 이스케이프되지 않은 경우 값 내부의 작은 따옴표는 문자열을 일찍 끝내고 뒤에 오는 모든 것을 SQL 로 해석하도록 합니다. 응용 프로그램에서 매개변수화된 쿼리로 이 문제를 해결합니다. 여기서 데이터베이스 드라이버는 값과 코드를 엄격하게 분리합니다. CSV 에서 정적 SQL 스크립트를 생성할 때 해당 분리가 없으므로 이스케이프 작업은 생성된 텍스트 자체에서 정확해야 합니다.

이 도구는 ANSI SQL 규칙을 따릅니다: 텍스트 값은 작은 따옴표로 감싸고 값 내부의 작은 따옴표는 두 배가됩니다. 그래서 O'Brien's Tools 된다 'O''Brien''s Tools'지원되는 데이터베이스의 모든 하나는 원래 문자열로 다시 읽습니다. 숫자와 부울은 따옴표없이 방출되므로 올바른 유형으로 저장되고 식별자는 dialect's 자신의 문자로 인용되므로 열이 호출됩니다 order 또는 select 예약어와 충돌하지 않습니다. 이것은 정확히 인간이 시간 압박에 의해 잘못되고 도구가 매번 올바르게되는 지루한 정확성의 종류이며,이를 자동화하는 전체 요점입니다. 탈출이 형식에 따라 어떻게 비교되는지 궁금하다면,the CSV 에서 JSON 변환기 이스케이프 문자가 이중 인용문이 아닌 백슬래시인 JSON 측에서도 동일한 문제에 직면합니다.

민감한 CSV 데이터를 온라인으로 변환하는 것이 안전한가요?

이 도구의 경우,예, 그리고 그 이유는 페이지의 약속보다는 아키텍처입니다. 모든 단계,CSV 구문 분석,열 유형 추론,값 이스케이프 및 문 빌드,자신의 브라우저 탭 내에서 자바 스크립트로 실행됩니다. 업로드가없고 서버 왕복이 없으며 아무것도 기록되거나 저장되지 않습니다. browser's 네트워크 탭을 열고 Convert: no request 를 클릭하면 페이지를 떠날 수 있습니다. 페이지가로드되면 인터넷과의 연결을 완전히 끊을 수 있으며 SQL 이 계속 생성됩니다.

CSVs 사람들이 SQL 로 변환하는 것은 종종 비즈니스가 가지고있는 가장 민감한 파일이기 때문에 중요합니다. 고객 테이블,주문 내역,사용자 기록 및 가격 책정 데이터는 모두 데이터베이스에 도착하기 전에 CSV 로 이동합니다. 서버에 파일을 업로드하는 변환기는 아무리 좋은 의미라도 개인 스프레드 시트를 다른 사람으로 바꿉니다 & # 39;s 로그 항목. 클라이언트 측 처리는 질문을 완전히 제거합니다: 데이터는 시작한 기계를 떠나지 않습니다. 그 기본값은 Toolz.dev 의 모든 도구에서 고의적이며,나는 긴 인수를 에 썼습니다 온라인 도구에 대한 데이터 개인 정보 보호 가이드 완전한 추론을 원하는 사람을 위해.

CSV to SQL 은 실제 워크플로우에서 어디에 적합합니까?

변환은 거의 전체 작업이 아니며 파이프라인의 한 스테이션입니다. 제가 가장 자주 치는 패턴은 시딩입니다: 클라이언트가 스프레드시트를 보내고 SQL 스크립트로 변환하고 해당 스크립트를 실행하여 스테이징 또는 개발 데이터베이스를 채우므로 앱이 작업할 현실적인 데이터를 출력합니다. 라이브 연결이 아닌 일반 스크립트이기 때문에 repo 에 시드 파일로 확인하고 풀 요청에서 검토하고 모든 환경에서 다시 실행하기가 쉽습니다.

그 주변의 도구는 데이터가 다음에 무엇을하고 있는지에 달려 있습니다. 변환하기 전에 정렬 가능한 그리드로 파일을 눈알해야하는 경우, CSV 뷰어 잘못된 행이 되기 전에 잘못된 행을 발견할 수 있도록 동일한 RFC 4180 구문 분석을 테이블로 렌더링합니다 INSERT. 대상이 데이터베이스가 아닌 API 또는 구성 파일인 경우 CSV 에서 JSON 변환기 대신 올바른 홉과 그 형제입니다 JSON에서 CSV로 데이터를 스프레드시트로 다시 넘겨야 할 때 그 반대를 처리합니다. 그리고 일단 SQL이 존재하면, SQL 포맷터 마이그레이션을 위해 읽을 수 있는 것으로 정리합니다. 이들 중 어느 것도 데이터를 업로드하지 않으므로 다시 생각하지 않고 동일한 개인 파일에 연결할 수 있습니다. 이러한 종류의 데이터 작업을 위한 키트를 조립하는 경우 내 웹 개발자 툴킷 가이드 조각들이 어떻게 연결되는지 살펴봅니다.

알만한 가치가 있는 한계는 무엇입니까?

한계에 대한 정직은 도구를 신뢰하는 것의 일부입니다. 변환기는 표준을 생성합니다 CREATE TABLE 그리고 INSERT 명령문,즉 기본 키,외부 키,인덱스 또는 열 제약 조건을 추론하지 않습니다. 왜냐하면 그 정보 중 어느 것도 플랫 CSV 에 존재하지 않기 때문입니다. 각 열에 대해 합리적인 유형을 선택하지만 VARCHAR(255) mysql 텍스트의 경우 측정값이 아닌 기본값입니다; 긴 설명 열이 있는 경우 해당 열을 확장할 수 있습니다 TEXT 로드 후, 그리고 있어야 할 열이 있는 경우 DATE 또는 DATETIME 도구가 잘못된 형식을 추측하지 않도록 날짜를 텍스트로 처리하므로 이를 변경하고 싶을 것입니다.

다중 행 INSERT 는 작고 빠르지만 매우 큰 파일은 매우 긴 문 하나를 생성하며 일부 데이터베이스는 하나의 문 크기 또는 자리 표시자 수를 제한합니다. 수만 개의 행을 로드하고 제한에 도달하면 하나로 전환합니다 INSERT 데이터베이스가 항상 받아들일 명령문에 대해 더 큰 스크립트를 거래하는 행당. 마지막으로,도구는 로드 스크립트를 생성하기 위한 것이지,멀티 기가바이트 파일을 스트리밍하기 위한 것이 아닙니다. 왜냐하면 전체 입력은 브라우저 메모리에 살고 있기 때문입니다. 대부분의 사람들이 실제로 가지고 있는 일상적인 내보내기,씨드 파일 및 픽스처의 경우 이러한 한계 중 어느 것도 물지 않습니다. 도구를 잘 사용하는 것과 도구에 놀라는 것의 차이일 뿐입니다.

자주 묻는 질문

CSV 파일을 SQL 로 변환하려면 어떻게 해야 하나요? CSV를 에 붙여넣습니다 CSV 에서 SQL 변환기를 설정하고 테이블 이름을 설정하고 SQL 방언을 선택합니다. 헤더 행을 열 이름으로 읽고 각 열 유형을 추론한 후 a를 생성합니다 CREATE TABLE 플러스 INSERT 데이터베이스에서 복사하고 실행할 수 있는 명령문. 모든 것이 브라우저에서 발생하므로 파일이 업로드되지 않습니다.

어떤 SQL 데이터베이스를 지원합니까? 변환기는 MySQL,PostgreSQL, SQLite,표준 SQL 에 대한 문을 출력합니다. 선택한 방언은 식별자 인용과 유형 및 부울 구문을 제어하므로 스크립트는 편집없이 해당 데이터베이스에서 실행됩니다. MySQL 은 백틱과 TINYINT(1) 부울, PostgreSQL 및 표준 SQL은 큰따옴표를 사용합니다 TRUE 또는 FALSE.

열 유형을 어떻게 결정합니까? 각 열은 데이터의 모든 값에 대해 검사됩니다. 비어 있지 않은 모든 값이 정수인 경우 열은 정수 유형이 되고,모든 숫자는 십진수 유형이 되며,모든 참 또는 거짓 값은 부울 유형이 되고,다른 모든 것은 텍스트와 같은 선행 0 을 갖는 값입니다 007 일반적으로 중요한 식별자이기 때문에 텍스트를 유지합니다. 추론을 끄면 모든 열 텍스트를 만들 수 있습니다.

테이블을 생성합니까, 아니면 삽입물만 생성합니까? 둘 다 기본적으로. a 를 방출합니다 CREATE TABLE 추론된 열 유형 뒤에 명령문이 있습니다 INSERT 문. 를 돌릴 수 있습니다 CREATE TABLE 테이블이 이미 존재하고 행만 로드하면 되는 경우 꺼짐.

따옴표와 특수 문자는 어떻게 처리되나요? 텍스트 값은 작은따옴표로 묶이고 값 내부의 작은따옴표는 두 배로 늘어납니다. 이것이 표준 SQL 이스케이프입니다 O'Brien 된다 'O''Brien'. 숫자와 부울은 인용되지 않은 상태로 유지되고 식별자는 MySQL의 백틱이나 다른 방언의 큰따옴표로 인용되므로 예약된 단어와 같습니다 order 는 진술을 어기지 않는다.

빈 세포는 어떻게 되나요? 기본적으로 빈 셀은 다음과 같습니다 NULL일반적으로 누락된 값에 대해 원하는 값입니다. 대신 빈 문자열을 저장하려면 NULL로 비우기 옵션을 끄고 빈 셀은 두 개의 작은따옴표가 됩니다.

헤더 행이 없는 CSV를 변환할 수 있나요? 예. 헤더 옵션을 끄고 도구에서 열의 이름을 지정합니다 column_1, column_2그리고,등등, 모든 행을 데이터로 취급합니다. 헤더 라인 없이 발송되는 원시 내보내기에 유용합니다.

내 CSV 파일이 아무데나 업로드되나요? 아니요. 파싱 및 SQL 생성은 브라우저에서 JavaScript 로 실행됩니다. 아무것도 전송,로그 또는 저장되지 않으며 도구가 로드된 후에도 오프라인으로 계속 작동하므로 변환하는 동안 네트워크 탭을 보고 확인할 수 있습니다.