excel文件中批量生成insert语句

1、有时因为工作需要我们要向表中写入很多的insert语句，现在有一个简单的方法使我们直接利用excel即可生成insert语句=5文件褊辑=5文件褊辑视图插入(I)格式工具数据窗口址)帮助心日詁兰丄丨毬逆*逼囂ty|耳卩“鶴已辭和|私心ioo%ABCDEF1MATERIELNAMEM_TESTMATERIELTYPEIN_TIMEOUT_TIMEFLAG2FTLF8524E2KNL8501052011-12-14I3FTLF8524E2KNL850NM52011-12-144FTLF8524E2KNL850NM52011-12-145FTLF8524E2KNL850NM52011-12-146F

2、TLF8524E2KNL850NM52011-12-147FTLF8524E2KNL850NM52011-12-148FTLF8524E2KNL8501052011-12-149FTLF8524E2KNL850NM52011-12-1410FTLF8524E2KNL850NM52011-12-1411FTLF8524E2KNL850NM52011-12-1412FTLF8524E2KNL850NM52011-12-1413FTLF8524E2KNL850NM52011-12-1414151617巴行出开始利用公司拼接字2首:先在第一仃数据的右侧空付串尋手12B旦G2=(.insertintom

3、list(.materielnamejmaterieltypeiii-tiinejcontractid)valuesCrrN2382rr,todateCyyyy-inm-dd)rr2rr)BCDEFGHIJMTESTMATERIELTYPEINTIMEOUTTIMEcontractid52011-12-14zb0003irtfrtiitom_list(materielnamejm52011-12-14zb0003QA11-1Q-1A在黑框处写如下字符串拼接语句:=(insertintom_list(materielname,materieltype,in_time,contractid)val

4、ues(&A2&,&C2&,to_date(&D2&,yyyy-mm-dd),&F2&);)解释如下：在“=”后面写公式，字符串部分用双引号括起来，表格公式之间用&连接。A2代表第A列，第二行，以此类推。在输入时间格式时需注意，数字在右对齐时excel默认为数字型，生成insert语句如下：(insertintomlist(materielnamematerieltype,in-timecontractid)valuesCtcidai:亡&D2此，yyyy-mm一dd)J&F2&)GHIJKLMN0PQRSTUVidlins亡rtintcimlist(mat亡ri亡nam巴mat亡ri亡.ty

5、p亡，intiip.GCintraGtid)walu亡s(FTLF呂524E2KNL呂50NIT.5,tudat(40吕91,wwinm-dd)zb0003)时间类型变成了如下格式，若需要excel识别其为文本格式，需要在当前行左侧加一个单引号。例:此时时间格式才会作为文本传入。(insertintomlist(materielnamejmaterieltypeiii-tiinejcontractid)valuesC/y&A21:口(1玄1:亡(yyyy-inm一ddGHIJKLMN0PQRSTUVidIinsurti此时的sqliJtomlist(materielname,materielt

6、ype,intime,contractid)valuesCFTLF8524E2KNL850NMJ5jtodateC2011-12-14Jyyyy-inm-ddJ)/zb0003)f才正常。在将其公式下拉复制到每一行中，批量的sql语句就生成啦:(f000q?(fPP-I-AAAAf-j平po；弓fOOS8TOE3ES8jniJf)sanpA(PTQEMLirb9IHTlUTa1?lipTJzairiinLgiurmygiza口iiQUIOUIT(fsoooqz/(fpp-i-M/1-Zl-TlOZf)3Po；冷ffMN038TOEStESSnijIf)saFApi；DEJ：LIODf9UIT；

7、LITfgdA.giJ：g.euiuiEuygTjgEuiuiOLIT(foooqz/(fpp-i-M/I-El-TIOEj科它po；写ffO038TOEStESSnijIf)sanpApi；3EJ：LI03f9UIT；LITdAfaiJig.euiuiEuyaTjaEuiUIOLIT(foooqs/(fpp-i-Mfff)3Pi冷ffO038TOESESSnijIf)sanAfauipUTdA-faijguiguiEuyaTjaEiiiUI01LIT-(f000qzff(fPp-inui-AAAAfffl-El-TlOEf)3珂o；冷ffWN038TOEStESSnijIf)sanpApi；

8、DEJ：l.LIODf9UIT；UTfadAq.pTJ：gq.EuifauiEUgTJ：g；Eui苗HUIOLIT(foooqz/(fpp-i-M/1-31-1103j平po；乜ffOOS8TOESESSniJ；)sIapi；DEJ：l.LIODf9UIT；_LITdAq.fgTJigq.EuifauiEUgTJ：g；Eui_UIOLIT(foooqz/(fpp-i-M/冲叩o；写ffOOS8TOE3ES8jniJf)sanpApi；DEJ：l.LIODf9UIT；LITfgdAq.piJ：gq.EuifauiEUgTJ：g；EuiUIOLIT(fsoooqz/(fpp-i-M/I-ZI-TI

9、OZf)3Po；冷ffMN038TOEStESSnijIf)saFApi；DEJ：LIODf9UIT；LITfgdA.giJ：g.euiuiEuygTjgEuiUIOLIT(foooqz/(fpp-i-M/I-El-TIOEj科它po；写ffO038TOEStESSnijIf)sanpApi；3EJ：LI03f9UIT；LITdAfaiJig.euiuiEuyaTjaEuiUIOLIT(foooqs/(fpp-i-Mfff)3Pi冷ffO038TOESESSnijIf)sanAfauipUTdA-faijguiguiEuyaTjaEiiiUI01LIT-(f000qzff(fPp-inui-A

10、AAAfffl-El-TlOEf)3珂o；冷ffWN038TOEStESSnijIf)sanpApi；DEJ：l.LIODfauiT；UTfadAq.pTJ：gq.EuifauiEUgTJ：g；Eui苗HUIOLIT(foooqz/(fpp-i-M/1-31-1103j平po；乜ffOOS8TOESESSniJ；)sIapi；DEJ：l.LIODf9UIT；_LITdAq.fgTJigq.EuifauiEUgTJ：g；Eui_UI01-UTIjzasui4JZ9SUT4JZ9SUTIJSSUT4JZ9SUT4JZ9SUT4JZ9SUT4JZ9SUT4JZ9SUTIJSSUT4JZ9SUT4JZ9SUTPHAISQd0