Linux xlsx to text

Excel – Convert xlsx to text CSV via command line only on Linux

Want to improve this question? Update the question so it’s on-topic for Stack Overflow.

Is it currently possible to invoke LibreOffice from a command line to open a *.xlsx and convert/save it as a *.csv?

Alternatively, if this is not possible what is the current best method for doing this via command line (needs to be invokable via script)?

Best Solution

localc --headless --convert-to csv:"Text - txt - csv (StarCalc)" *.xlsx 

The above will save all converted files in current directory(directory from where you’ll run the command). All converted files will have their filename extensions changed to csv.

Alternative, method is the use of unoconv which i’ve not used.

Related Solutions

Linux – Convert DOS line endings to Linux line endings in Vim

dos2unix is a commandline utility that will do this, or :%s/^M//g will if you use Ctrl — v Ctrl — m to input the ^M, or you can :set ff=unix and Vim will do it for you.

There is documentation on the fileformat setting, and the Vim wiki has a comprehensive page on line ending conversions.

Alternately, if you move files back and forth a lot, you might not want to convert them, but rather to do :set ff=dos , so Vim will know it’s a DOS file and use DOS conventions for line endings.

Bash – How to parse command line arguments in Bash

Bash Space-Separated (e.g., —option argument )

cat >/tmp/demo-space-separated.sh " # restore positional parameters echo "FILE EXTENSION = $" echo "SEARCH PATH = $" echo "LIBRARY PATH = $" echo "DEFAULT = $" echo "Number files in SEARCH PATH with EXTENSION:" $(ls -1 "$"/*."$" | wc -l) if [[ -n $1 ]]; then echo "Last line of file specified as non-opt/last argument:" tail -1 "$1" fi EOF chmod +x /tmp/demo-space-separated.sh /tmp/demo-space-separated.sh -e conf -s /etc -l /usr/lib /etc/hosts 

Output from copy-pasting the block above

FILE EXTENSION = conf SEARCH PATH = /etc LIBRARY PATH = /usr/lib DEFAULT = Number files in SEARCH PATH with EXTENSION: 14 Last line of file specified as non-opt/last argument: #93.184.216.34 example.com 

Usage

demo-space-separated.sh -e conf -s /etc -l /usr/lib /etc/hosts 

Bash Equals-Separated (e.g., —option=argument )

cat >/tmp/demo-equals-separated.sh " shift # past argument=value ;; -s=*|--searchpath=*) SEARCHPATH="$" shift # past argument=value ;; -l=*|--lib=*) LIBPATH="$" shift # past argument=value ;; --default) DEFAULT=YES shift # past argument with no value ;; *) # unknown option ;; esac done echo "FILE EXTENSION = $" echo "SEARCH PATH = $" echo "LIBRARY PATH = $" echo "DEFAULT = $" echo "Number files in SEARCH PATH with EXTENSION:" $(ls -1 "$"/*."$" | wc -l) if [[ -n $1 ]]; then echo "Last line of file specified as non-opt/last argument:" tail -1 $1 fi EOF chmod +x /tmp/demo-equals-separated.sh /tmp/demo-equals-separated.sh -e=conf -s=/etc -l=/usr/lib /etc/hosts 

Output from copy-pasting the block above

FILE EXTENSION = conf SEARCH PATH = /etc LIBRARY PATH = /usr/lib DEFAULT = Number files in SEARCH PATH with EXTENSION: 14 Last line of file specified as non-opt/last argument: #93.184.216.34 example.com 

Usage

demo-equals-separated.sh -e=conf -s=/etc -l=/usr/lib /etc/hosts 

