CSV в SQL: превратите электронную таблицу во ВСТАВКУ отчетов без ошибок цитирования

Задача, которая, наконец, заставила меня построить это было скучно и это стоило мне часа, которого у меня не было Клиент вручил мне электронную таблицу из 1800 строк продуктов и попросил загрузить ее в их промежуточную базу данных перед демонстрацией на следующее утро. Нет API, нет экрана импорта, просто CSV и таблица MySQL ожидая его. Я сделал то, что все делают в первый раз: я открыл файл, написал пару операторов INSERT от руки, чтобы получить форму правильно, и начал копировать значения в. Три строки спустя название продукта с апострофом в нем, O'Brien's Tools, сломал весь оператор, потому что одиночная цитата закрыла строку рано, а MySQL захлебнулся на остальных. Это момент, когда вы понимаете, что написание SQL от руки из электронной таблицы - это не пятиминутная работа, это цитирование минное поле с 1800 шансами сделать шаг не так. Итак, я построил Преобразователь CSV в SQL, и это руководство является причиной каждого варианта.

TL;DR: Преобразователь CSV в SQL считывает файл CSV и генерирует CREATE TABLE а INSERT операторы база данных должна хранить его Инструмент Toolz анализирует файл с конечным автоматом RFC 4180, делает вывод, является ли каждый столбец целым числом, десятичным, логическим или текстом, цитирует идентификаторы для MySQL, PostgreSQL или SQLite, правильно ускользает от каждого значения и превращает пустые ячейки в NULL. Вставьте CSV, выберите диалект и скопируйте сценарий, от которого вы можете сразу же бежать. Все работает на стороне клиента: нет загрузки, нет регистрации, работает в автономном режиме.

Я создаю продукты SaaS на Laravel и React и отправляю плагины WordPress, поэтому загрузка табличных данных в базу данных - это еженедельная работа, а не разовая. иногда это клиентский экспорт, иногда начальный файл для свежей среды, иногда быстрое приспособление для теста. CSV всегда является форматом, в котором он поступает, и SQL всегда находится там, где ему нужно оказаться. Переход от одного к другому вручную - это именно тот вид повторяющейся, подверженной ошибкам работы, который должен стереть инструмент браузера, поэтому это руководство, которое я хотел бы иметь, когда этот апостроф нарушил мой импорт.

Что такое преобразователь CSV в SQL?

Преобразователь CSV в SQL принимает строки значений, разделенных запятыми, и создает операторы SQL, которые создают таблицу и загружают в нее данные. Сам CSV стандартизирован лишь в незначительной степени - RFC 4180 описывает общую форму и ясно, что она документирует существующую практику, а не диктует ее. Он читает первую строку как имена столбцов, рассматривает каждую следующую строку как запись и выделяет две вещи: a CREATE TABLE оператор, определяющий столбцы с понятными типами и набором INSERT операторы, несущие значения. вывод представляет собой простой скрипт SQL, который вы можете вставить в клиент базы данных, такой как TablePlus или DBeaver, перейти в миграцию или ввести в канал mysql, psql, или sqlite3 командная строка для построения и заполнения таблицы за один шаг.

Причина, по которой категория существует, заключается в том, что перевод скрипки способами, которые легко недооценить. каждое значение текста должно быть обернуто в единичные кавычки.Каждая отдельная цитата внутри значения должна быть удвоена, чтобы она не завершала строку. Числа и логические значения должны быть оставлены без кавычек, иначе база данных будет хранить их как текст и ваш WHERE price > 100 будет вести себя странно. пустые клетки обычно должны стать NULL вместо пустой строки. имена столбцов с пробелами или зарезервированными словами должны быть процитированы правильным символом для вашей базы данных. Пропустите любой из них в нескольких тысячах строк, и вся партия потерпит неудачу, часто с ошибкой, которая указывает на неправильную строку. конвертер каждый раз применяет все эти правила одинаково, что является всем ценностным предложением.

Как инструмент определяет тип каждого столбца и #39;s?

Это та часть, которая отделяет полезный конвертер от немого Наивный инструмент делает каждый столбец TEXT и называет это сделано, что технически работает, но дает вам таблицу, где числа сортируются как строки, и вы не можете делать арифметику без литья. Инструмент Инструмент CSV-SQL вместо этого сканирует каждое значение в столбце и выбирает самый узкий тип, подходящий для всех.

