I have the folder of txt files saved in both google drive and on my desktop. So far I can get power query to work with a small sample of files but when I try to do all of them I get the error that there is too much data. I am using a Mac with Microsoft 365. One problem is that these files do not contain the same amount of data nor do they have headings. I need to get the data from each into either one cell each (that we can expand to view), one column each, or even one sheet each as long as its within the same file.

I first tried google colab with the folder saved under google drive but can only get code to read and write one file at a time. Same with python. On the mac, there is no get data from folder so I have been trying to use power query which works but only for a small sample of files and puts everything into the same column.

Using Power Query in excel w/ a mac I’ve done the steps illustrated below:

Excel > Get Data (power query) > Blank Query

let
  Source = Folder.Files("/Users/sarah/Desktop/testingtxtfolder")
in
  Source

Combine Files in power query window

*delimeter none *do not detect data types

Close & Load : The query returned more data than will fit on a worksheet

This works with a small sample but not with the folder of 350+ txt files I have, I get that more data error. Additionally this only works by putting all of the data from each file into one singular column. I’d like to be able to get each txt files data into its own column, row, or sheet if its possible.

Using Google Colab I’ve tried the below code:

from google.colab import drive
drive.mount('/content/drive')

import os

new_list = []
for root, dirs, files in os.walk("/content/drive/MyDrive/testingtxtfolder"):
    for file in files:
        if file.endswith('.txt'):
            with open(os.path.join(root, file), 'r') as f:
                text = f.read()
                new_list.append(text)

print(new_list)              

This reads and writes the data from what I can tell but I’m not sure how to get it into a spreadsheet or if that is even possible…