Excel CSV. файл с более чем 1 048 576 строками данных

Мне дали файл CSV с большим количеством файлов, чем может обработать MAX Excel, и мне действительно нужно иметь возможность видеть все данные. Я понимаю и пробовал метод «splitting» it, но он не работает.

Некоторая предыстория: файл CSV — это файл Excel CSV, и человек, который дал файл, сказал, что есть около 2m строк данных.

Когда я импортирую его в Excel, я получаю данные до строки 1,048,576 , а затем повторно импортирую их в новую вкладку, начиная со строки 1,048,577 в данных, но это дает мне только одну строку, и я точно знаю, что их должно быть больше (не только из-за того, что «the person» сказал, что их больше 2 миллионов, но и из-за информации в последних нескольких наборах строк).)

Я подумал, что, возможно, причина этого заключается в том, что мне предоставили файл CSV как файл Excel CSV, и поэтому вся информация, прошедшая 1,048,576 , потеряна (?).

Нужно ли мне запрашивать файл в формате базы данных SQL?

14 ответов

Исправьте файл CSV с новыми строками

Я запустил запрос к базе данных MS SQL с помощью SQL Server Management Studio, и некоторые поля содержали новые строки. Я решил сохранить результат как csv, и, по-видимому, MS SQL недостаточно умна, чтобы дать мне правильно отформатированный файл CSV. Некоторые из этих полей с новыми строками.

Я вижу, что количество строк на листе ограничено 1 048 576. Это просто excel вещь? Например, могу ли я создать файл csv, который имеет больше строк, скажем, 5 миллионов строк? Я понимаю, что не могу открыть его с помощью excel, но могу ли я все еще иметь файл и получить к нему доступ каким-то.

Вы должны попробовать разграничить , что он может открыть до 2 миллиардов строк и 2 миллионов столбцов очень быстро, а также бесплатную 15-дневную пробную версию. Делает эту работу за меня!

Я бы предложил загрузить файл .CSV в MS-Access.

С помощью MS-Excel вы можете затем создать соединение для передачи данных с этим источником (без фактической загрузки записей на листе) и создать подключенную таблицу pivot. Тогда у вас может быть практически неограниченное количество строк в таблице (в зависимости от процессора и памяти: у меня сейчас 15 миллионов строк с 3 Гб памяти).

Дополнительным преимуществом является то, что теперь вы можете создать агрегированное представление в MS-Access. Таким образом, вы можете создавать обзоры из сотен миллионов строк, а затем просматривать их в MS-Excel (остерегайтесь ограничения на 2 Гб файлов NTFS в 32 битах OS).

Вы можете использовать PowerPivot для работы с файлами объемом до 2 ГБ, чего будет достаточно для ваших нужд.

Excel 2007+ ограничен примерно 1 миллионом строк ( 2^20, если быть точным), поэтому он никогда не загрузит ваш файл строки 2M. Я думаю, что техника, которую вы называете расщеплением,-это встроенная вещь Excel, но afaik, которая работает только для проблем ширины , а не для проблем длины .

Самый простой способ, который я сразу вижу, — это использовать какой-нибудь инструмент для разделения файлов -их много , и использовать его для загрузки полученных частичных файлов csv в несколько листов.

ps: «excel csv files» не существует, есть только файлы, созданные Excel, которые используют один из форматов, обычно называемых файлами csv.

Сначала вы хотите изменить формат файла с csv на txt. Это просто сделать, просто отредактируйте имя файла и измените csv на txt. (Windows предупредит вас о возможном повреждении данных, но это нормально, просто нажмите кнопку ОК). Затем сделайте копию файла txt, чтобы теперь у вас было два файла с 2 миллионами строк данных. Затем откройте первый файл txt, удалите второй миллион строк и сохраните файл. Затем откройте второй файл txt, удалите первый миллион строк и сохраните файл. Теперь измените два файла обратно на csv так же, как вы изначально изменили их на txt.