Логика проста, как только вы ее видите Если каждое непустое значение в столбце является целым числом, столбец становится целым типом Если каждое значение числовое, но некоторые имеют десятичные точки, оно становится плавающим типом Если каждое значение является словом true или false, это становится логическим. все остальное, включая смешанное содержание и все, что связано с буквами, возвращается к тексту. Есть одно преднамеренное исключение, которое хорошо ловит людей: значение, подобное 007 или 00123 остается текстом, а не целым числом, потому что начальный ноль почти всегда означает, что столбец - это идентификатор, почтовый индекс или номер телефона, где ноль значим и будет потерян, если будет сохранен как число. Это единственное правило спасло меня от бесшумного повреждения более чем одного столбца 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 отклонит идентификаторы с двойными кавычками PostgreSQL хочет, если вы не находитесь в режиме ANSI, а PostgreSQL не имеет TINYINT. SQLite вообще не имеет реального логического типа, поэтому логические значения становятся целыми числами, поэтому инструмент пишет 1 а 0 как для MySQL, так и для SQLite, но TRUE а FALSE для PostgreSQL и стандартного SQL. Если вы не уверены или пишете что-то портативное, стандартный SQL является наиболее консервативным выбором. как только операторы сгенерированы, вы всегда можете запустить их через Форматтер SQL чтобы красиво напечатать выходные данные перед их переходом в файл миграции.

Как конвертировать CSV в SQL с помощью этого инструмента?

Поток намеренно короткий. вставьте свой CSV в поле ввода или нажмите "Загрузить образец", чтобы увидеть обработанный пример с идентификатором, именем, ролью, логическим значением и столбцом заработной платы.

Установите имя таблицы в любое, что целевая таблица должна быть вызвана, и выберите свой SQL диалект. Затем определитесь с параметрами вывода.Оставить & quot;Включить CREATE TABLE& quot; на если таблица еще не существует, или выключите ее, чтобы сгенерировать только INSERT операторы, когда вы загружаете в таблицу, которая уже есть.Keep & quot;Infer column types& quot; on for the typed table, или выключить, чтобы сделать все текстовым.Выберите & quot;Multi-row INSERT& quot; for one compact stator with many value tuples, который загружается быстрее всего, или выключите его, чтобы получить один INSERT для каждой строки, которая более удобна для контроля версий и позволяет запускать строки индивидуально. оставьте & quot;Пустые ячейки как NULL" на, если только вы специально не хотите, чтобы были сохранены пустые строки.

Разделитель можно автоматически обнаружить или установить вручную Авто-обнаружение оценки запятая, точка с запятой, вкладка и трубка по тому, насколько последовательно каждая из них разделяет первые несколько строк на одинаковое количество столбцов, что правильно обрабатывает европейские файлы с запятой и разделенные вкладками экспортные товары. Нажмите Конвертировать в SQL и вывод отображается с подсчетом строк и столбцов, плюс любые предупреждения о дублировании названий столбцов или рваных строках. Скопируйте его и запустите. Если экспорт поступил без строки заголовка, выключите "Первая строка - заголовок и цитата; и инструмент называет столбцы column_1, column_2, и так далее, затем рассматривает каждую строку как данные.

Почему правильное ускользание ценностей так важно?

Поскольку неправильный выход - это не просто ошибка, это класс уязвимости безопасности. апостроф, который вручную нарушил мой первый импорт, - это тот же механизм, что и внедрение SQL: одна цитата внутри значения, если она не экранирована, рано завершает строку и позволяет интерпретировать все, что следует за ней, как SQL. В приложении вы решаете это с помощью параметризованных запросов, где драйвер базы данных сохраняет значения и код строго отдельными. когда вы генерируете статический сценарий SQL из CSV, у вас нет такого разделения, поэтому выход должен быть правильным в самом сгенерированном тексте.

Инструмент следует правилу ANSI SQL: текстовое значение оборачивается в одиночные кавычки, и любая отдельная цитата внутри значения удваивается. так O'Brien's Tools становиться 'O''Brien''s Tools', который каждая из поддерживаемых баз данных читает обратно как исходную строку. Числа и логические значения излучаются без кавычек, поэтому они сохраняются как правильный тип, а идентификаторы цитируются с помощью диалекта и собственного символа #39;s, поэтому называется столбец order или select не сталкивается с зарезервированным словом. это именно та утомительная корректность, что люди ошибаются в условиях нехватки времени, и инструмент каждый раз становится правильным, в чем весь смысл его автоматизации. Если вам интересно, как побег сравнивается с форматами, Преобразователь CSV в JSON сталкивается с той же проблемой со стороны JSON, где escape-символ представляет собой обратную косую черту, а не удвоенную кавычку.

