Code to import data from text files into spreadsheets

[MiroScript]

Hi, there!

I have some txt files in a local folder that I need to read line by line and import data to active spreadsheet.

In fact, this txt file has a csv format. 

I need to:

1. Read first line;
2. Get the values of some "fields" on this line;
3. Apply some format, calculations or inclusion of new data based on the values.
4. Save the final values (formated, changed...) in the spreadsheet;
   
5. Go to next line....

Can someone send a code example?

Regards,

[Alex]

Hi @MiroScript!

You need to use your local environments. The Google Apps Script work on the Google Cloud.

Try a python's or go's scripts.

Best, Alex.

[Kim Nilsson]

You can use GAMADV-XTD3 for example.

https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Spreadsheets

That will push your CSV to a Sheet (not the active sheet), and then you do all the magic there.

No need for Apps Script, as all you describe are things that can be done with formulas.

Then you use IMPORTRANGE to pull the data from your temporary/magic sheet into the active spreadsheet.

If you want the data to be formatted in som special way in your active spreadsheet then you can choose to do so either by formatting cells, or maybe use some apps script to do it dynamically (if necessary).

[Clark Lind]

If you upload all your text files into a single Drive folder, you should be able to access each file's content using filename.getAs(text/plain). 

See the answer to this question on stack exchange for more full code example.

The above will get you access to the content. Then you have to parse the content for what you need, file-by-file, line-by-line.

Because the script has a high probability of timing out before finishing, you might be better off copying the content of each txt file into a sheet column or something. Then do a second function to do your parsing.

[--Hyde]

Hi,

If this is a one-time thing and not a process that you repeat every week or so, it would probably be easiest to import the .csv files into a Google Sheet through File > Import, and as Kim suggested, process the data with spreadsheet functions such as regexreplace().

If there are many files or if this is an recurring process, you may want to look into Utilities.parseCsv().

Cheers --Hyde