Assign the Include file to a Variable

[#1]

Hi There,

I thought I could assign the Include file to a variable. I did a quick test but obviously didn't look at the result properly.

LET sConnectwiseConnectionString = $(Include=$(vConnectionFilePath));

LET sConnectwiseConnectionString = Replace($(sConnectwiseConnectionString), 'CONNECT TO','');

CONNECT TO $(sConnectwiseConnectionString);

Knowing that my file that is located at $(vConnectionFilePath) contains one line like below.

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=ServerName];

I see that by using the LET syntax in the saved file it will save the connection to a variable.

e.g. LET sConnectwiseConnectionString = [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=ServerName];

then

$(Include=$(vConnectionFilePath));

As nstefaniuk pointed out to me here but I didn't read it properly. http://community.qlik.com/thread/41760

I don't have the option of changing the connection file as there are hundreds out there on various customer systems.

Is there a way of getting the text in a file into a variable in the load script?

Hope so

Thanks

[rwunderlich · Accepted solution]

temptab:

LOAD @1 as conn FROM [$(vConnectionFilePath)] (txt);

LET vConnString = peek('conn');

DROP TABLE temptab;

CONNECT TO $(vConnString);

I wasn't clear if your file contained the "CONNECT TO" portion. If it does, then you only need to put $(vConnString); on a line by itself.

-Rob

[#3]

Thanks Rob, Excellent.

For anyone needing thisI did need to add (txt, no labels) so that the @1 would work because I didn't have column headings in the file.

LOAD @1 as conn FROM [$(vConnectionFilePath)] (txt, no labels);

Thanks again Rob.