Ask Your Question

How can I copy a huge number of .txt files into LibreOffice Calc columns of the same sheet?

asked 2020-07-14 18:57:38 +0200

janap gravatar image

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?

edit retag flag offensive close merge delete


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?

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-14 19:00:11 +0200 )edit

Thanks, I am using Ubuntu 18.04.4

janap gravatar imagejanap ( 2020-07-14 19:04:04 +0200 )edit

You may try

cat *.txt > /path/to/output.txt

and then load that output.txt.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-14 19:17:35 +0200 )edit

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.

keme gravatar imagekeme ( 2020-07-14 22:18:43 +0200 )edit

Oh my bad! Sorry for being totally distracted.

You also may transpose the data:

  1. Replace \n on .txt with a safe character (depends on your data; maybe \t?);
  2. Concatenate the result into a new .txt;
  3. Import that .txt into a spreadsheet;
  4. Select all, cut, paste special, check Transpose.
Mike Kaganski gravatar imageMike Kaganski ( 2020-07-14 22:56:59 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-15 13:51:23 +0200

erAck gravatar image

updated 2020-07-15 13:57:41 +0200

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.

edit flag offensive delete link more


Thank you very much!

janap gravatar imagejanap ( 2020-07-15 15:20:16 +0200 )edit

Brilliant! I never knew about the paste command. I learned something today. Thanks!

keme gravatar imagekeme ( 2020-07-15 17:20:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-14 18:57:38 +0200

Seen: 109 times

Last updated: Jul 15 '20