Если у вас есть Matlab, вы можете открыть большие файлы CSV (или TXT) с помощью средства импорта. Инструмент предоставляет вам различные параметры формата импорта, включая таблицы, векторы столбцов, числовую матрицу и т. Д. Однако, поскольку Matlab является пакетом интерпретатора, для импорта такого большого файла требуется собственное время, и я смог импортировать файл с более чем 2 миллионами строк примерно за 10 минут.

Инструмент доступен на вкладке «Главная» Matlab, нажав на кнопку «Import Data». Пример загрузки большого файла показан ниже: После импорта данные отображаются в рабочей области right-hand-side, которую затем можно дважды щелкнуть в формате Excel и даже отобразить в разных форматах.

Преобразование текстовых файлов в Microsoft Excel, которые могут вместить более 65536 строк

Я нашел код Python (как показано ниже) для преобразования текстовых файлов в Microsoft Excel, и он работает. Однако 65 536-это максимальное количество строк электронных таблиц, поддерживаемых Excel 97, Excel 2000, Excel 2002 и Excel 2003, тогда как Excel 2007, 2010 и 2013 поддерживают 1 048 576.

Как вы знаете, Excel имеет 1048574 строки. Как я могу экспортировать более миллиона строк ниже моего кода ? если достигнуто максимальное количество строк, то как я могу продолжить работу над новым листом в цикле ? как его адаптировать? Я гуглю некоторые из них после того, как нахожу, что nice.

Я удивлен, что никто не упомянул Microsoft Query . Вы можете просто запросить данные из большого файла CSV по мере необходимости, запросив только то, что вам нужно. (Запрос настраивается так же, как фильтрация таблицы в Excel)

Еще лучше, если вы готовы установить надстройку Power Query, это очень просто и быстро. Примечание: Power Query-это надстройка для 2010 и 2013 годов, но поставляется с 2016 годом.

Попробуйте PowerPivot от Microsoft. Здесь вы можете найти пошаговое руководство. Это сработало для моих 4М + рядов!

«DO I need to ask for a file in an SQL database format?» YES.

Использование базы данных-лучший вариант для решения этой проблемы.

Excel 2010 технические характеристики .

Используйте MS Access. У меня есть файл с 2 673 404 записями. Он не откроется в notepad++, и excel не загрузит более 1 048 576 записей. Он разделен табуляцией, так как я экспортировал данные из базы данных mysql, и мне это нужно в формате csv. Поэтому я импортировал его в Access. Измените расширение файла на .txt, чтобы MS Access провел вас через мастер импорта.

MS Access будет ссылаться на ваш файл, поэтому, чтобы база данных оставалась нетронутой, сохраните файл csv

Мне удалось без проблем отредактировать большой файл размером 17 ГБ csv в Sublime Text (нумерация строк значительно облегчает отслеживание ручного разделения), а затем сбросить его в Excel кусками размером менее 1 048 576 строк. Просто и довольно быстро — менее сложно, чем исследовать, устанавливать и изучать индивидуальные решения. Быстро и грязно, но это работает.

Читать еще: iTunes: ошибка 9

Я обнаружил, что этот предмет исследуется. Существует способ скопировать все эти данные в таблицу Excel. (У меня уже была эта проблема с файлом 50 миллионов строк CSV) Если есть какой-либо формат, можно включить дополнительный код. Попробуй это.

Лучший способ справиться с этим (легко и без дополнительного программного обеспечения) — использовать Excel-но с помощью Powerpivot (в который встроен MSFT Power Query). Просто создайте новую модель данных Power Pivot, которая присоединится к вашему большому csv или текстовому файлу. Затем вы сможете импортировать многомиллионные строки в память с помощью встроенного механизма сжатия X-Velocity (in-memory compression). Предел Excel листа неприменим — так как движок X-Velocity помещает все в RAM в сжатом виде. Я загрузил 15 миллионов строк и отфильтровал по желанию, используя эту технику. Надеюсь, это кому-то поможет. — Джейси

Split CSV в два файла в Блокноте. Это боль, но после этого вы можете просто отредактировать каждый из них по отдельности в Excel.

Самый простой способ открыть CSV с запятыми в Excel

Файлы CSV автоматически связываются с Excel, но когда я открываю их, все строки в основном находятся в первом столбце, например:

