topic Re: Transformation txt file in Connectivity & Data Prep

Transformation txt file

benjamin_naujoks — Fri, 01 Jun 2018 10:02:16 GMT

Hey Hey Community,

I have a problem with a txt file which is an export from SAP.

The text file looks very strange when I open it with the text editor because there are many blanks between the values. When I open it with notepad++ it looks like a normal table and is how I wanted the table to be.

I want to import the file into Qlik Sense but there is no common rule for a delimiter (sometimes it is a blank, somtimes there are 5 blanks etc.) and because of that, the data slips into different and implausibly fields.

Is there any option to transform the txt file into an file which looks like the table in notepad++?

Thanks in advance

Benny

Re: Transformation txt file

rwunderlich — Sun, 03 Jun 2018 18:53:39 GMT

It sounds like your delimiter may be a tab character which your first text edit expands and NotePad++ does not. You can confirm in NotePad++ by menu View, Show Symbol, Show All Characters.

If it's a tab than specify that as your delimiter when you load in Qlik Sense.

-Rob

Re: Transformation txt file

benjamin_naujoks — Sun, 03 Jun 2018 19:24:41 GMT

Hej Rob,

unfortunately it is no tab character. I tried every delimiter which is possible to choose but I couldn't get the table like I wanted it to have.
I found out that you can use " fixed record size" to make the data in a table, but then my headlines did not match to the columns.

I think there must be an easier way, especially because it's an exported file from SAP

Greetz

Benny

Re: Transformation txt file

dwforest — Wed, 06 Jun 2018 20:08:33 GMT

Is it using fixed width fields? Select Fixed record for the File format in Qlik.

Re: Transformation txt file

marcus_sommer — Thu, 07 Jun 2018 08:03:11 GMT

Probably you need a multi-step approach by loading it at first with fix-records and performing then various cleaning- and replacing-measures. Here an example which might give you some ideas how you could resolve it: Re: Loading File with Blank Values and Space delimited.

- Marcus