Imports qvd into SQL Server

[#1]

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

[manojkulkarni]

refer SQL Writeback from QlikView extension object.

[ashfaq_haseeb · Accepted solution]

Hi

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

[#4]

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

[#5]

Thanks ASHFAQ;

Is exactly what I was looking for

Thanks a lot

[dclark0699]

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);

[#7]

Hi Donnie;

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

Thanks

[arieidel]

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

[dclark0699]

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.

[arieidel]

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