Это, вероятно, потому, что когда Excel считает «значения, разделенные запятыми», он на самом деле ищет какой-то другой разделитель (я думаю, что это точка с запятой, но это не важно).

Теперь, когда я уже открыл этот файл в Excel, есть ли кнопка или что-то, чтобы сказать ему «открыть этот файл и использовать запятую в качестве разделителя»?

Я знаю, что могу импортировать данные в новый лист и т. Д., Но я специально обращаюсь за помощью в ситуации, когда у меня уже есть файл CSV с запятыми в нем, и я хочу открыть его в Excel, не создавая новую книгу или не преобразовывая исходный файл.

Перейдите на вкладку « Данные » и выберите текст для столбцов в разделе « Инструменты данных», затем выберите « Разграничить» и нажмите « Далее» . Затем выберите запятую в качестве разделителя и нажмите » Готово»

в качестве первой строки вашего CSV-файла будет автоматически указана Excel использовать этот символ в качестве разделителя вместо запятой по умолчанию.

приведет к тому, что труба (|) будет использоваться в качестве разделителя вместо запятой, без необходимости пользователю сначала запускать мастер импорта.

Я не знаю, удалось ли вам решить эту проблему, но у меня тоже была такая же проблема, и я разобрался с ней после некоторых расследований.

Оказывается, это была проблема региональных условий;

Зайдите в панель управления -> Региональные настройки -> Расширенные настройки и измените разделитель списка на запятую .

У меня была установлена точка с запятой для Южной Африки после того, как я перешел на Windows 8. После того, как я изменил его на запятую, все мои CSV правильно открываются в Excel 2013.

Надеюсь это поможет.

Дополнительный комментарий:
я использовал те же шаги, что и Lèse majesté, но я также изменил символ десятичной дроби с запятой (,) на точку с запятой (.), И это решило мою проблему.

Это связано с тем, что по умолчанию Windows 8 использует запятую в качестве десятичного знака, а Excel запутывается, когда ему приходится использовать оба символа в качестве разделителя и десятичного символа.

Это то, что сработало для меня — это сочетание некоторых ответов здесь.

(Примечание. Мой компьютер использует настройки чешского формата, которые форматируют числа следующим образом: 1 000,00 т. Е. Запятая используется как десятичный разделитель, а пробел — как разделитель тысяч. Разделитель списка по умолчанию в настройках системы ; .)

Я изменил системный разделитель списка на запятую в регионе -> Дополнительные настройки
В Excel 2013 я перешел к «Параметры» -> «Дополнительно» и не выбрал пункт « Использовать системные разделители» (в разделе «Параметры редактирования», который является первым разделом).
Я установил десятичный разделитель в Excel, . а разделитель тысяч — , (разделитель тысяч, вероятно, не имеет значения, но я хотел, чтобы он соответствовал форматированию США)

Альтернативой шагам 2 + 3 было бы изменение этих настроек в системных настройках (шаг 1), но я обычно хочу, чтобы числа были отформатированы в соответствии с чешским языком.

Недостатки : в Excel мне теперь нужно вводить десятичные числа, используя локали США, т.е. используя точку вместо запятой. Это немного неестественно, но для меня это приемлемый компромисс. К счастью, клавиша запятой на моей цифровой клавиатуре превратилась в клавишу точки автоматически (и только в Excel — другие приложения по-прежнему выводят запятую).

Очевидно, Excel использует региональный параметр Windows 7 для разделителя по умолчанию для CSV (который по-видимому является вкладкой по умолчанию). Вы можете изменить эту настройку следующим образом .

Читать еще: Скачать dvm.dll

Тем не менее, я не знаю, каковы будут другие последствия. Я полагаю, что если все файлы CSV на вашем компьютере разделены запятыми, то проблем быть не должно. Но если у вас есть другая (скорее всего, Microsoft) программа, которая также использует файлы CSV и обычно использует файлы CSV, отформатированные с другим разделителем, то это может быть проблематично.

