将大型.csv文件导入Google表格时出现问题 [英] Problem importing a large .csv file into Google Sheets
问题描述
我已经基于一个GoogleSheets文件在Google DataStudio中设计了一个仪表板。进入GoogleSheets文件的数据来自两个以;分隔的.csv文件。这些.csv文件会在夜间自动更新，并存储在GoogleDrive中。为了使DashBoard也可以根据.csv文件中的更改自动更新，我需要将它们自动读入GoogleSheets文件，这是通过触发脚本在.csvs更新后每晚运行来实现的...
I have designed a Dashboard in Google DataStudio based on one GoogleSheets file. The data that goes into the GoogleSheets file comes from two .csv files seperated by ";". These .csv files are automatically updated at night and stored on the GoogleDrive. In order for my DashBoard to also automatically update based on the changes in the .csv files I need to automatically read them into the GoogleSheets file, which I do by triggering my script to run every night after the .csvs have gotten updated...
除了一些基本知识外，我真的不知道如何编码，因此我从互联网上的各种来源构建了以下脚本。很好的是，它基本上可以正常工作：
As I do not really know how to code besides from some basics I build the following script from various sources throughout the internet. The nice thing is, that it basically works:
function parseCsv(csvString, delimiter) {
var sanitizedString = csvString.replace(/(["'])(?:(?=(\\?))\2[\s\S])*?\1/g, function(e){return e.replace(/\r?\n|\r/g, ' ') });
return Utilities.parseCsv(sanitizedString, delimiter)
}
//------------------------------------------------------------
function import_Sales() {
var fileName = "exported_Sales.csv";
var searchTerm = "title = '"+fileName+"'";
var files = DriveApp.searchFiles(searchTerm)
var csvFile = "";
while (files.hasNext()) {
var file = files.next();
if (file.getName() == fileName) {
csvFile = file.getBlob().getDataAsString('ISO-8859-15');
break;
}
}
var csvData = parseCsv(csvFile,";");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('ExportSales');
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
如上所述，我正在导入2个不同的.csv文件（因此没有
As already mentioned I am importing 2 different .csv files (so there is not only the import_sales function but also an import_stock funtion.
对于股票csv，一切工作正常（看起来与上面的import_Sales（）函数完全一样，当然还有名称） ）。
For the stocks csv everything works really fine (it looks exactly like the import_Sales() function above, besides the names of course).
问题似乎出在我的sales..csv文件的大小上（23列x 56.000行），并且每天晚上该文件越来越长因此，当尝试在几分钟后运行import_Sales函数时，出现错误最大执行时间 ...因此，我认为这与.csv的大小或功能（希望）有关。效率低下，也许你们中的某个人知道它如何运行得更快？.csv的大小无法更改，而且我无法想象不可能将其导入到Google Sheets中！!
The problem seems to be the size of my sales-.csv file. (23 colums x 56.000 rows) and the file is getting longer every night when it is being updated. So when trying to run the import_Sales function after some minutes i get the error "maximum execution time"... So i get that this must have something to do with the size of the .csv or the funtion (hopefully) being inefficient and maybe someone of you has an idea how it could run faster? The size of the .csv can not be changed and i can't imagine that it shall be impossible to import it to google Sheets?!
让任何人知道如何管理它以从csv中获取数据每晚自动进入Google表格吗？也许我可以跳过Google表格文件中已经存在的行，而只是以某种方式从cvs中导入新行？但这绝对是我的学业了，所以我很高兴你们能够帮助我！
Has anyone an idea how i can manage it to get the data from the csv into google sheets automatically every night? Maybe i could skip the row that are already in the google sheets file and just import the new lines from the cvs somehow? But thats were my knowledge comes to an end definitely, so i would be glad i you guys were able to help me!
感谢&
推荐答案
如果它已经在云端硬盘中，请尝试复制CSV并将其转换为表格，而不是手动获取从csv到现有工作表的单元格。
If it’s already in your Drive, try copying the CSV and converting it to Sheets, instead of "manually" taking the cells from csv to an existing sheet.
function main(){
var files = DriveApp.searchFiles('title contains "your csv file name"').next();
var name = files.getName();
var file_id = files.getId();
var file_Blob = files.getBlob();
var newFile = { title : name+'_Sheet',
key : file_id};
files = Drive.Files.insert(newFile,file_Blob, {convert: true});
}
我还附上文档。
这篇关于将大型.csv文件导入Google表格时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