Безопасно ли конвертировать конфиденциальные данные CSV в Интернете?

Для этого инструмента, да, и причина является архитектурным, а не обещание на странице. каждый шаг, анализируя CSV, вывод типов столбцов, избегая значений, и построения операторов, запускается как JavaScript внутри вашей собственной вкладки браузера. нет никакой загрузки, ни сервера туда и обратно, и ничего не регистрируется или не сохраняется. Вы можете доказать это, открыв свой браузер и #39;s вкладка сети и нажав Конвертировать: ни один запрос не покидает страницу. После загрузки страницы вы можете полностью отключиться от Интернета, и он продолжает генерировать SQL.

Это имеет значение, потому что CSV люди конвертируют в SQL часто являются наиболее конфиденциальными файлами у бизнеса. таблицы клиентов, истории заказов, записи пользователей и ценовые данные все путешествуют как CSV, прежде чем они приземляются в базе данных. конвертер, который загружает ваш файл на сервер, как бы хорошо это ни было, превращает частную электронную таблицу в кого-то другого. & # 39;s запись в журнале. обработка на стороне клиента полностью удаляет вопрос: данные никогда не покидают машину, на которой они были запущены. Это по умолчанию преднамеренно для каждого инструмента на Toolz.dev, и я записал более длинный аргумент в руководство по конфиденциальности данных для онлайн-инструментов для всех, кто хочет полного рассуждения.

Где CSV-SQL вписывается в реальный рабочий процесс?

Преобразование редко является всей работой, это одна станция в конвейере. шаблон, который я чаще всего нажимаю, это посев: клиент отправляет электронную таблицу, я преобразую ее в сценарий SQL, и я запускаю этот сценарий, чтобы заполнить базу данных этапа или разработки, чтобы приложение имело реалистичные данные для работы против. Поскольку вывод представляет собой простой сценарий, а не живое соединение, его легко проверить в репо в качестве начального файла, просмотреть в запросе на извлечение и повторно запустить в любой среде.

Инструменты вокруг него зависят от того, что данные будут делать дальше. Если мне нужно просмотреть файл как сортируемую сетку перед преобразованием, Просмотрщик 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, йо-

Как он определяет типы столбцов? Каждый столбец сверяется с каждым значением в данных Если все непустые значения являются целыми числами столбец становится целым типом, все числа становятся десятичным типом, все истинные или ложные значения становятся логическим типом, а все остальное становится текстом. значение с ведущим нулем, например 007 остается текстом, потому что это обычно значимый идентификатор. вы можете отключить вывод, чтобы сделать каждый текст столбца.

Создает ли он таблицу или только вставки? Оба по умолчанию. Он излучает a CREATE TABLE оператор с выведенными типами столбцов, за которыми следует INSERT заявления. Вы можете повернуть CREATE TABLE выключено, когда таблица уже существует и вам нужно только загрузить строки.

Как обрабатываются цитаты и специальные персонажи? Значения текста обернуты в отдельные кавычки, и любая отдельная кавычка внутри значения удваивается, что является стандартным экранированием SQL, так что O'Brien становиться 'O''Brien'. Числа и логические значения остаются без кавычек, а идентификаторы цитируются с обратными кавычками для MySQL или двойными кавычками для других диалектов, поэтому слово зарезервировано order не нарушает утверждение.

Что происходит с пустыми ячейками? По умолчанию становится пустая ячейка NULL, что обычно является тем, что вы хотите для отсутствующего значения. если вы предпочитаете вместо этого хранить пустую строку, выключите опцию пустой как НУЛЬ и пустые ячейки станут двумя одиночными кавычками.

Могу ли я преобразовать CSV, в котором нет строки заголовка? Да. Выключите опцию заголовка и инструмент называет столбцы column_1, column_2, и так далее, то рассматривает каждую строку как данные Это полезно для экспорта сырья, которое отправляется без заголовка.

Загружаются ли мои файлы CSV куда-нибудь? Нет. парсинг и генерация SQL работают как JavaScript в вашем браузере. Ничего не передается, не регистрируется и не сохраняется, и инструмент продолжает работать в автономном режиме после загрузки, что вы можете подтвердить, просматривая вкладку сети во время конвертации.