Например, если Outlook использует, например, разделенные табуляцией CSV для импорта / экспорта контактов, и вы получаете такие CSV из внешнего источника (Gmail, другой компьютер и т. Д.), То изменение этого общесистемного параметра может помешать открытию Outlook эти разделенные табуляцией CSV.

LibreOffice Calc имеет очень продвинутый CSV-фильтр, который позволяет выбирать разделители, форматы и кодировки. Я интенсивно работаю с данными в различных форматах, и очень часто мне нужно отправлять эти данные в формате Excel пользователям. Я использую LibreOffice Calc для преобразования csv-файлов в нужный формат, а затем сохраняю как xls.

Это может не отвечать на вопрос, как это легко сделать в Excel, но отвечает на вопрос, как это сделать легко, тем более, что OP в комментариях предлагает использовать что-то отличное от Excel, что будет приемлемым вариантом.

Excel 2010 — Помимо проверки расширенных региональных настроек, обязательно проверьте дополнительные настройки Excel:

Импорт и экспорт текстовых файлов (в формате TXT или CSV)

Импортировать данные из текстового файла в Excel можно двумя способами: открыть их в Excel или импортировать как диапазон внешних данных. Чтобы экспортировать данные из Excel в текстовый файл, воспользуйтесь командой Сохранить как и измените тип файла в меню.

Чаще всего используются два формата текстовых файлов:

текстовые файлы с разделителями (TXT), в которых для разделения полей текста обычно используется знак табуляции (знак ASCII с кодом 009);

текстовые файлы с разделителями-запятыми (CSV), в которых в качестве разделителя полей текста обычно используется запятая (,).

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

Примечание: Можно импортировать и экспортировать до 1 048 576 строк и 16 384 столбцов.

Импорт текстового файла путем его открытия в Excel

Текстовый файл, созданный в другой программе, можно открыть как книгу Excel с помощью команды Открыть. При открытии текстового файла в Excel его формат не изменяется — это можно заметить по заголовку окна Excel, где имя указывается с расширением текстового файла (например, TXT или CSV).

Перейдите в >открыть и перейдите в папку с текстовым файлом.

В диалоговом окне Открытие выберите текстовые файлы в списке тип файла.

Найдите нужный файл и дважды щелкните его.

Если это текстовый файл (с расширением ТХТ), запустится мастер импорта текста. Окончив работу с мастером, нажмите кнопку Готово, чтобы завершить импорт. Дополнительные сведения о разных вариантах и дополнительных параметрах см. в мастере импорта текста.

Если файл имеет расширение CSV, Excel автоматически откроет его и отобразит данные в новой книге.

Примечание: При открытии CSV-файлов для определения способа импорта каждого столбца используются заданные по умолчанию параметры форматирования данных. Если вы хотите применить другие параметры, используйте мастер импорта текста. Например, мастер потребуется, если столбец данных в CSV-файле имеет формат МДГ, а в Excel по умолчанию используется формат ГМД или необходимо преобразовать в текст столбец чисел, содержащих начальные нули, чтобы сохранить их. Чтобы принудительно запустить мастер импорта текста, можно изменить расширение имени файла с CSV на TXT или импортировать текстовый файл путем подключения к нем (дополнительные сведения см. в следующем разделе).

Импорт текстового файла путем подключения к нем (Power Query)

Вы можете импортировать данные из текстового файла в существующий.

На вкладке Данные в группе & преобразование данных выберите из текстового/CSV-документа.

В диалоговом окне Импорт данных найдите и дважды щелкните текстовый файл, который нужно импортировать,и нажмите кнопку Импорт.

В диалоговом окне предварительного просмотра есть несколько вариантов:

Выберите Загрузить, если вы хотите загрузить данные непосредственно на новый.

Кроме того, выберите загрузить в, если вы хотите загрузить данные в таблицу, в смету или на существующий или новый листа Excel, или просто создать подключение. Вы также можете добавить данные в модель данных.

Выберите Преобразовать данные, если вы хотите загрузить данные в Power Query и отредактировать их перед их преобразованием в Excel.

Если Excel не преобразует определенный столбец данных в нужный формат, их можно преобразовать после импорта. Дополнительные сведения см. в статьях Преобразование чисел из текстового формата в числовой и Преобразование дат из текстового формата в формат даты.

