Keboola WIKI‎ > ‎Recipes‎ > ‎

MS-Dos batch script for uploading multiple csv tables into storage api

by giannetti.giuliano@keboola.com
--

This script will allow you to take all tables from a single folder in your computer and upload them to Keboola Connection. 

Prerequisites:

1) having the Keboola SAPI client installed on your computer (PC: 05 - Windows executable SAPI client, Linux/Mac: https://github.com/keboola/storage-api-cli)
2) have multiple valid csv tables that you want to create or update in your project in keboola in a folder on your PC
3) have an access token with the permission to write within the selected folder in KBC
4) the tables have the same name as is desired to have in KBC
6) All the tables should have the delimiter set to  ,   and the enclosure to "" (in case this is an issue see the solution using Curl below)

Create tables:

Using cmd run the following script from the same folder as where are the tables that should be imported into KBC

FOR %A in (*.csv) DO sapi-client create-table in.c-target_bucket --primary-key "id" %~nA %A -t "your-token"
the script will load all the tables within the folder and recreate them in the in.c-target_bucket folder in KBC

the 
%A operator returns the filepath for the files in the folder
%~nA returns the respective file's name without prefixes and suffixes
--primary-key sets the key for incremental uploads (see below)

Update tables:

Once the tables are created you can update them by using this script

FOR %A in (*.csv) DO sapi-client write-table in.c-target_bucket.%~nA %A -t "your-token" -i
Note the two differences: this script will take the table and upload it to the in.c-taget_bucket.FILENAME table in KBC. 

Possible options:
-i: will  load the files incrementally. It will fail if the table has not a primary key assigned in KBC.

Disclamer - handing errors

Keboola takes no account for the MS-DOS script running in the client's environment. The administrator should set automatic warnings to be send on his email in case of failure. 
Typically the errors are caused by different configuration of csv files in the FOR cycle, different delimiters, unvalid csv formats etc. See the command log for details.

Create an executable 
To create an executable .bat file just copy the script to a .txt file and change the suffix to .bat. Do not forget to change all the '%'  in the file to '%%' 
Run it from the same folder as where the files are located.

Known issues: 
Some standard functions and setting from the Keboola API may be missing in that case use the solution below.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Uploading files using a CURL script (Deprecated)

by tomas.trnka@keboola.com
---

prerequisites:
1) You are running windows and you have a curl installed (http://curl.haxx.se/)
2) You have multiple tables in your project you would like to UPDATE
3) You have a folder on your PC that contains the tables with the bucket.table.csv names (e.g. the table on your harddrive is called in.c-main.table.csv)

Here is the simple code, run the cURL from the same folder:

FOR %A IN (in.c-main.*) DO curl -H "X-StorageApi-Token:YOUR_TOKEN" -F "tableId=%A" -F "data=@%A" "https://syrup.keboola.com/sapi-importer/run"

pro tip: If you want to process it as a windows batch file, double all "%"

have fun

Comments