Convert xls to csv linux

Конвертируйте xlsx в csv в Linux с помощью командной строки

Я ищу способ конвертировать xlsx файлы в csv файлы в Linux.

Я не хочу использовать PHP / Perl или что-то подобное, так как я смотрю на обработку нескольких миллионов строк, поэтому мне нужно что-то быстрое. Я нашел программу в репозиториях Ubuntu под названием xls2csv, но она будет конвертировать только файлы xls (Office 2003) (которые я сейчас использую), но мне нужна поддержка более новых файлов Excel.

Приложение электронных таблиц Gnumeric поставляется с утилитой командной строки под названием ssconvert, которая может конвертировать различные форматы электронных таблиц:

Чтобы установить на Ubuntu:

Чтобы установить на Mac:

Вы можете сделать это с LibreOffice:

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

Если у вас уже есть среда рабочего стола, то я уверен, что Gnumeric / LibreOffice будет работать хорошо, но на автономном сервере (таком как Amazon Web Services) им требуются десятки зависимостей, которые также необходимо установить.

Я нашел эту альтернативу Python:

Потребовалось 2 секунды для установки и работает как шарм.

Если у вас есть несколько листов, вы можете экспортировать все сразу или по одному:

Он также ссылается на несколько альтернатив, встроенных в Bash, Python, Ruby и Java.

В bash я использовал эту команду libreoffice для преобразования всех моих файлов xlsx в текущем каталоге:

Он заботится о пробелах в имени файла.

Попробовал еще несколько лет спустя, и это не сработало. Этот поток дает несколько советов, но самым быстрым решением было запустить от имени пользователя root (или запустить sudo libreoffice ). Не элегантно, но быстро.

Используйте команду scalc.exe в Windows

Для деталей проверьте их отличные документы

Другой вариант — для удобства использовать R с помощью небольшой оболочки bash:

Если в .xlsx файле много листов, -s можно использовать флаг, чтобы получить нужный лист. Например:

second_sheet.csv будет содержать данные 2-го листа в my_file.xlsx .

Использовать приложение Gnumeric для работы с электронными таблицами, в котором утилита командной строки под названием ssconvert действительно очень просто:

Если вы можете запустить командную строку Java, вы можете сделать это с помощью Apache POI HSSF Excel Extractor . У него есть main метод, который говорит, что это экстрактор командной строки . Этот, кажется, просто вываливает все. Они указывают на этот пример, который конвертируется в CSV . Вам придется скомпилировать его, прежде чем вы сможете запустить его, но он тоже имеет main метод, поэтому вам не нужно делать много кода как такового, чтобы заставить его работать.

Другой вариант, который может сработать, но потребует некоторой работы на другом конце, состоит в том, чтобы заставить ваши файлы Excel поступать к вам в виде XML-данных Excel или электронной таблицы XML любого вызова MS, который форматирует в эти дни. Это откроет вам новый мир возможностей нарезать и нарезать кубиками так, как вы хотите.

Как говорили другие, libreoffice может конвертировать файлы xls в csv. Проблемой для меня был выбор листа.

Этот скрипт Python libreoffice отлично справляется с конвертацией одного листа в CSV.

Единственным недостатком (с моей стороны) является то, что —headless , похоже, не работает. У меня есть окно LO, которое появляется на секунду, а затем выходит.
Это нормально для меня, это единственный инструмент, который делает работу быстро.

Источник

5 Methods to Convert xlsx Format Files to CSV on Linux CLI

XLSX is a file extension for an open XML spreadsheet file format used by Microsoft excel. Converting Microsoft Excel sheet to a Comma Separated file (CSV) is relatively very easy while using command line. The situation may arrive when you have a XLS file and you need to fill the database from it after formatting the data. It exists some methods in command line in order to do the conversion of the different format files.

1) Gnumeric spreadsheet program