Экспорт данных в текстовый файл путем его сохранения

Лист Excel можно преобразовать в текстовый файл, используя команду Сохранить как.

Выберите Файл > Сохранить как.

Нажмите кнопку Обзор.

В диалоговом окне Сохранить как в поле Тип файла выберите формат текстового файла для этого документа. Например, щелкните Текст (с табу женой) или CSV (сзапятой).

Примечание: Различные форматы текстовых файлов поддерживают разные возможности. Дополнительные сведения см. в статье Поддерживаемые Excel форматы файлов.

Перейдите в папку, куда вы хотите сохранить файл, а затем нажмите кнопку Сохранить.

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

Кроме того, под лентой может появиться предупреждение о том, что некоторые возможности могут быть потеряны при сохранение книги в формате CSV.

Дополнительные сведения о сохранении файлов в других форматах см. в статье Сохранение книги в другом формате файла.

Импорт текстового файла путем подключения к нему

Вы можете импортировать данные из текстового файла в существующий.

Щелкните ячейку, в которую вы хотите поместить данные из текстового файла.

На вкладке Данные в группе Получение внешних данных нажмите Из текста.

Следуйте инструкциям мастера импорта текста. Щелкните на любой странице мастера импорта текста, чтобы получить дополнительные сведения об использовании мастера. Окончив работу с мастером, нажмите кнопку Готово, чтобы завершить импорт.

В диалоговом окне Импорт данных:

В группе Куда следует поместить данные? выполните одно из следующих действий:

Чтобы поместить данные в выбранные ячейки, щелкните На существующий лист.

Чтобы поместить данные в верхний левый угол нового листа, выберите вариант На новый лист.

При необходимости нажмите кнопку Свойства, чтобы задать параметры обновления, форматирования и макета импортируемых данных.

Нажмите кнопку ОК.

Excel добавит диапазон внешних данных в указанное место.

Если Excel не преобразует столбец в необходимый формат, это можно сделать после импорта. Дополнительные сведения см. в статьях Преобразование чисел из текстового формата в числовой и Преобразование дат из текстового формата в формат даты.

Читать еще: Ошибка при установке Дискорда

Экспорт данных в текстовый файл путем его сохранения

Лист Excel можно преобразовать в текстовый файл, используя команду Сохранить как.

Выберите Файл > Сохранить как.

Откроется диалоговое окно Сохранение документа.

В поле Тип файла выберите формат текстового файла для листа.

Например, выберите Текстовые файлы (с разделителями табуляции) или CSV (разделители — запятые).

Перейдите в папку, куда вы хотите сохранить файл, а затем нажмите кнопку Сохранить.

Откроется второе диалоговое окно с напоминанием о том, что сохраняемый лист может содержать возможности, которые не поддерживаются текстовыми форматами файлов. Если в новом текстовом файле нужно сохранить только данные листа, нажмите кнопку Да. Если вы не уверены и хотите получить дополнительные сведения о том, какие возможности Excel не поддерживаются форматами текстовых файлов, нажмите кнопку Справка.

Способ изменения деления при импорте зависит от того, как вы импортируете текст.

Если вы используете get & Transform Data > From Text/CSV, после выбора текстового файла и нажатия кнопки Импорт выберитесимвол, который нужно использовать в списке . Новый вариант можно сразу же увидеть в окне предварительного просмотра данных, чтобы убедиться в том, что вы сделаете нужный вариант, прежде чем продолжить.

Если вы импортируете текстовый файл с помощью мастера импорта текста, вы можете изменить его на шаге 2 мастера импорта текста. На этом шаге также можно изменить способ обработки последовательных разделителей (например, идущих подряд кавычек).

Дополнительные сведения о разных вариантах и дополнительных параметрах см. в мастере импорта текста.

Если при сохранение в CSV-окне вы хотите использовать в качестве по умолчанию полу двоеточие, но для этого нужно ограничиться Excel, измените десятичной запятой. В этом случае Excel будет использовать для этого двоеточие. Очевидно, что это также изменит способ отображения десятичных чисел, поэтому также рассмотрите возможность изменения разграничения чисел в тысячах, чтобы сузить путаницу.

