Upload CSV Files to Google BigQuery

This Google App Script gets each file in the "ToBeUploaded" folder in Google Drive and creates a Google BigQuery load job with that CSV data.  The routine then moves the CSV file to the "Uploaded" folder.  To use this code, replace 'YOURPROJECTID', 'YOURDATASET', and 'YOURTABLE' in the code below with your values.  Also, you must add Google BigQuery to the Services your script can use or the insert job will fail.

function UploadCSV() {
tobeuploadedfolder = DriveApp.getFoldersByName("ToBeUploaded").next();
uploadedfolder = DriveApp.getFoldersByName("Uploaded").next();
files = tobeuploadedfolder.getFiles();
projectId = 'YOURPROJECTID';
datasetId = 'YOURDATASET';
tableId = 'YOURTABLE';
while (files.hasNext()){
csvfile = files.next();
data = csvfile.getBlob().setContentType('application/octet-stream');
job = {
configuration: {
load: {
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: tableId
},
skipLeadingRows: 0,
}
}
}
try {
jobResult = BigQuery.Jobs.insert(job, projectId, data);
console.log(`Load job started. Status: ${jobResult.status.state}`);
} catch (err) {
console.log('unable to insert job');
}
csvfile.moveTo(uploadedfolder);
}
}

No comments:

Post a Comment

Upload CSV Files to Google BigQuery

This Google App Script gets each file in the "ToBeUploaded" folder in Google Drive and creates a Google BigQuery load job with tha...