Gnumeric is a spreadsheet program for Unix and Unix-like operating systems distributed under the GNU General Public License. It stores its information by creating files and re-opening these files during a future session. It can import and export spreadsheet data to and from multiple formats, including CSV, Microsoft Excel, HTML, OpenDocument, Quattro Pro, and LaTex.

Gnumeric is not present by default in the repository of your centos 7, you must first install the latest lux-release. First download it

Now you can install the lux release

With the lux-release installed, we can now install gnumeric via package

Now you can use the ssconvert command of the gnumeric spreadsheet to convert the file

You can visualize the file now

2) xlsx2csv converter

xlsx2csv converter is a python application that is capable to convert a batch of XLSX/XLS files to CSV format. You can specify exactly which sheets to be converted. If you have multiple sheets, the xlsx2csv give the possibility to export all the sheets at once, or one at a time.

To install it, you need to have python already installed. Then, you can proceed as below:

Now you can convert you xlsx file

You can check the content of the file

By default, the xlsx2csv command convert only the first sheet even if your file contains multiples sheets. Fortunately, it offes the possibilty to convert all the sheets or to choose the one to convert. You can use some interesting paramaters:

• -a, —all to export all sheets
• -d DELIMITER for columns delimiter in csv
• -p SHEETDELIMITER for sheet delimiter used to separate sheets, pass » if you do not need delimiter, or ‘x07’ or ‘\f’ for form feed (default: ‘———‘)
• -s SHEETID for the sheet number to convert

For example, if you want to convert only a specific sheet

Now if you want to convert all the sheet, you can do as below

You can check the content as below

You can see that the default delimiter helps to know the sheets.

3) csvkit tool

csvkit is a python library optimized for working with CSV files. It is a nice tool to manipulate, organize, analyze and work with data, using the csv format. It is very light and fast. It is used through the terminal with its in2csv command which converts a variety of common file formats, including xls, xlsx and fixed-width into CSV format..

Now you can convert as below:

4) unoconv

OpenOffice comes with the unoconv program to perform format conversions on the command line. It is present by default if openoffice is installed. You can use the manual

The command is capable to convert between various file formats. by default, it converts in pdf. It means that you should indicate the desired format if you don’t want to have a undesired format. So, to convert in csv with the unoconv command, you need to use two main parameters:

• -f which indicates the request the final format of the output file
• -o to indicate the name and the path of the converted file

You can check the content

Note that the second row of our original xlsx file is empty, that is why you have the comma on the second line of the csv file.

5) Libreoffice headless

By starting the LibreOffice software from the command line you can assign various parameters, with which you can influence the performance. It is possible through the headless mode which help you to launch LibreOffice in command line without any graphical interface component. It gives you the possibility to convert file in some formats as you need. So, you can use it to convert xlsx files in csv. You need to use the indicated the final format (csv) with the —convert-to parameter followed by the file to convert as below:

Now you can check the file

You can directly convert some xlsx files as below:

You can look the converted as below

You can check the content of one file

We have seen the different tools available on Linux to convert any xlsx file format in csv file on command line. You can decide to convert the file in odt or pdf and it is possible with unoconv and libreoffice headless. Worth trying Miller tool which does conversion between formats and more.

Источник

Methods to Convert xlsx Format Files to CSV on Linux CLI

XLSX is a format/file extension for Open XML Spreadsheet file format used by Microsoft Excel. Transforming xlsx to csv (comma-separated file) is easy via command line. Here are few methods to convert xlsx to csv format.

Gnumeric Spreadsheet Program

It is a GNOME-based program, a spreadsheet application that replicates basic features of a popular commercial program like Excel. It can import and export data to and from multiple formats like including CSV, Microsoft Excel, HTML, OpenDocument, Quattro Pro, and LaTeX.

To install Gnumeric in Linux use the apt-get command to install the Gnumeric repository via Linux terminal.

Now to convert xlsx format to csv format using ssconvert command of Gnumeric to convert the file.

To view the contents of the file using the cat command to check the csv file.

xlsx2csc converter

