Has anybody tested the Remote File Import using a CSV with a non-comma separator (using pipe)?

[treidfrb]

The Remote File Import does upload the CSV file from the mid server to a Data Source.  Even when pointing the Remote File Import to an existing import set load table with an existing transform map, the uploaded data source configuration does not retain the custom separator value.  It would seem I need to use another file format like XML or JSON to overcome this issue.  We have tested a manual import set configuration, with the delimiter set to pipe, and the import set table load and transform work fine.  We would like to automate the upload and transform from the mid-server file in a schedule, but the nature of the CSV will not retain the custom delimiter when it attempts to load the import table, so it fails to successfully populate the import set table and does not attempt the transform.  Thank you.

[denis hoffmann]

Hi,

Does the Remote file import create a new Data source record  in sys_data_source?

if so , the first upload will fail his transformation due to default settings, but after you set your custom delimiter in"CSV delimiter" field of this new Data source it should be ok.

Regards,
Denis.

[treidfrb]

Hi Denis,

Thanks for your reply.  I have tested this with two separate and working import sets that run manually without issue.  When the Remote File Import runs, it creates a new data source, which implies it is creating a new import table.  This is the root of the issue.  If it's using a new import set table, it has not respected the import set table specified in the Remote File Import configuration record.  Thus, this is the behavior I have recorded:

1) run import manually per Remote File Import documentation

2) configure a Remote File Import record, referencing the import set table created in 1)

3) running the Remote File Import fails with the following message, due to the non-standard file format, it is expecting a CSV, specifically a comma separator.

message="Expecting , in position 19"

4) take the newly created data source with the file copied from the mid-server, add the appropriate delimiter, and run again to create a new import set table.

message="data contains 1 columns, expected 15"

I am planning to test Remote File Import using a JSON file.

[denis hoffmann]

All right,
also did you check if your CSV file on the Remote server isn't saved with BOM option ?
This Byte Order Mark was an invisible cause of trouble with imports, like in this case : https://community.servicenow.com/community?id=community_question&sys_id=881950dedb245b04fc5b7a9e0f96...

Regards,
Denis

[sr82]

Hi, did you eventually try pulling JSON file and managed to get it working? I have similar requirement to pull a JSON file, while I can get the manual load of the file to  data source (format of Data Source set to JSON) working I dont see the file been pulled from MidServer.