We had a new business requirement for our team to create a new source for a CSV through Qlik Replicate.<\/p>\n\n\n\n
OK – simple enough; create the Source Connector and define the file’s schema in the table section of the connector.<\/p>\n\n\n\n
But the file was 250 columns wide.<\/p>\n\n\n\n
I had nightmares before of defining file schemas before in programs like SSIS where one little mistake can lead to hours of debugging.<\/p>\n\n\n\n
Luckily we knew the source of the CSV file was an output from a view of a database; so the first part of the problem was solved – we had the schema. Now to import the schema into a Qlik Replicate task.<\/p>\n\n\n\n
Knowing the view definition; a simple python script can convert it to json.<\/p>\n\n\n
\nimport csv\nimport json\n\nif __name__ == '__main__':\n\u00a0 final_dic = {}\n\u00a0 source_file = "FileDef.txt"\n\u00a0 field_array = []\n\n\u00a0 \u00a0 with open(source_file, 'r') as f:\n\u00a0 \u00a0 \u00a0 reader = csv.reader(f, delimiter='t')\n\u00a0 \u00a0 \u00a0 schema_items = list(reader)\n\n\u00a0 \u00a0 \u00a0 \u00a0 for item in schema_items:\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item = {}\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["name"] = item[0]\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["nullable"] = True\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_string = item[1]\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if item[1].find("NUMBER") != -1:\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["type"] = "kAR_DATA_TYPE_NUMERIC"\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 tokens = focus_string[focus_string.find("(") + 1: -1].split(",")\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 precision = tokens[0]\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["precision"] = int(precision)\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if len(tokens) == 2:\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 scale = tokens[1]\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["scale"] = int(scale)\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 elif item[1].find("VARCHAR2") != -1: \u00a0#VARCHAR2\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["type"] = "kAR_DATA_TYPE_STR"\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 length = focus_string[focus_string.find("(") + 1 : focus_string.find("BYTE") - 1]\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["length"] = int(length)\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 elif item[1].find("DATE") != -1:\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 focus_item["type"] = "kAR_DATA_TYPE_TIMESTAMP"\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 field_array.append(focus_item)\n\n\u00a0 \u00a0 \u00a0 \u00a0 columns = {}\n\u00a0 \u00a0 \u00a0 columns["columns"] = field_array\n\n\u00a0 \u00a0 f = open("out.json", "w")\n\u00a0 f.write(json.dumps(columns, indent=4))\n\u00a0 f.close()\n<\/pre><\/div>\n\n\nFileDef.txt<\/h4>\n\n\n\n
The following text file contains the schema of the view with the table delimited columns of:<\/p>\n\n\n\n
UDF_VARCHAR1 VARCHAR2(200 BYTE) Yes 1
UDF_VARCHAR2 VARCHAR2(200 BYTE) Yes 2
UDF_VARCHAR3 VARCHAR2(200 BYTE) Yes 3
UDF_NUMERIC1 NUMBER(10,6) Yes 4
UDF_NUMERIC2 NUMBER(10,6) Yes 5
UDF_NUMERIC3 NUMBER(10,6) Yes 6
UDF_INT1 NUMBER(10,0) Yes 7
UDF_INT2 NUMBER(10,0) Yes 8
UDF_INT3 NUMBER(10,0) Yes 9
UDF_DATE1 DATE Yes 10
UDF_DATE2 DATE Yes 11
UDF_DATE3 DATE Yes 12<\/pre>\n\n\n\nThis particular view definition is from Oracle so uses oracle data types; but it would be simple to change the code over to use a definition from a MS SQL database or other source.<\/p>\n\n\n\n
Sticking it all back together<\/h3>\n\n\n\n
\n
- Open up the exported task file json<\/li>\n\n\n\n
- Find the dummy field that you created in the table definition<\/li>\n\n\n\n
- Overwrite the dummy field with the json created in the script file<\/li>\n\n\n\n
- Save and reimport the job.<\/li>\n<\/ol>\n\n\n\n
If all goes well; the File source connector will be overwritten with the full table definition.<\/p>\n","protected":false},"excerpt":{"rendered":"
A wide problem We had a new business requirement for our team to create a new source for a CSV through Qlik Replicate. OK – simple...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,16],"tags":[],"class_list":["post-833","post","type-post","status-publish","format-standard","hentry","category-code","category-qlik-replicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/833","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=833"}],"version-history":[{"count":5,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/833\/revisions"}],"predecessor-version":[{"id":1446,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/833\/revisions\/1446"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=833"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=833"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=833"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}