#!/usr/bin/env python
# coding: utf-8
# In[24]:
## chunking data from csv and exporting to a QuickBase app
import pandas as pd
import requests
import xml.etree.ElementTree as ET
import io
import numpy as np
mydata = pd.read_csv('Downloads/sample.csv')
step = 25000 # if you have many columns, you can change this to a lower number
# these 2 functions handle chunking of imports if you have lots of data
def get_lines(mydata, start_line, step=step):
end_line = start_line+step
csv_lines = mydata.iloc[start_line:end_line,:].to_csv(index=False, header=False)
return csv_lines
def find_between( s, first, last ):
try:
start = s.index( first ) + len( first )
end = s.index( last, start )
return s[start:end]
except ValueError:
return ""
# replace these variables with your own!!
usertoken = 'kdr_uyp_zq32r7ca9d3djby3jbfjqucjyk'
dbid = 'bqjayd9hy'
# finds the field ids for your column names.
def clistFromColumns(mydata):
headers = {'Content-Type': 'application/xml', 'QUICKBASE-ACTION': 'api_getschema'}
data = '<qdbapi><usertoken>' + usertoken + '</usertoken></qdbapi>'
schema = requests.post(url='https://team.quickbase.com/db/' + dbid, data=data.encode('utf-8'), headers=headers)
root = ET.fromstring(schema.content)
dict_labelfid = {}
for field in root.iter('field'):
label = field.find('label').text
fid = field.attrib['id']
dict_labelfid[label] = fid
list_fieldlist = []
for col in mydata.columns:
if col in dict_labelfid:
list_fieldlist.append(dict_labelfid[col])
else:
print(col + ' field not found.')
return
field_list = '.'.join(str(i) for i in list_fieldlist)
return field_list
field_list = clistFromColumns(mydata)
dflength = len(mydata.index)
iter_np = np.arange(0, dflength, step)
iter = list(iter_np)
req_total = int(np.ceil(dflength / step))
req_nr = 1
headers = {'Content-Type': 'application/xml', 'QUICKBASE-ACTION': 'api_importfromcsv'}
for i in iter :
mystr = get_lines(mydata, i)
print('Sending API request ' + str(req_nr) + ' out of ' + str(req_total))
data = '<qdbapi><records_csv><![CDATA[' + mystr + ']]></records_csv><clist>'+field_list+'</clist>\
<usertoken>' + usertoken + '</usertoken></qdbapi>'
r2 = requests.post(url='https://team.quickbase.com/db/' + dbid, data=data.encode('utf-8'), headers=headers)
root = ET.fromstring(r2.content)
errtext = root.find('errtext').text
print(errtext)
recs_input = root.find('num_recs_input').text
print(recs_input + ' records input.')
recs_added = root.find('num_recs_added').text
print(recs_added + ' records added.')
try:
recs_updated = root.find('num_recs_updated').text
print(recs_updated + ' records updated.')
except:
print('0 records updated.')
req_nr += 1