Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jaryszek2
Creator
Creator

What is the difference between 2 stored csv files? Page encoding

Hi Guys,

I am trying to do Bulk Insert from *.csv created from QlikView into MS SQL Server.

I have 2 applications in QlikView:

1. Generator_Korekty:

Here cvs is working.

my code:

Max_Spolki_JG_skrót:

mapping load

PESEL,

JG_skrót

from $(HrIBSQvd)SAP1_*.qvd (qvd)

where applymap('Max_Spolki_max_data',PESEL,0)=date(Data_zwolnienia);

Map_Pesel_Ostatnia:

mapping load

PESEL,

text(Nazwisko) as Nazwisko

Resident MaxZmiana

where ApplyMap('MaxZmiana2',PESEL,0)=Ostatnia_zmiana;

Map_Pesel_OstatniaImie:

mapping load

PESEL,

text(Imię) as Imię

Resident MaxZmiana

where ApplyMap('MaxZmiana2',PESEL,0)=Ostatnia_zmiana;

Map_Oddzial:

mapping load

Schemat & '-' & KOD,

OPIS

FROM

$(HrIBSQvd)PrKOD.qvd (qvd) where RODZKOD='39';

Map_NIP_Pr:

mapping load

Schemat,

text(purgechar(WARTOSC,'-')) as NIP

FROM

$(HrIBSQvd)PrZMIENNE.qvd (qvd)

where NAZWA='k_nip';

DaneOsobowe:

LOAD

num(upper(NR_PESEL)) as PESEL,

text(upper(Schemat) & '-' & upper(NUMPRAC)) as Nr_osobowy,

text(upper(NAZWISKO))as Nazwisko,

text(upper(IMIE1)) as Imię,

applymap('Map_Oddzial',Schemat & '-' & MIEJ_ZATR,'brak') as Podobszar,

applymap('Map_NIP_Pr', Schemat,0) as Zleceniodawca_NIP,

upper(Schemat) as JG_nazwa,

upper(Schemat) as Zleceniodawca_skrót,

'PROBIT' as System

FROM

$(HrIBSQvd)PrKadry.qvd

(qvd) where DOSTEP<>'00';

Map_Nazwisko_Pr:

Mapping load

PESEL,

text(Nazwisko) as Nazwisko

resident DaneOsobowe;

Map_Imię_Pr:

Mapping load

PESEL,

text(Imię) as Imię

resident DaneOsobowe;

NoConcatenate

DaneOsobowe2:

load

// RowNo() as Number,

PESEL,

text(Nr_osobowy) as Nr_osobowy,

applymap('Map_Pesel_Ostatnia', PESEL,applymap('Map_Nazwisko_Pr', PESEL,0)) as Nazwisko,

applymap('Map_Pesel_OstatniaImie', PESEL,applymap('Map_Imię_Pr', PESEL,0)) as Imie,

Podobszar,

num#(Zleceniodawca_NIP) as Zleceniodawca_NIP,

JG_nazwa as Zleceniodawca_nazwa,

Zleceniodawca_skrót,

System as System_source

//Date(Now()) as Data_Zmiany

Resident DaneOsobowe

WHERE len(PESEL)>10;

drop table DaneOsobowe;

rename table DaneOsobowe2 to DaneOsobowe;

Map_NIP:

mapping load

   text(upper(PYRID)) as JG_skrót,

   text(NIP00) as NIP

FROM

$(TabelaHR)T7PLD1_*.qvd (qvd);

Concatenate

DaneOsobowe:

LOAD

RowNo() as Number,

num(upper(PESEL)) as PESEL,

text(upper(Klucz_Mandant_Nr_osobowy)) as Nr_osobowy,

ApplyMap('Map_Pesel_Ostatnia',num(upper(PESEL)),0) as Nazwisko,

ApplyMap('Map_Pesel_OstatniaImie',num(upper(PESEL)),0) as Imie, 

text(upper(Jednostka)) as Podobszar,

ApplyMap('Map_NIP',text(upper(JG_skrót)),0) as Zleceniodawca_NIP,

upper(JG_nazwa) as Zleceniodawca_nazwa,

text(upper(JG_skrót)) as Zleceniodawca_skrót,

'SAP' as System_source,

//Date(Now()) as Data_Zmiany

ApplyMap('Max_Spolki_JG_skrót',Klucz_Mandant_Nr_osobowy,0) as Aktualna

FROM

$(HrIBSQvd)SAP1_*.qvd (qvd)

where (Typ_umowy='F1' or Typ_umowy='U1') and len(PESEL)>10;  //and JG_skrót<>'JISW';

map_names:

mapping LOAD * INLINE [

   nameOLD, nameNEW

       Zleceniodawca_NIP, Company_NIP

       Nr_osobowy, Person_ID

       Podobszar, Department

       Zleceniodawca_skrót, Company_shortcut

       Zleceniodawca_nazwa, Company_name

];

RENAME Fields using map_names;

NoConcatenate

DaneOsobowe2:

load

replace(replace(Text(Hash128(PESEL, Person_ID, Company_shortcut, Department)),chr(39),'"'),';','|')  as UniqueID,

//PESEL & Person_ID & Company_shortcut & Department AS UniqueID,

Now() as Data_Zmiany,

if(IsNull(PESEL),0,PESEL) as PESEL,

Person_ID,

Nazwisko,

Imie,

Department,

if(len(Company_NIP)<2,0,num#(Company_NIP)) as Company_NIP,

Company_name,

Company_shortcut,

System_source

resident DaneOsobowe;

drop table DaneOsobowe;

RENAME table DaneOsobowe2 to DaneOsobowe;

STORE DaneOsobowe into $(HrIBSQvd)Dane_SlownikDaily.qvd(qvd);

store DaneOsobowe into $(PlikiQlikViewPath)Dane_Slownik.csv(txt, delimiter is ';');

exit Script;

And now all data is strored into Dane_SlownikDaily.qvd and created CSV file i can bulk insert it into ms sql database without any problems.

And here i am doing incremental load in the next application.

Code:

Table_source:

load

*

from

$(HrIBSQvd)Dane_SlownikSource.qvd (qvd);

NoConcatenate

AppendsNew:

load UniqueID,

     PESEL,

     Person_ID,

     Nazwisko,

     Imie,

     Department,

     Company_NIP,

     Company_name,

     Company_shortcut,

     System_source,

     Date(Now()) as Data_Zmiany

from $(HrIBSQvd)Dane_SlownikDaily.qvd (qvd)

WHERE NOT Exists(UniqueID);

drop Table Table_source;

STORE AppendsNew into $(HrIBSQvd)Dane_SlownikAppends.qvd(qvd);

store AppendsNew into $(PlikiQlikViewPath)Dane_Slownik.csv(txt, delimiter is ';');

Concatenate

load

*

from

$(HrIBSQvd)Dane_SlownikSource.qvd (qvd);

STORE AppendsNew into $(HrIBSQvd)Dane_SlownikSource.qvd(qvd);

After reloading also csv is created.

Here MS SQL trrows the error:

problem.png

In attachment please find my tested files.

I know that code pages or sam encoding is changing here but why?

Thank you for helping me,

Best Wishes,

Jacek Antek

1 Reply
narenjln
Creator
Creator