Как разделить csv файл в excel

Обновлено 25.05.2020

Добрый день! Уважаемые читатели и гости одного из крупнейших IT блогов для системных администраторов Pyatilistnik.org. В прошлый раз мы с вами подробно разобрали 10 методов как открыть командную строку. В сегодняшней публикации я хочу вас научить разбивать строки в CSV файле на столбцы, которые потом можно использовать в файлах Excel. Уверен, что многие системные администраторы часто работаю с экспортными данными в формате CSV.

Постановка задачи

Бьюсь об заклад, что у 99% средних и крупных организаций в России инфраструктура построена на использовании Active Directory. Активный каталог замечательная вещь и легко интегрируется в различные сторонние сервисы. Самая частая задача администратора, это получение отчетов или выгрузок по определенным критериям, например:

• Получить список пользователей Active Directory с рядом атрибутов
• Получить список серверов у которых операционная система Windows Server 2008 R2, чтобы в дальнейшем их вывести из эксплуатации
• Получить список всех компьютеров или пользователей, кто не авторизовывался какое-то время. И так до бесконечности

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

Структура файла CSV

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

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

Вот пример такого строки с разделителями:

теперь представьте, что таких строк сотни или тысячи, а вы хотите все скопировать в ваш Exсel и по столбцам, вот тут вы и поймете, что одностроковый формат требует преобразования. Благо, это делается очень быстро и просто. Вот пример моего тестового csv файла.

Как csv разделить по столбцам в Excel

Первым инструментом, который позволит из csv получить красивые столбцы и разбиение по ним, будет Excel. Откройте в Excel ваш файл csv.

• Выделите первый столбец и перейдите на вкладку «Данные«
• Найдите пункт «Текст по столбцам«

На первом окне мастера распределения текста по столбцам нажмите «Далее».

Далее вам необходимо указать по каким критериям производить разбивку по столбцам, на выбор у вас будет:

• Знак табуляции
• Точка с запятой
• Запятая
• Пробел
• Другой вариант

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

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

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

Далее вам остается сохранить файл в формате xlsx и радуемся жизни.

Как csv разделить по столбцам через Google Таблицы

Не так давно я вам рассказывал про установку через групповую политику расширения для Google Chrome под названием «Редактирование файлов Office«. Это расширение позволяло работать с файлами Word, Excel и PowerPoint в Google Документах, Google Таблицах и Google Презентациях. Если вы через него откроете ваш файл CSV, то вы буквально в два клика сможете его разбить на столбцы. Для этого выберите меню «Файл — Сохранить в формате Google Таблиц«.

Начнется переделывание формата CSV в формат Google Таблиц

На выходе вы получаете разделенный по столбцам документ.

Далее вы уже можете оперировать этими данными или же можете их сохранить в нужный формат. Если у вас изначально документ Google Таблиц содержит данные разделенные запятой в виде одной строки, то вы их можете преобразовать в столбцы вот таким методом. Открываете меню «Данные — Разделить текст на столбцы«

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

Затем выбираете ваш столбец с данным и еще раз нажмите «Данные — Разделить текст на столбцы«

В результате чего они будут разбиты на аккуратные столбцы

Как видите Google Таблицы умеют быстро разбить файл CSV по столбцам. Надеюсь, что это вам сэкономит время и вы сможете теперь более удобно оперировать данными. С вами был Иван Семин, автор и создатель IT портала Pyatilistnik.org.

Microsoft Excel is excellent at so many day-to-day tasks. But now and then, you encounter one of its shortcomings: the size of a spreadsheet. Read on to find out how to make an Excel spreadsheet smaller or split a large CSV file into multiple files.

Why Split a Large CSV Into Multiple Files?

You might be thinking, “Why would I need to split a large Excel file into multiple smaller files?” It’s an interesting question, especially as Excel has a spreadsheet row limit of 1,048,576.

Over 1 million rows sounds phenomenal. However, hitting the row limit is easier than you think, especially during certain tasks. For example, if you’re marketing via email, you might import a CSV file containing millions of email addresses.

The only problem is, how do you manage a spreadsheet with that many addresses in? Furthermore, what if someone sends you a CSV that is already over the limit (from another program)?

If that sounds like an issue you’re likely to face, check out the following five ways to split a large CSV or Excel file into multiple smaller files.