To better understand $ search for «Substring Removal» in this guide. It is functionally equivalent to `sed ‘s/[^=]*=//’ two needless subprocesses.

Using bash with getopt[s]

getopt(1) limitations (older, relatively-recent getopt versions):

• can’t handle arguments that are empty strings
• can’t handle arguments with embedded whitespace

More recent getopt versions don’t have these limitations. For more information, see these docs.

POSIX getopts

Additionally, the POSIX shell and others offer getopts which doen’t have these limitations. I’ve included a simplistic getopts example.

cat >/tmp/demo-getopts.sh " = "--" ] && shift echo "verbose=$verbose, output_file='$output_file', Leftovers: $@" EOF chmod +x /tmp/demo-getopts.sh /tmp/demo-getopts.sh -vf /etc/hosts foo bar 

Output from copy-pasting the block above

verbose=1, output_file='/etc/hosts', Leftovers: foo bar 

Usage

demo-getopts.sh -vf /etc/hosts foo bar 

The advantages of getopts are:

1. It’s more portable, and will work in other shells like dash .
2. It can handle multiple single options like -vf filename in the typical Unix way, automatically.

The disadvantage of getopts is that it can only handle short options ( -h , not —help ) without additional code.

There is a getopts tutorial which explains what all of the syntax and variables mean. In bash, there is also help getopts , which might be informative.

Related Question

Источник

Extracting excel to text on linux server

Alternatively, tells awk to use any sequence of one or more field tabs as a field separator. tells awk to print a double-quote, followed by field 1 followed by a field separator followed by field 2 followed by a field separator followed by field 4 followed by a double-quote. tells awk to use as the field separator on output. Solution 1: You can use below command to separate it out with comma : Output Will be : Solution 2: Your input file is not comma-separated.

Extracting Excel to text on Linux server

It’s not particularly elegant, but you might try using the Linux command «strings» to extract just the printable characters from your spreadsheet file first. Then you could parse the output until you see the column headings, and the data should be after that.

For modern XLSX format you might want to use csvkit (might be packaged as python3-csvkit) . it will convert it into CSV wich is human readable and shell script friendly. Once you have it installed you can extract all the sheets in the xlsx like this: in2csv —write-sheet — Some_excel_file.xlsx > some_file.csv

Extracting Excel to text on Linux server, 2 Answers. It’s not particularly elegant, but you might try using the Linux command «strings» to extract just the printable characters from your spreadsheet file first. Then you could parse the output until you see the column headings, and the data should be after that. For modern XLSX format you might want to use csvkit …

Extract excel data to text file

Sub ExportSheetsToTxt() Dim Ws As Worksheet Dim FileName As String Dim rngDB As Range Dim r As Long, c As Integer Dim i As Long Dim myString As String Set Ws = Sheets(1) 'FileName = CurDir & "\" & Ws.Name & ".txt" FileName = "/Users/random/Desktop/G_code.Path.txt" With Ws r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column For i = 1 To c Step 6 Set rngDB = .Cells(5, i).Resize(r - 4, 6) myString = myString & getString(rngDB) Next i End With TransToTxt Filename, myString MsgBox ("Files Saved Successfully") End Sub Function getString(rng As Range) Dim vDB, vR() As String, vTxt() Dim i As Long, n As Long, j As Integer Dim strTxt As String vDB = rng For i = 1 To UBound(vDB, 1) n = n + 1 ReDim vR(1 To UBound(vDB, 2)) For j = 1 To UBound(vDB, 2) vR(j) = vDB(i, j) Next j ReDim Preserve vTxt(1 To n) vTxt(n) = Join(vR) Next i strTxt = Join(vTxt, vbCrLf) & vbCrLf & vbCrLf getString = strTxt End Function Sub TransToTxt(myfile As String, strTxt As String) Dim objStream As Object Set objStream = CreateObject("ADODB.Stream") With objStream '.Charset = "utf-8" .Open .WriteText strTxt .SaveToFile myfile, 2 .Close End With Set objStream = Nothing End Sub 

Extract excel metadata in Linux, 1 Answer. Excel files store a lot of meta data within the file, so you would have to parse the file itself to get at it. Since you’re on Linux and can’t use the Excel interop, you could try to use an Excel library like ExcelWriter or something similar. ExcelWriter is written for .Net, so you’d have to use mono. Hmm, kk.

Extracting data from text

Seems you have options that would be easier, but please try in B1:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&MID(A1,7,2)) 

=1*MID(A1,FIND(",",A1)+1,FIND(",",MID(A1,FIND(",",A1)+1,99))-1) 

I believe the easiest way for you to handle this situation is to simply save your current worksheet as a text file, and then reopen it in Excel. When you open the file again in Excel it should automatically separate out the three columns the way you want it. Here is a screen capture from my Excel after I have done this:

Next you can format the A column as a date however you wish.

How to read data from Excel sheet in Linux using Shell, In that excel sheet there will be S.No. in 1st column & Domain names in second column. There are 100 of domains in my excel sheet. So, I do not want to manually copy every domain & paste them into browser to check all. I simply want to write a shell script which can open all the domains into browser by running the …

Extract columns from csv to a text file

You can use below command to separate it out with comma , :

page_id,post_name,post_type 5550296508,Ben,www.cnn.com 5830242058,John,www.abc.com 9485676544,Sara,www.msc.com 

Your input file is not comma-separated. I am guessing that it is tab-separated. If that is the case, then try:

$ awk -F'\t' '' OFS='","' Data.csv "page_id","post_name","post_type" "5550296508","Ben Carson","shared_story" "5830242058","John Smith","news_story" "9485676544","Sara John","shared_story" 

If that is not quite it, then try:

How it works

• -F’\t’ tells awk to use tab as the field separator. Alternatively, -F’\t+’ tells awk to use any sequence of one or more field tabs as a field separator.
• print «\»»$1,$2,$4″\»» tells awk to print a double-quote, followed by field 1 followed by a field separator followed by field 2 followed by a field separator followed by field 4 followed by a double-quote.
• OFS='»,»‘ tells awk to use «,» as the field separator on output.

Extracting data from Excel Pivot Table Spreadsheet in, It seems like their should be some scriptable way to extract the underlying data. I only have linux machines (running windows/office in a VM; but I’d prefer a solution that doesn’t involve windows). I am familiar with tools like xls2csv (which doesn’t access the raw data) and using tools like python-unoconv to edit openoffice …

Источник

Extracting Excel to text on Linux server

I’ve been struggling with converting *.xls files generated by an instrument to text format on a Linux server. I’m unable to process the files with Spreadsheet::ParseExcel, unless, I manually open them, sign off on the security warning and save them. Otherwise, they are not recognized as Excel (tested with the sample code).

!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $file = "/data/excel/matrix.xls"; my $workbook = $parser->parse($file); if ( !defined $workbook ) < print "can't find workbook. "; die $parser->error(), ".\n"; > for my $worksheet ( $workbook->worksheets() ) < my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) < for my $col ( $col_min .. $col_max ) < my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; print "Row, Col = ($row, $col)\n"; print "Value = ", $cell->value(), "\n"; print "Unformatted = ", $cell->unformatted(), "\n"; print "\n"; > > > 

I’ve tried changing the extension to *.prn and it lets me open the files manually without a warning but they are not recognized by the Spreadsheet::ParseExcel either. The files contain 8 columns of data on the first sheet only. I would like to convert them to text files and used them to look up values in my Perl script. Here is some sample data in excel:

Gene Target Barcode1 Barcode2 Barcode3 Barcode4 Barcode5 Barcode6 MOTOR MOTOR_1 343 453 432 345 543 342 MYCN MYCN_2 342 98 87 876 54 765 

My last option is to use VBA but I would reader stick to Perl/Shell code if possible. Is there a straightforward solution to this problem? Thank you,

Источник