A python application to convert XLSX/XLS files to CSV format. There is also an option to convert a specific sheet or all the sheets from the data. xlsx2csv has a feature to export all sheets at once.

To install xlsx2csv python should be installed in the Linux machine.

Or we can install it in Linux terminal with the help of apt-get command as Kali Linux has the package is pre-installed,

Now convert the file from xlsx to csv and view the contents:

Here we see that xlsx2cv has only converted a single sheet, but our sheet has multiple sheets so to convert all of them to csv, the xlsx2csv converter has many parameters to convert the files/sheets.

• -a, –all – export all sheets
• -d – DELIMITER for columns delimiter in csv
• -p – SHEETDELIMITER for sheet delimiter used to separate sheets, pass
• -s – SHEETID for the sheet number to convert

To convert all the sheets from the file, execute the following command, on viewing the contents of the file we see that there are 4 sheets (sheet 1: Instructions, sheet 2: sales order, sheet 3: Sample number, and sheet 4: my link) being converted from xlsx to csv format

csvkit Tool

The tool is a python library for working with csv files with this we can manipulate, organize and analyze data. To use the tool in2csv is executed for converting files. The tool is light and user-friendly. Install the csvkit tool from Linux CLI using the command given below,

Convert the xlsx file to csv by executing the following command:

Viewing contents of the file using cat command:

unoconv

A command-line program to convert office document file format. It also uses LibreOffice to do the conversion, where it can import to any file format that LibreOffice is capable of exporting. For installation in Linux machine use the following the command,

Converting the Microsoft Excel file to csv format we execute or run the command given below, this command is capable to convert many files format. This also means that it indicates desired format we want to convert the file to, to mention the output format the following parameters are used.

unoconv options:

• -f, –format=format: Specify the output format
• -o, –output=name: Output basename, filename or directory

View contents or to check if the output file has successfully converted the file, we use the cat command

LibreOffice Headless

A package allowing LibreOffice to work in command-line with the help of a headless flag allows the application to work without a user interface. It works by converting a single or group of files from one format to another. We can directly use it from CLI, installation of LibreOffice is not required. Indicate the output file format (csv) that you would desire to get with –convert-to parameter followed by file for conversion as done below. After this use ls command to check the output file of xlsx format

Use cat command to view contents of the file:

Источник

Convert XLSX to CSV in Linux with Command Line in Linux

XLSX file is a standard file extension for the modern Microsoft Excel spreadsheet. We use of these files to analyze and organize data and they usually contain numerical data separated by rows and columns within a cell.

On the other hand, a CSV file is basically a delimited text file that uses a comma to separate values, and each line of this file is usually a data record.

While both the xlsx and csv formats of storing data are way different, as one makes use of tables with rows and columns, while the other uses commas to separate the values, but that doesn’t mean we can’t convert one into the other.

There are several online tools that one can use, but when it comes to converting a xlsx into a csv using the linux terminal, it’s a little more complicated than uploading the file somewhere and getting the csv as an output.

There are different command line applications that we can make use to do the same, I’ll show two of the most commonly used applications that help us to convert the xlsx into csv using the linux command line.

The first one is the gnumeric spreadsheet application. The gnumeric spreadsheet application comes with a command line utility called ssconvert that we will make use of to convert the xlsx into a csv.

In order to use the ssconvert, we first need to install the gnumeric spreadsheet application on our local machine.

For Ubuntu

For Mac

Now we have gnumeric on our local machines, the next step is to make use of ssconvert utility and we are done.

Write the command shown below to the terminal to convert a.xlsx file into b.csv

It should be noted that both these files are present in my local environment, you can simply replace them with whatever files you have on your local machine.

Output

Now, if I check the contents of my Sample_CSVFile which was created with the help of the above command, I’ll get the following output −

You can clearly notice that records are separated by commas, and hence the file is valid.

One alternate way to achieve the above is to make use of libreoffice, in that case we just need to write the following command to the terminal to convert a xlsx file to csv.

The above command will convert all the files that are present in the current directory to their csv namesake.

Источник