I have 1000 .txt files with one column and 200 rows each and I want to import all of them into one LibreOffice Calc sheet. So I will get the table with 200 rows x 1000 columns. How can I do this without copying .txt files one by one into columns?
Maybe concatenate all the .txt files together in a command line (you didn’t mention your OS to propose a command line), and then import that one resulting file?
Thanks, I am using Ubuntu 18.04.4
You may try
cat *.txt > /path/to/output.txt
and then load that output.txt.
Concatenation will give one column with 200000 rows.
If your files are consistently 200 rows each, it is easy to extract to columns by INDEX() function.
Assuming imported data is in sheet1:
- In a new sheet, enter in cell A1:
=INDEX($Sheet1.$A$1:$A$200000;(COLUMN()-1)*200+ROW();1) - Copy cell A1
- Select range
A1:ALU200
Type range spec. into the selection box, far left from the formula bar. - Paste
If your files display some variation in row count, you need something different.
Oh my bad! Sorry for being totally distracted.
You also may transpose the data:
- Replace
\non .txt with a safe character (depends on your data; maybe\t?); - Concatenate the result into a new .txt;
- Import that .txt into a spreadsheet;
- Select all, cut, paste special, check
Transpose.
Use the paste command, paste *.txt >output.tsv which merges lines of files into a tab separated values file that you can import. See man paste. Using .tsv file name extension here to automatically trigger the CSV/TSV import.
Thank you very much!
Brilliant! I never knew about the paste command. I learned something today. Thanks!