topic Re: Imports qvd into SQL Server in QlikView

Imports qvd into SQL Server

Thu, 23 Jul 2015 09:08:49 GMT

Hello;

I need to import a qvd file into a SQL Server database.

It's possible?

I know there are programs that do it, but I ask for the way to do it without an external program.

Thanks,

Fernando

Re: Imports qvd into SQL Server

manojkulkarni — Thu, 23 Jul 2015 09:50:05 GMT

refer SQL Writeback from QlikView extension object.

Re: Imports qvd into SQL Server

ashfaq_haseeb — Thu, 23 Jul 2015 09:55:16 GMT

You can try this code as well

ODBC Connect to TransactionDatabase;

//Load from qvd file

TABLE:

LOAD transaction_date,

location,

item,

site

FROM

[Data.qvd](qvd);

// Getting number of records in Table

LET V_ROWS = NoOfRows('TABLE');

// Loop through the records and insert into Database table

FOR V_ROW = 0 TO V_ROWS - 1

LET V_FIELD1 = Peek('transaction_date',V_ROW);

LET V_FIELD2 = Peek('location',V_ROW);

LET V_FIELD3 = Peek('item',V_ROW);

LET V_FIELD4 = Peek('site',V_ROW);

SQL INSERT INTO dbo.temp_Table(transaction_date, location,item, site) VALUES('$(V_FIELD1)','$(V_FIELD2)', '$(V_FIELD3)','$(V_FIELD4)');

NEXT;

Hope it helps

Regards

ASHFAQ

Re: Imports qvd into SQL Server

Thu, 23 Jul 2015 10:53:07 GMT

Thanks Manoj

I agry this solution, but was looking for a simpler solution.

Ashfaq gave me a most simple to the solution I was looking for.

Thanks a lot

Re: Imports qvd into SQL Server

Thu, 23 Jul 2015 10:54:25 GMT

Thanks ASHFAQ;

Is exactly what I was looking for

Thanks a lot

Re: Imports qvd into SQL Server

dclark0699 — Thu, 23 Jul 2015 11:10:54 GMT

You can also, with the proper configuration on the SQL Server end and access set up to the file share, use the SQL BULK INSERT command from Qlikview after storing a QVD as a text file. This would likely perform better than looping and inserting one row at a time depending on how many rows are in your QVD.

STORE QV_TABLE_NAME INTO file.txt(txt);

SQL DELETE FROM sql_table_name;

SQL

BULK INSERT sql_table_name

FROM 'file.txt' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2);

Re: Imports qvd into SQL Server

Fri, 24 Jul 2015 12:47:44 GMT

Hi Donnie;

Is a good idea but I don't want to create any file extra.

Thanks

Re: Imports qvd into SQL Server

arieidel — Tue, 06 Dec 2016 16:25:13 GMT

Hello Donnie,

I'm getting a script error because the txt file wasn't found. I create it just a few lines before the SQL BULK INSERT. I see the file created but somehow it isn't found by the QV script.

Do you know why? May I need to clear caché or something like that?

Thanks,

Ariel

Re: Imports qvd into SQL Server

dclark0699 — Tue, 06 Dec 2016 16:31:58 GMT

Hi Ari,

Is it a QV error, or is it a SQL Sever error? The SQL Server will need to have access to the fileshare where the file is in order to do the Bulk Insert. That was kicking back a file not found or access denied error for me when I did this initially.

Re: Imports qvd into SQL Server

arieidel — Tue, 06 Dec 2016 17:40:54 GMT

It was a SQL Server error. I've just fix it with the QvWorkPath system variable to indicate where the txt file is located. Thanks!

Then, I got several different error messages but they were due to data type or number of columns issues. I also fixed that and now it worked great. I also added the ROWTERMINATOR = '0x0A' parameter inside the "WITH" section.

Thanks again!

Ariel

Re: Imports qvd into SQL Server

roledens — Tue, 20 Nov 2018 14:45:54 GMT

Hi,

I tried this solution to insert data to MySQL database but I am getting below error. data is inserting to MySQL database but each insertion it throws this error.

My code as follows

LET V_ROWS = NoOfRows('table');

FOR V_ROW = 0 TO V_ROWS - 1

LET V_FIELD4 = Peek('Impact',V_ROW,'table');

LET V_FIELD5 = Peek('Urgency',V_ROW,'table');

LET V_FIELD6 = Peek('Priority',V_ROW,'table');

SQL INSERT INTO kedb.incidents(Impact,Urgency,Priority) VALUES('$(V_FIELD4)','$(V_FIELD5)','$(V_FIELD6)');

NEXT;