Don’t have a large CSV file handy but want to play along at home? I’m using the COVID-19 Open Research Dataset in the examples, which you can download and use too.

1. Break Up CSV Files Using a Program

There are several useful CSV splitter programs out there. Here are two of the best. Fair warning, though, as these programs are working, they sometimes run into memory issues, which is a common problem for CSV-splitting programs.

Free Huge CSV Splitter

The Free Huge CSV Splitter is a basic CSV splitting tool. You input the CSV file you want to split, the line count you want to use, and then select Split File. The line count determines the number of output files you end up with.

CSV Splitter

CSV Splitter is the second tool. It offers much the same functionality as Free Huge CSV Splitter, albeit with a slightly sleeker design. It splits your CSV into small chunks rapidly, allowing you to determine the line count you want to use.

2. Use a Batch File

Next up, create a programmable batch file. You can use a batch file to process the CSV into smaller chunks, customizing the file to deliver different chunks.

Open a new text document, then copy and paste the following:

 @echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile



  


                       
  



REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
)
)
endlocal
Pause 

You’ll need to configure the batch file before running. I’ll tell you what each command does, and you can alter it to suit the size of your batch file, as well as the required output.

• «SET BFN=» should point to the CSV you need to break down
• «SET LPF=» is the number of rows you’d like to limit your new file to
• «SET SFN=» is the new naming scheme for your split files

Once you’ve entered your variables, head to File > Save As. Choose a filename, and select Save. Then, select your newly saved text file and press F2 to rename it. Replace the .txt extension with .bat and press OK when the warning appears. Now, you can split your large CSV file into smaller output files.

3. Use a PowerShell Script to Break Up a CSV File

You can use batch files for a wide range of day-to-day tasks. But PowerShell scripts are faster, especially for this type of processing and division.

The following script quickly cuts your large CSV into smaller files.

First up, press CTRL + X to open the Windows Power Menu, then select PowerShell. If PowerShell isn’t an option, input powershell in your Start Menu search bar and select the Best Match.

Now, copy and paste the following script:

 $InputFilename = Get-Content 'C:filelocation'
$OutputFilenamePattern = 'output_done_'
$LineLimit = 50000
$line = 0
$i = 0
$file = 0
$start = 0
while ($line -le $InputFilename.Length) {
if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
$file++
$Filename = "$OutputFilenamePattern$file.csv"
$InputFilename[$start..($line-1)] | Out-File $Filename -Force
$start = $line;
$i = 0
Write-Host "$Filename"
}
$i++;
$line++
} 

Replace the file location in the first line with your CSV file, then run the script. The script outputs the smaller CSV files in your user directory. For example, my CSV files are found at C:UsersGavin with file name output_done_1.csv. You can change the output name by altering the $OutputFilenamePattern = ‘output_done_’ line.

You can find the original script at SPJeff.

4. Break Up a Large CSV Using Power Pivot

Your penultimate solution to breaking up a large CSV file into small bits doesn’t actually break it down. Rather, it lets you load your massive CSV file into Excel and use the Power Pivot tool to open it. That’s right; you can effectively ignore the Excel line limit and manage the file within the program.

You achieve this by creating a data link to the CSV file, then using Power Pivot to manage the contents. For a full explanation and tutorial, read Jose Barreto’s blog detailing the process.

In short, Barreto creates a Pivot Table using “up to 8.5 million rows with no problem at all.” The above image comes from the blog post, showing a total of 2 million rows in use in Excel.

Remember, this process doesn’t split the CSV into small chunks. However, it does mean you can manipulate the CSV in Excel, which is a very handy alternative. If you need more tips, find out how to use a Pivot Table for data analysis.

5. Break Up Large CSV Online Using Split CSV

There are also online services that break your big CSV file into smaller bits. One such option is Split CSV, a free online CSV splitter.

Split CSV managed the COVID-19 dataset fine, splitting it into handy chunks. As with other tools, you define the line count for each file and let it split it up. However, I didn’t have a large CSV file to hand to test with, and, as such, your experience may vary.

Split CSV includes premium options, too. For a subscription fee, you can use a custom delimiter, choose a selection of output file types, remove certain characters from the output files, and delete duplicate lines.

Break Down Your CSV Files Into Easily Manageable Chunks