Очистка параметров Excel> Дополнительные>параметры редактирования> Использование системных междугов.

Для десятичной запятой установите для десятичной запятой (запятую).

Установите для 1000-го построитель . (точка).

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

Внимание: Изменение параметров Windows приведет к глобальным изменениям на компьютере и повлияет на все приложения. Чтобы изменить только делитель для Excel, см. изменение стандартного деления в списке для сохранения файлов в виде текста (CSV- или CSV-файла) в Excel.

В Microsoft Windows 10 щелкните правой кнопкой мыши кнопку Начните и выберите параметры.

Нажмите кнопку & языки выберите регион в левой области.

На главной панели в группе Региональные параметрыщелкните Дополнительные параметры даты,времени и региона.

В областивыберите изменить форматы даты, времени или числа.

В диалоговом окке Регион на вкладке Формат нажмите кнопку Дополнительные параметры.

В диалоговом окне Настройка формата на вкладке Числа введите символ, который будет использовать в качестве нового в поле «Сепаратор списков».

Дважды нажмите кнопку ОК.

Нажмите кнопку Пуск и выберите пункт Панель управления.

В разделе Часы, язык и регион щелкните элемент Изменение форматов даты, времени и чисел.

В диалоговом окке Регион на вкладке Формат нажмите кнопку Дополнительные параметры.

В диалоговом окне Настройка формата на вкладке Числа введите символ, который будет использовать в качестве нового в поле «Список».

Дважды нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Преобразование форматов CSV в XLS или XLSX

В соответствии с Методическими рекомендациями публикации откртых данных 3 версии наборы данных большого объема более 80 тыс записей скачиваются в форматах JSON или CSV. Формат CSV иногда вызывает трудности с преобразованием в xls или xlsx. Инструкция по шагам расскажет как импортировать данные из CSV с использованием стандартного инструментария Exsel 2007-20ХХ Инструкция по конвертации CSV в XSL.

Как открыть в Excel файл CSV в формате открытых данных версии МР 3.0

Выгрузки данных в формате CSV (comma-separated values) обычно делаются с разделителями значений – запятыми и в кодировке UTF-8. Однако Microsoft Excel по умолчанию не может правильно отображать их содержимое при стандартной процедуре открытия файла (неправильно отображается кириллица и данные не разбиваются по колонкам).

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

Перейти на вкладку «Данные» и выбрать пункт меню «Из текста».

В стандартном диалоговом окне открытия файла выбрать файл, который необходимо открыть, и нажать кнопку «Импорт».

Откроется окно мастера импорта файлов. В поле «Формат исходных данных» необходимо выбрать «с разделителями», в выпадающем окне «Формат файла» выбрать «Юникод (UTF-8)».

В самом низу окна находится поле предварительного просмотра. В нем можно проверить, что текст на русском языке отображается корректно.

После настройки параметров необходимо нажать кнопку «Далее».

В следующем окне необходимо правильно задать разделитель значений. Для этого в поле «Символом-разделителем является» поставить галочку «запятая» и снять все остальные галочки, если они где-то стоят. При этом можно увидеть, что в поле предпросмотра в нижней части окна значения начнут отображаться в виде колонок.

Необходимо нажать кнопку «Далее».

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

При импорте даты часто искажаются, поэтому рекомендуется указать мышью столбец, содержащий дату (он выделится черным цветом), и назначить ему значение даты в нужном формате (для случая на экране у нас формат год-месяц-день, поэтому в выпадающем списке «дата:» необходимо выбрать значение «ГМД».

По окончании настройки нажать кнопку «Готово».

После этого на экране появится диалоговое окно «Импорт данных». Требуется указать ячейку, начиная с которой на листе будут вставлены данные из файла CSV (это будет левый верхний угол таблицы). После нажатия кнопки «ОК» данные появятся на листе.

0 0 голоса

Рейтинг статьи

Excel CSV. файл с более чем 1 048 576 строками данных