{"id":833,"date":"2022-07-29T10:44:26","date_gmt":"2022-07-29T10:44:26","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=833"},"modified":"2024-07-10T02:08:53","modified_gmt":"2024-07-10T02:08:53","slug":"qlik-replicate-the-wide-text-file-problem","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=833","title":{"rendered":"Qlik Replicate – The Wide Text file problem"},"content":{"rendered":"\n

A wide problem<\/h3>\n\n\n\n

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

A stroke of Luck<\/h3>\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

    \n
  1. Create a new File Source connector and prefill as much known information as you can<\/li>\n\n\n\n
  2. In the Tables definition of the File Source; add a dummy field<\/li>\n\n\n\n
  3. Create a NULL Target connector<\/li>\n\n\n\n
  4. Create a dummy task with your new file source and the Null target and save it<\/li>\n\n\n\n
  5. Export task with End Points<\/em> to save out the json definition of the task<\/li>\n<\/ol>\n\n\n\n

    Creating the table definition in JSON<\/h3>\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\n
    FileDef.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
      \n
    • Column Name<\/li>\n\n\n\n
    • Data type<\/li>\n\n\n\n
    • Nullable<\/li>\n\n\n\n
    • Column order<\/li>\n<\/ul>\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\n
      This 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
      1. Open up the exported task file json<\/li>\n\n\n\n
      2. Find the dummy field that you created in the table definition<\/li>\n\n\n\n
      3. Overwrite the dummy field with the json created in the script file<\/li>\n\n\n\n
      4. 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":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t