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

Anonymous — Fri, 08 Dec 2017 10:23:52 GMT

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:

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

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

Anonymous — Fri, 08 Dec 2017 10:31:07 GMT

Hi Kindly refer to this Thread

sql - Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column …

topic Re: What is the difference between 2 stored csv files? Page encoding in QlikView

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

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