You now have five solutions for breaking your CSV files into small bits, making them easy to manage. The solutions vary in speed and the size of the CSV files they can manage, so you may have to experiment to find the solution that works best for you. And the best part of all? These CSV splitting techniques work on Windows 10 and Windows 11. You can even use the online CSV splitting tool on macOS and Linux!

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

Для чего это необходимо

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

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

Дальше приходит понимание, что управлять такой огромной таблицей очень не просто, даже если вы знаете всякие «хитрые» приёмы Эксель, умеете пользоваться фильтрами и формулами. Или ещё может сложиться такая ситуация, что вам кто-то прислал такой файл, иногда он даже может превышать лимит, если был создан в какой-то другой программе.

Первый способ — разбить с помощью программы

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

1. Free Huge CSV Splitter.

Эта программа считается базовым инструментом для разделения CSV файлов. Работает она по очень простому принципу: нужно выбрать файл, который необходимо разделить, а также указать количество строк, которое нужно использовать, после чего нажать кнопку «Split file».

2. CSV Splitter.

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

Второй способ — использование пакетного файла

Этот вариант немного сложнее, чем первый, поэтому здесь очень важно соблюдать чёткую инструкцию.

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

@echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. If will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F “delims==” %%1 in (%BFN%) Do (
SET /A LeneNum+=1
echo %%1 >> %SFN%!FileNum! .%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A LineNum+=1
)
)
endlocal
Pause

Источник кода: как разделить огромную электронную таблицу CSV Excel на отдельные файлы

Но не стоит торопиться, поскольку перед запуском нужно настроить командный файл. Чтобы понимать, как это работает, нужно разобраться, для чего и как работает каждая команда, которая использовалась выше.

«SET BFN» — это очень важная команда, потому что именно с помощью неё указывается CSV файл, который необходимо разбить.

Команда «SET LPF» укажет на то, сколько строк будет в новом файле.

«SET SFN» отвечает за то, как будут именоваться новые разделённые файлы.

Теперь переходим к самому интересному — использованию получившегося файла. Чтобы это сделать, надо нажать Файл > Сохранить как > выбрать имя файла. После этого выбрать этот же файл и нажать кнопку F2, это даст возможность изменить формат этого файла с .txt на .bat. Появится предупреждение, нужно согласиться и нажать ОК.

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

Третий способ — использование сценария PowerShell

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

Сначала пользователю необходимо попасть в специальное меню Windows Power. Для этого он должен нажать комбинацию клавиш CTRL+R. В открывшемся меню как раз и будет находиться PowerShall. Если по какой-то причине этот способ не сработал, тогда можно ввести powershell прямо в строке поиска в «Пуске» и выбрать «Лучшее совпадение». После этого необходимо прописать следующий скрипт:

$InputFilename = Get-Content ‘C:filelocation’
$0outputFilenamePattern = ‘output_done_’
$LineLimit = 50000
$line = 0
$i = 0
$file = 0
$start = 0
while ($line -le $InputFilename . Length) {
if ($i -eq $LineLimit -Or $line -eq $InputFilename.Lenght) {
$file++
$Filename = “$OutputFilenamePattern$file.csv”
$InputFilename[$start..($line-1)] | Out-File $Filename -Force
$start = $line;
£i = 0
Write-Host “$Filename”
}
$i++;
$line++
}

Источник кода: как разделить огромную электронную таблицу Excel CSV на отдельные файлы

Прежде чем работать с этим скриптом, нужно понимать, что вместо «filelocation» должно быть прописано местоположение вашего файла CSV, который необходимо разделить.

Четвёртый способ — использовать Power Pivot

На самом деле, Power Pivot не разбивает файлы CSV на несколько маленьких файлов, принцип работы этой программы немного другой.

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

Если нет желания разбираться со скриптами, которые не для всех покажутся лёгким решением, можно воспользоваться бесплатными онлайн-разделителями CSV.

Одним из таких сервисов является Split CSV. Он работает также, как первые две программы, про которые шла речь выше, с той лишь разницей, что всё действие происходит в Интернете, а не в самом компьютере.

Однако у этой онлайн-программы всё же есть кое-что интересное в арсенале. Если пользователь оплатит абонентскую плату, он получит в дополнение следующие функции:

• Использование настраиваемого разделителя;
• Выбор типа файла на выходе;
• Удаление определённых символов из выходных файлов;

Подводя итог: каковы различные способы разделения файла CSV?

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

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

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