question How to load .txt file in data source and transform it please any help is appreciated in Developer forum

How to load .txt file in data source and transform it please any help is appreciated

ABHAY_M — Fri, 23 Oct 2020 07:56:18 GMT

this is the sample data of text file first coloumn represents location field and second column asset tag how to load this data in data source if parsing then how do i parse with script

Thank you

Re: How to load .txt file in data source and transform it please any help is appreciated

Hitoshi Ozawa — Fri, 23 Oct 2020 08:04:15 GMT

Follow the steps provided in the following page.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/import-sets/task/t_ProcessingCustomCSVFiles.html

Re: How to load .txt file in data source and transform it please any help is appreciated

Ankur Bawiskar — Fri, 23 Oct 2020 08:18:54 GMT

Hi,

txt file is not supported out of the box.

possibly you will have to write custom script to get the txt file content and then transform it

Regards
Ankur

Re: How to load .txt file in data source and transform it please any help is appreciated

ABHAY_M — Fri, 23 Oct 2020 08:32:08 GMT

i have added image of the above sample data how do i tranform it unable to figure out

Re: How to load .txt file in data source and transform it please any help is appreciated

Ankur Bawiskar — Fri, 23 Oct 2020 11:33:44 GMT

Hi,

I would suggest to use CSV file

Regards
Ankur

Re: How to load .txt file in data source and transform it please any help is appreciated

ABHAY_M — Fri, 23 Oct 2020 12:01:38 GMT

i have tried with csv file but unable use multiple delimiter as it is saperated by tab and '|' i just cannot import it

Re: How to load .txt file in data source and transform it please any help is appreciated

Ankur Bawiskar — Fri, 23 Oct 2020 12:22:09 GMT

Hi,

you should be able to use the delimiter as pipe

tab character is not shown

So it would be difficult to load

Regards
Ankur

Re: How to load .txt file in data source and transform it please any help is appreciated

Jon23 — Fri, 23 Oct 2020 23:38:21 GMT

One approach is to use the 'Custom (Parse by Script)' format for a data source.

Based on the file you provided, the following parsing script can be used in the data source:

Parsing Script

// The input value can be accessed through the variables named "line", "lineNumber" and "result"
// The function uses result variable to return parse result back. 
(function(line, lineNumber, result) {

    var map = {};
    var items = line.split("|");
    map['location'] = items[0];
    map['asset'] = items[1];
    result.addRow(map);

})(line, lineNumber, result);

Data Source Record

Re: How to load .txt file in data source and transform it please any help is appreciated

ABHAY_M — Sat, 24 Oct 2020 14:44:57 GMT

Thank you very much @jwalton i searched it like anything over the internet for servicenow but i couldnt found it thank you very much wish You all the best

Re: How to load .txt file in data source and transform it please any help is appreciated

VaranAwesomenow — Fri, 20 Jan 2023 07:18:41 GMT

@Jon23 Thanks for sharing this, adding to your script I have utilized this concept to parse midserver logs.

Since its in .txt format I wanted a way to convert that to data source and import set table to better analyze the data and I didnt want to manually convert it to csv or touch the file, below is the script I created to achieve that.

var map = {};
var items = line.split(" ");
map['datetime'] = items[0];
var items1 = line.split(" ");
map['level'] = items1[0];
map['message'] = items1[1];
result.addRow(map);

})(line, lineNumber, result);

Re: How to load .txt file in data source and transform it please any help is appreciated

Xhare — Fri, 13 Sep 2024 08:59:29 GMT

Hi @Jon23 ,

Do you have idea, on what to use if I will retrieve the data start from row 15. My column header starts in row 14.

Re: How to load .txt file in data source and transform it please any help is appreciated

Jon23 — Fri, 13 Sep 2024 12:24:39 GMT

Hi @Xhare

You should be able to wrap the code with an 'if' and not start processing until the lineNumber is reached. For example:

// The input value can be accessed through the variables named "line", "lineNumber" and "result" // The function uses result variable to return parse result back. (function(line, lineNumber, result) { if (lineNumber > 13) { // <<<lineNumber starts at 0 var map = {}; var items = line.split(","); map['UserName'] = items[0]; map['LastName'] = items[1]; map['FirstName'] = items[2]; map['MiddleName'] = items[3]; map['Organization'] = items[4]; result.addRow(map); } })(line, lineNumber, result);

Re: How to load .txt file in data source and transform it please any help is appreciated

Xhare — Tue, 17 Sep 2024 07:09:53 GMT

Hi @Jon23 ,

I'm using below script, but still getting data from row 1 until the end of row.

(function(line, lineNumber, result) {

if (lineNumber > 14) {

var headers = gs.getProperty("ccc.ftp.log.extract.headers").split("|");

var items = line.replace(/[^\x20-\x7E]/g, '').split(",");

var map = {};

for (var i = 0; i < items.length; i++) {

map[headers[i]] = items[i];

}

result.addRow(map);

}

})(line, lineNumber, result);

Re: How to load .txt file in data source and transform it please any help is appreciated

Jon23 — Fri, 04 Oct 2024 18:29:09 GMT

@Xhare

Can you check your end of line (EOL) characters on the file?

Depending on the system creating the file it could be a line feed which may be causing the issue. If it is, try converting EOL to carriage return/line feed.