Stampa
Categoria: Articles
Visite: 905
Stella inattivaStella inattivaStella inattivaStella inattivaStella inattiva
 

 

If you have to import an excel file into a database table you have different ways. Recently I faced with an excel file with inside cells with single quote inside the text and some amount cells with zero value.

 

Here you can find my formula, you can use it a basic formula for your case.

Formulas are in italian but you can replace them:

SOSTITUISCI with REPLACE

TEST with TEXT

CODICE.CARATT with I really don't know but it return a char starting from ascii value. In our case return the single quote for ascii 39 and blank space for ascii 32.

 

 

="INSERT INTO [dbo].[FECasFis] ([TIPDOC],[NUMDOC],[DATDOC],[PARIVA],[RAGSOC],[IMPONIB],[IMPOSTA],[SDIFILE],[STATOCONS],[DATRIC],[BOLLO]) VALUES ("
& "'" & SOSTITUISCI(B2,CODICE.CARATT(39),CODICE.CARATT(32)) & "'" & ","
& "" & C2 & "" & ","
& "'" & ANNO(D2) & "-" & MESE(D2) & "-" & GIORNO(D2) &"'" & ","
& "" & E2 & "" & ","
& "" & "'" & SOSTITUISCI(F2,CODICE.CARATT(39),CODICE.CARATT(32)) & "'" & "," 
&  TESTO(G2,"#########0.##0") & ","
&  TESTO(H2,"#########0.##0") & ","
& "'" & I2 &"'" & ","
& "'" & J2 &"'" & ","
& "'" & ANNO(K2) & "-" & MESE(K2) & "-" & GIORNO(K2) &"'" & ","
& "'" & L2 &"'"
& ")"

 

and here the SQL Insert generated from Excel formula

 

INSERT INTO [dbo].[FECasFis] 
([TIPDOC],[NUMDOC],[DATDOC],[PARIVA],[RAGSOC],[IMPONIB],[IMPOSTA],[SDIFILE],[STATOCONS],[DATRIC],[BOLLO]) 
VALUES 
('','40 001','2021-1-31','01468220122',' FONTANELLA SAS DI A. REZZONICO & C. ',44.80,9.860,'4509841433','Fattura consegnata','2021-2-8','')