Некорректный формат cvs файла количество столбцов не постоянно
CSV является стандартом де-факто для связи между собой разнородных систем, для передачи и обработки объемных данных с «жесткой», табличной структурой. Во многих скриптовых языках программирования есть встроенные средства разбора и генерации, он хорошо понятен как программистам, так и рядовым пользователям, а проблемы с самими данными в нем хорошо обнаруживаются, как говорится, на глаз.
История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.
В этой статье я попробую обобщить существующие знания об этом формате, указать на типичные ошибки, а также проиллюстрировать описанные проблемы на примере кривой реализации импорта-экспорта в Microsoft Office 2007. Также покажу, как обходить эти проблемы (в т.ч. автоматическое преобразование типов Excel-ом в DATETIME и NUMBER) при открытии .csv.
Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.
Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.
В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.
Создадим в Excel тестовую табличку:
… и попробуем экспортировать ее в три текстовых формата:
«Текст Unicode» | Кодировка — UTF-16, разделители — табуляция, переводы строк — 0×0D, 0×0A, объем файла — 222 байт |
«CSV (разделители — запятые)» | Кодировка — Windows-1251, разделители — точка с запятой (не запятая!), во второй строке значение телефонов не взято в кавычки, несмотря на запятую, зато взято в кавычки значение «01;02», что правильно. Переводы строк — 0×0D, 0×0A. Объем файла — 110 байт |
«Текстовые файлы (с разделителями табуляции)» | Кодировка — Windows-1251, разделители — табуляция, переводы строк — 0×0D, 0×0A. Значение «01;02» помещено в кавычки (без особой нужды). Объем файла — 110 байт |
Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.
Теперь посмотрим, как на это смотрит Outlook. Попробуем импортировать эти файлы из него, указав такие же источники данных. Outlook 2007: Файл -> Импорт и экспорт… -> Импорт из другой программы или файла. Далее выбираем формат данных: «Значения, разделенные запятыми (Windows)» и «Значения, разделенные табуляцией (Windows)».
«Значения, разделенные табуляцией(Windows)» | Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец. |
«Значения, разделенные запятыми (Windows)» | А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно. |
Два майкрософтовских продукта не понимают друг друга, у них напрочь отсутствует возможность передать через текстовый файл структурированные данные. Для того, чтобы все заработало, требуются «пляски с бубном» программиста.
Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.
Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.
Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.
Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:
- между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
- разделители — запятые, в конце строки не должно быть запятой,
- в последней строке CRLF не обязателен,
- первая строка может быть строкой заголовка (никак не помечается при этом)
- пробелы, окружающие запятую-разделитель, игнорируются.
- если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
- т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
- если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.
Вот в нотации ABNF описание формата:
Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:
- строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
- количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
- Квотить кавычки через «слэш» не по стандарту, делать так не надо.
- Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
- Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
- Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
- У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.
Пример валидного CSV, который можно использовать для тестов:
точно такой же SCSV:
Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.
Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:
- Учлись пробелы, окружающие разделители
- Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
- В поле индекс Excel «опустил» ведущие нули.
- в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол
Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:
С приведением типов сработало, но зато теперь не обрабатываются нормально переводы строк и осталась проблема с ведущими нулями, кавычками и лишними пробелами. Да и пользователям так открывать CSV крайне неудобно.
Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
то, что я пытаюсь сделать, это зарегистрировать пользователя в моей базе данных, поэтому сохранение формы ввода в мой users_tb.
получаю эту ошибку, когда я пытаюсь вставить значения в виде:
"количество столбцов не совпадает количество значений в строке 1"
Я думал, что это потому, что я не вставлял значение user_id( которое является автоматическим приращением), поэтому я попытался вставить " в мой запрос для user_id, но все равно не повезло.
ли это поможет. Если вам нужен другой код, просто скажите.
просто чтобы убедиться, что вставки не должны быть в том же порядке, что и поля в таблице, не так ли?
у вас отсутствует одно значение.
для запросов такой длины с таким количеством столбцов (и если вы вставляете только одну строку), я бы предложил использовать следующий синтаксис вставки, который намного легче читать и менее вероятно вызвать проблемы.
вам не хватает значения для user_email .
и просто напоминание: вы должны защитить ценности, используя mysql_real_escape_string если вы еще не делаете этого.
Марк Байерс ответил на проблемную часть, но не ответил на этот вопрос:
просто чтобы убедиться, что вставки не должны быть в том же порядке, что и поля в таблице, не так ли?
нет, они не должны быть в том же порядке, что и столбцы в таблице, но список столбцов и список значений должны совпадать как по количеству, так и по типу данных. Проблема у вас одна марка пятнистый; вы упускаете значение user_email , Что означает the dob value пытается войти в этот столбец вместо этого. MySQL видит, что недостаточно значений для столбцов, которые вы перечислили, и сообщает вам об ошибке.
вы можете использовать эту простую функцию для создания запроса из массива $_POST и списка разрешенных полей:
конечно, предполагается, что имена полей HTML-формы соответствуют именам полей таблицы SQL, что очень удобно рассматривать
У вас есть 10 полей, которые вы хотите вставить, но вы предоставляете только 9 значений
Я пытаюсь получить ".csv " файл в базу данных SQL с phpMyAdmin. Однако всякий раз, когда я импортирую его, я получаю ошибку: недопустимое количество столбцов во входных данных CSV в строке 1. Я провел весь день, играя вокруг с различными вариантами, чтобы попытаться заставить его работать, но безрезультатно. В моем ровно 47 столбцов .CSV-файл. Я создал 47 столбцов в моей таблице SQL. Однако имена не совсем совпадают с именами в файле. Когда я импортирую, он продолжает давать мне эту ошибку. Любая помощь был бы очень признателен! ~ Carpetfizz Одна вещь, которую я думал, может вызвать проблему, заключалась в том, что первый столбец ничего не назван в моем документе excel. Может ли это вызвать проблему?
EDIT 12: 30AM: phpMyAdmin уже последняя версия доступна, через (apt-get install phpmyadmin) (phpmyadmin уже последняя версия)
здесь - это .CSV-файл, если это поможет.
Если ваша таблица БД уже существует, и вы не хотите включать все столбцы таблицы в свой CSV-файл, то при запуске PHP Admin Import вам нужно будет заполнить поле имена столбцов в параметрах формата для CSV, показанных здесь внизу следующего скриншота.
- Выберите файл CSV
- установите формат в CSV
- заполните поле имена столбцов именами столбцов в вашем CSV
- если ваш CSV-файл имеет имена столбцов, перечисленные в строке 1, Установите "пропустить это количество запросов (для SQL) или строк (для других форматов), начиная с первого" до 1
исправлено! Я в основном просто выбрал "импорт", даже не делая таблицу сам. phpMyAdmin создал для меня таблицу со всеми правильными именами столбцов из исходного документа.
Я получил ту же ошибку при импорте .csv-файл с использованием phpMyAdmin.
решением моей проблемы было то, что мой компьютер сохранил .csv-файл с; (с запятой) в качестве разделителя вместо, (запятые).
в параметрах формата вы можете выбрать "столбцы разделены:" и выбрать ; вместо, (запятая).
чтобы посмотреть, в чем ваш компьютер хранит файл, откройте .CSV-файл в текстовом редакторе.
имел ту же проблему и сделал два изменения: (a) не переписал существующие данные (не идеально, если это ваше намерение, но вы можете запустить запрос на удаление заранее), и (b) подсчитал столбцы и обнаружил, что csv имеет пустой столбец, поэтому всегда платит, чтобы вернуться к исходной работе, даже если все "кажется" правильным.
вам нужно будет пропустить первую строку (где определены имена столбцов), и вам нужно будет проверить, какой "символ" разделяет ячейки (обычно это , , но в моем случае был ; )
Ниже приведена картина моего импорта:
ваше решение, похоже, предполагает, что вы хотите создать совершенно новую таблицу.
однако, если вы хотите добавить содержимое в уже существующую таблицу, найдите структуру таблицы и обратите внимание на количество столбцов (столбец id, если у вас есть один все еще подсчитывает ->, даже если это может быть автоматическое приращение/уникальный)
Итак, если таблица выглядит так id имя возраст секс
убедитесь, что ваша таблица excel выглядит так A1 id B1 имя C1 возраст D1 пол
и теперь они оба имеют 4 колонки.
также прямо под частичным импортом, рядом с пропуском количества запросов. увеличьте число, чтобы пропустить соответствующую строку. выбор 1 автоматически пропустит первую строку. Для тех, у кого могут быть заголовки в файлах excel
Если таблица уже была создана, и Вы были достаточно ленивы, чтобы не указывать столбцы в полях ввода имен, то все, что вам нужно сделать, это выбрать пустые столбцы справа от содержимого файла и удалить их.
У меня была аналогичная проблема с phpmyAdmin. Количество столбцов в импортируемом файле соответствует столбцам в целевой таблице базы данных. Я попытался импортировать файлы в обоих .csv и .формат ods безрезультатно, получая множество ошибок, включая один аргумент, что количество столбцов было неправильным.
Как .csv и .файлы ods были созданы с помощью LibreOffice 5.204. Основываясь на небольшом опыте с проблемами импорта в прошлые годы, я решил переделать файлы с помощью gnumeric электронная таблица, экспорт .СОД в соответствии со стандартом "строгого" формата. Вуаля! Больше нет проблем с импортом. Хотя у меня не было времени исследовать проблему дальше, я подозреваю, что что-то изменилось во внутренней структуре вывода файла LibreOffice.
при возникновении ошибок с входными файлами любого типа,проблемы с кодировкой общие.
простым решением может быть открытие нового файла, копирование вставки в него текста CSV, а затем сохранение его как нового файла.
последний столбец моей базы данных (это столбец F в электронной таблице) не используется и поэтому пуст. Когда я импортировал файл CSV excel, я получил ошибку "количество столбцов".
Это потому, что excel сохранял только столбцы, которые я использую. А-Е
добавление 0 в первую строку в F решило проблему, затем я удалил ее после успешной загрузки.
надеюсь, это поможет и сэкономит кому-то еще время и потерю волос:)
самая тупая вещь, которая когда-либо исправит эту ошибку в Microsoft Excel (при условии, что у вас действительно есть все остальное):
выберите данные и нажмите "границы все" в Excel (ставит визуальные границы вокруг ваших данных) перед сохранением CSV. Звучит бессмысленно? Я полностью согласен! Однако он исправит эту ошибку. Я использую этот трюк, по крайней мере, три раза в неделю.
забавная вещь, когда я хочу сохранить столбцы excel с длинными числами, как показано ниже, в результате csv содержит преобразованные числа в научную нотацию, которая непригодна для использования. Я хочу, чтобы они были сохранены как текст. Есть какой-нибудь трюк?
28160010390002003505456159
12160010390002003505456156
39160010390002003505456155
39160010390002003505456155
чтобы иметь эти длинные (>15-значные) числа в Excel, они уже отформатированы как текст. Я подозреваю, что .csv-файл также показывает их как длинные числа (если вы открываете csv-файл с помощью блокнота), но когда вы открываете csv-файл в Excel, вы видите их как усеченные и преобразованные в научную нотацию.
Если это так, то вам нужно импортировать файл csv. При этом откроется мастер преобразования текста в столбцы, который позволит отформатировать этот столбец как текст. Расположение импорта отличается в разных версиях. В Excel 2007 он находится на ленте данных, получает внешние данные / из текста.
Добавить вкладку char (ASCII 9) в конце числа.
поместите число как функцию, как показано ниже: ="123456789123456789"
Это старый вопрос, но поскольку на данный момент он по-прежнему является лучшим результатом поиска google по теме, я думаю, что поток должен быть текущим. Хусейн mahyoub, единственный реальный ответ на вопрос, но не получил голосов.
ответ, который говорит нам добавить символ вкладки после вашего номера получает строку текста с вкладкой после него внутри excel. Это выглядит визуально правильным, но, это не правильно внутри электронной таблицы. Если намерение чтобы использовать данные в excel в качестве данных excel и использовать формулу и т. д., Это может вызвать проблемы. Интересно, что если вы поместите вкладку перед текстом, она появится в данных после текста.
ответ, который говорит нам о вставке "до того, как текст получит" в файле excel. Это визуально неверное представление данных.
ответ, который хвастается достоинством openoffice, - это просто злой тролль, который даже не пытается ответить на вопрос.
в ответ, который объясняет, как импортировать CSV, который не был правильно отформатирован, - это хорошая информация, но, вне темы.
ИЗМЕНИТЬ 12:30: phpMyAdmin уже является последней доступной версией через (apt-get install phpmyadmin) (phpmyadmin уже является последней версией)
Здесь находится файл .csv, если это поможет.
Зафиксированный! Я просто выбрал «Импорт», даже не создав сам таблицу. phpMyAdmin создал для меня таблицу со всеми правильными именами столбцов из исходного документа.
Я также столкнулся с этой проблемой в phpMyAdmin. Использование LOAD_DATA вместо LOAD решило проблему .
С id в качестве первичного ключа AUTO_INCREMENT. Записи были такие:
Кодировка таблицы была utf8_general_ci , а файл был закодирован в utf-8 .
ЗАГРУЗИТЬ также не удалось установить вручную значение для столбца id .
Самая глупая вещь, которая исправит эту ошибку в Microsoft Excel (при условии, что все остальное у вас правильно):
Выберите данные и нажмите «Границы всех» в Excel (визуальные границы вокруг данных) перед сохранением CSV. Звучит бессмысленно? Я полностью согласен! Однако это исправит эту ошибку. Я использую этот трюк не реже трех раз в неделю.
Последний столбец моей базы данных (это столбец F в электронной таблице) не используется и поэтому пуст. Когда я импортировал CSV-файл Excel, я получил ошибку «счетчик столбцов».
Это потому, что Excel сохранял только те столбцы, которые я использую. A-E
Добавление 0 к первой строке в F решило проблему, затем я удалил ее после успешной загрузки.
Надеюсь, это поможет и сэкономит кому-то время и избавит от выпадения волос :)
У меня была аналогичная проблема с phpmyAdmin. Количество столбцов в импортируемом файле соответствует столбцам в таблице целевой базы данных. Я безуспешно пытался импортировать файлы в формате .csv и .ods, получая множество ошибок, в том числе одну, утверждающую, что количество столбцов было неправильным.
Файлы .csv и .ods были созданы с помощью LibreOffice 5.204. Основываясь на небольшом опыте решения проблем с импортом в прошлые годы, я решил переделать файлы с помощью электронной таблицы gnumeric, экспортируя .ods в соответствии со стандартом «строгого» формата. Вуаля! Больше никаких проблем с импортом. Хотя у меня не было времени на дальнейшее изучение проблемы, я подозреваю, что что-то изменилось во внутренней структуре файлового вывода LibreOffice.
Кажется, ваше решение предполагает, что вы хотите создать совершенно новую таблицу.
Однако, если вы хотите добавить контент в уже существующую таблицу, найдите структуру таблицы и обратите внимание на количество столбцов (столбец id, если он у вас есть, все еще считается->, даже если он может быть автоматически увеличивающимся / уникальным)
Итак, если ваша таблица выглядит так: id Имя Возраст Пол
Убедитесь, что ваша таблица Excel выглядит как A1 id B1 Имя C1 Возраст D1 Пол
И теперь у них обоих по 4 столбца.
Также прямо под частичным импортом, рядом с пропустить количество запросов . увеличьте число, чтобы пропустить соответствующую строку. выбор 1 автоматически пропустит первую строку. Для тех, у кого могут быть заголовки в файлах Excel
Если таблица уже была создана, и вы были достаточно ленивы, чтобы не указывать столбцы во вводе имен полей, то все, что вам нужно сделать, это выбрать пустые столбцы справа от содержимого файла и удалить их.
Была та же проблема и внесены два изменения: (а) не перезаписывал существующие данные (не идеально, если это ваше намерение, но вы можете заранее выполнить запрос на удаление), и (б) подсчитал столбцы и обнаружил, что csv имеет пустой столбец, поэтому всегда стоит вернуться к исходной работе, даже если все «кажется» выглядит правильным.
Вам нужно будет пропустить первую строку (где определены имена столбцов), и вам нужно будет проверить, какой «символ» разделяет ячейки (обычно это , , но в моем случае был ; )
Ниже приведена фотография моего импорта:
При обнаружении ошибок с входными файлами любого типа обычно возникают проблемы с кодировкой .
Простое решение может заключаться в том, чтобы открыть новый файл, скопировать, вставив в него текст CSV, а затем сохранить его как новый файл.
Что вы хотите сделать, так это изменить "Поля, заканчивающиеся на" с ";" на "," и затем убедитесь, что выбрано "Использовать ЛОКАЛЬНОЕ ключевое слово".
У меня такая же ошибка при импорте файла .csv с помощью phpMyAdmin.
Решение моей проблемы заключалось в том, что мой компьютер сохранил файл .csv с; (точка с запятой) в качестве разделителя вместо (запятые).
Однако в параметрах, связанных с форматом, вы можете выбрать «разделенные столбцы:» и выбрать; вместо (запятая).
Чтобы увидеть, в чем ваш компьютер хранит файл, откройте файл .csv в текстовом редакторе.
Если ваша таблица БД уже существует, и вы НЕ хотите включать все столбцы таблицы в свой CSV-файл, тогда, когда вы запустите PHP Admin Import, вам нужно будет заполнить поле «Имена столбцов» в параметрах, зависящих от формата для CSV - показано здесь, внизу следующего снимка экрана.
Читайте также: