Monday, November 26, 2007

When people who know SQL use Excel

So I had some urgent data import to do, and DTS wasn't playing fair...
I have the following csv format:

contactid, telephone1, telephone2, mobilephone
GUID, number, number, number
GUID, number, number, number
GUID, number, number, number

and I needed it in a database... so sure, I could have got DTS working, or I could have used BCP to put the data in (though the biggest set is only 3000 lines)... or I could have written a dot net program to load the csv and import it to a table using a sqlbulkcopy (which i've done before for significantly bigger files) but..

Well, i'm ashamed to say, log growth and performance issues aside, that I did this:


=CONCATENATE("create table [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (id int identity(1,1) primary key, contactid uniqueidentifier, ",$B$1," varchar(1000), ",$C$1," varchar(1000), ",$D$1," varchar(1000))")


=SUBSTITUTE(CONCATENATE("insert into [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (contactid,",$B$1,",", $C$1, ",", $D$1,") values ('",A2,"','",SUBSTITUTE(B2,"'","''"),"','",SUBSTITUTE(C2,"'","''"),"','",SUBSTITUTE(D2,"'","''"),"')"),"'NULL'","NULL")


It's like I said... when people who know SQL use Excel.

No comments: