Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Import File containing field (US) and Row (RS) separator

Hi,

my txt file contains both field (US) and row (RS) separators:

Example.PNG

I can only define one separator within the wizard for importing tables:

Example_3.PNG

If I choose US (\x1f), the load script generates an error (field not found) for the last column (Column 3), because it cannot resolve the "RS" separator.

Example_2.PNG

Example_4.PNG

How can I import this file to QV? Is it possible to define both field and row separators within the table import wizard?


Many thanks,

Paul

1 Solution

Accepted Solutions
marcus_sommer

Like previous mentioned you might need a load-chain. Maybe by loading the file at first without any delimiter with fixed lenghts and on this happens some transformations, maybe something like: replace(@1, '  ', ' ') or something similar. After that you stored the table as txt-file again and load it normally with a delimiter.

Such transformations could be quite difficult if simply measure like this replace didn't work - therefore the easiest way might be to change the source (maybe a different output format like xml could be useful).

- Marcus

View solution in original post

9 Replies
sunny_talwar

Would you be able to share the sample text file so that we can test this at our end?

Anonymous
Not applicable
Author

Sure, here you go. Please note that I can only see this special kind of separators using Notepad++. Also, I have done some more tests in the meantime and it seems that the error is occurring due to the column names, not the data itself.

marcus_sommer

You couldn't apply two delimiters at the same time. This meant you might need a load-process with several chained loadings. What happens if you simply load: Load * From ... with and without embedded labels?

- Marcus

Anonymous
Not applicable
Author

Marcus, thanks for your help. Load * from is working even with embedded levels. As you can see in the screenshot, QlikView is creating automatically a large space after the column name "Column 3" in order to resolve the RS tag. I tried to copy the label including the space to the QV load script, but it's not working.

Unbenannt.PNG

antoniotiman
Master III
Master III

Hi Paul,

try this

LOAD Column1,
Column2,
Column3
FROM
File
(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);

Regards,

Antonio

Anonymous
Not applicable
Author

Nice - that's working. Many thanks

Anonymous
Not applicable
Author

However, this solution only works if there are no spaces in the data. If there are spaces, the result is wrong.

Unbenannt.PNG

Unbenannt.PNG

marcus_sommer

Like previous mentioned you might need a load-chain. Maybe by loading the file at first without any delimiter with fixed lenghts and on this happens some transformations, maybe something like: replace(@1, '  ', ' ') or something similar. After that you stored the table as txt-file again and load it normally with a delimiter.

Such transformations could be quite difficult if simply measure like this replace didn't work - therefore the easiest way might be to change the source (maybe a different output format like xml could be useful).

- Marcus

Anonymous
Not applicable
Author

Thanks!