Using 'tab' separator in synchro_import

[Marc Franquesa]

Hi,

I modified my code to use 'tab' character as CSV separator as my data contains commas. So when calling synchro_import I set the 'separator' argument to the tab character.

Debugging the API call I can confirm that separator is set to '\t' and the data is properly splitted by '\t'. Even iTop seems to handle that (I don't receive the error telling that separator must be 1 character long).

However the HTML response (that's another issue/feature request: REST APIs return JSON, csv import returns text and synchro_import returns HTML shouldn't be this homogenized to return the same response format?) spits:

#Unknown column 'primary_key dbserver_id name description org_id' (class: 'DatabaseSchema')

So seems that iTop doesn't properly split the header/fields by tab when calling the SQL backend, and pass the entire line as a single-field.

Regards

[Pierre Goiffon]

Hello,
Not sure to understand exactly what you did ?
Can you give a step by step guide on how to reproduce please ?

[Marc Franquesa]

Basically I'm trying to load a CSV as a SynchroDataSource thru synchro_import API.

I have a script which generates a CSV (comma separated, quoted by double quotes) to be used as a Synchro DataSource and then an ansible playbook which loads the data and calls synchro_import

The ansible playbook does:
- Loads the generated CSV (ansible read_csv module generates a dict)
- Transforms de loaded data into a list (rows) of lists (field-values)
- Calls synchro_import API with the loaded data

The last task is problematic one:

    - name: Synchro DataSet replicas
      when:
        - not ansible_check_mode
      uri:
        url: "{{ itop_url }}/synchro/synchro_import.php"
        method: POST
        timeout: 360
        body_format: form-urlencoded
        return_content: true
        body:
          data_source_id: "{{ itop_synchro_ids[ itop_synchro_prefix + ' ' + item.class ] }}"
          separator: ","
          text_qualifier: '"'
          synchronize: 0
          auth_user: "{{ itop_user }}"
          auth_pwd: "{{ itop_password }}"
          csvdata: "{{ ([ item.headers ] + item.data) | map('join',',') | list | join('\n') }}"
      loop: "{{ dataset }}"
      register: itop_response

The above code works fine as long as the data (fields) don't contain any commas. But my data may contain commas, and in that case the SQL INSERT fails with invalid numer of columns (obviosuly)

If I change the above code to use 'tab' as separator simply by changing the lines:

    separator: "    " # literal tab
    csvdata: "{{ ([ item.headers ] + item.data) | map('join','\t') | list | join('\n') }}"    # Use '\t' to separate the fields

Then iTop synchro_import HTML response is:

Unknown column 'primary_key dbserver_id name description org_id' (class: 'DatabaseSchema')

Note that the space between fields is indeed a tab, so seems iTop didn't properly build the SQL sentence

[Marc Franquesa]

For better test I reproduced the issue using plain curl.

I attach the JSON DataSource definition of the SynchroDataSource, although any existing SynchroDataSource should work as long as the CSV file provided matches the class.

Setting
SDS_ID= to the DataSource ID created
ITOP_URL=
ITOP_USER=
ITOP_PASS=
CSV_FILE=

Run (note that separator is a literal tab, not spaces):

curl \
    -v \
    -X POST \
    -o sync-output.html \
    --trace-ascii sync-http.txt \
    --data-urlencode "auth_user=$ITOP_USER" \
    --data-urlencode "auth_pwd=$ITOP_PASS" \
    --data-urlencode "data_source_id=$SDS_ID" \
    --data-urlencode "separator=    " \
    --data-urlencode 'text_qualifier="' \
    --data-urlencode "synchronize=0" \
    --data-ascii "csvdata=$(cat $CSV_FILE)" \
    "$ITOP_URL/synchro/synchro_import.php"

On sync-http.txt you will clearly see that the request pass 'tab' properly encoded as %09. The response is an HTTP/200 OK
The HTML content response from iTop:

Unknown column 'primary_key dbserver_id name description org_id' (class: 'DatabaseSchema')

So seems is passing the tab-separated data, but not properly encoding it for the SQL query.

[Marc Franquesa]

As a side note I obviously tested with other rare chars as separators '#,@', ... all work fine except for 'tab'. Currently I use this as workaround (as my data contains , but no #), but I think properly supporting tab (which is common an rare to be inside the data) would be good.

[Vincent @ Combodo]

Have you tried to type explicitly 'tab' as the separator, rather than the character itself (or maybe "\t")?

[Marc Franquesa]

If I use '\t' if fails with same result, But using literal 'tab' string seems it worked. Thanks!

(On my initial tests I got sometimes an error saying that only one-char separator was allowed, so I didn't ever tried with 'tab' literally).

Thanks much for pointing, I could checked the source to find that 'tab' was to be specified literally.