从CSV文件批量插入到MS SQL数据库 [英] Bulk Insert from CSV file to MS SQL Database

问题描述

我有这个工作脚本，可用于批量插入CSV文件.

I have this working script that I use to BULK INSERT A CSV FILE.

代码是:

    ' OPEN DATABASE
    dim objConn,strQuery,objBULK,strConnection
    set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=DemoSrvCld;Trusted_Connection=no;UID=dcdcdcdcd;PWD=blabla;database=demotestTST;"
    objConn.Open strConnection
    set objBULK = Server.CreateObject("ADODB.Recordset")
    set objBULK.ActiveConnection = objConn

dim strAPPPATH
strAPPPATH="C:\DEMO001Test.CSV"
    strQuery = "BULK INSERT EFS_OlderStyle FROM '" & strAPPPATH & "' WITH (firstrow=1, FIELDTERMINATOR=',', ROWTERMINATOR='\n')"
Set objBULK= objConn.Execute(strQuery) 
objConn.Close

这里是.CSV文件的示例:

HERE IS A EXAMPLE OF THE .CSV FILE:

Date,Time,Card Number,Driver Id,Driver Name,Unit No,Sub-Fleet,Hub Miles,Odo Miles,Trip No,Invoice,T/S Code,In Dir,T/S Name,T/S City,ST,Total Inv,Fee,PPU,Fuel_UOM,Fuel_CUR,RFuel_UOM,RFuel_CUR,Oil_CUR,Add_CUR,Cash Adv,Tax,Amt Billed,Svc Bill,Chain,Ambest,MPU
10/08/13,03:20,70113531460800693,,,2100,,,,,0454591156,546200,Y,PILOT QUARTZSITE 328,QUARTZSITE,AZ,742.30,1.00,3.749,149.000,558.60,49.00,183.70,0.00,0.00,0.00,0.00,743.30,S, ,N,0.0
10/08/13,07:03,70110535170800735,,,6210,,,,,343723,512227,Y,PETRO WHEELER RIDGE,LEBEC,CA,678.78,1.00,4.169,139.140,580.08,23.68,98.70,0.00,0.00,0.00,0.00,679.78,S, ,N,0.0

但是我现在拥有的.CSV文件与上面的文件不同.

But the .CSV FILE I HAVE NOW IS DIFFRENT then the one above.

这里是当前.CSV文件的一个示例:

HERE IS A EXAMPLE OF THE CURRENT .CSV FILE:

"BRANCH","CARD","BILL_TYPE","AUTH_CODE","INVOICE","UNIT","EMP_NUM","TRIP","TRAILER","HUB/SPEED","VEH_LICENSE","DRIVER","DATE","TIME","CHAIN","IN_NETWORK","TS#","TS_NAME","TS_CITY","TS_STATE","PPG","NET_PPG","FUEL_GALS","FUEL_AMT","RFR_GALS","RFR_AMT","CASH","MISC","INV_TOTAL","FEE","DISC","INV_BALANCE",1.00,1.00,"E","004ACS","02812","365","-","-","0",0.00,"-","JOHN S  ",11/4/2013,"16:18:49E","IC","N",3257.00,"IRVING HOULTON","HOULTON","ME",3.95,3.95,121.57,480.08,0.00,0.00,0.00,0.00,480.08,1.50,0.00,481.58
"BRANCH","CARD","BILL_TYPE","AUTH_CODE","INVOICE","UNIT","EMP_NUM","TRIP","TRAILER","HUB/SPEED","VEH_LICENSE","DRIVER","DATE","TIME","CHAIN","IN_NETWORK","TS#","TS_NAME","TS_CITY","TS_STATE","PPG","NET_PPG","FUEL_GALS","FUEL_AMT","RFR_GALS","RFR_AMT","CASH","MISC","INV_TOTAL","FEE","DISC","INV_BALANCE",1.00,2.00,"E","014ACI","976234","430","-","-","0",0.00,"-","STACY    ",11/4/2013,"00:21:16E","F","Y",8796.00,"PILOT 405","TIFTON","GA",3.77,3.77,172.65,650.73,0.00,0.00,0.00,0.00,650.73,1.50,0.00,652.23

我已经编辑了ms sql数据库字段以反映新的.csv字段，但是旧的和新的.csv文件都不存储该信息.以同样的方式. 如何解决此问题，使其起作用?

I have edited the ms sql database fields to reflect the new .csv fields but the old and new .csv files do not store the info. in the same way. How do I fix this so that it works ?

我当时想先删除所有"，然后再删除除一个"BRANCH","CARD","BILL_TYPE","AUTH_CODE","INVOICE","UNIT","EMP_NUM","TRIP","TRAILER","HUB/SPEED","VEH_LICENSE","DRIVER","DATE","TIME","CHAIN","IN_NETWORK","TS#","TS_NAME","TS_CITY","TS_STATE","PPG","NET_PPG","FUEL_GALS","FUEL_AMT","RFR_GALS","RFR_AMT","CASH","MISC","INV_TOTAL","FEE","DISC","INV_BALANCE",

I was thinking to first remove all of the " and then to remove all but one "BRANCH","CARD","BILL_TYPE","AUTH_CODE","INVOICE","UNIT","EMP_NUM","TRIP","TRAILER","HUB/SPEED","VEH_LICENSE","DRIVER","DATE","TIME","CHAIN","IN_NETWORK","TS#","TS_NAME","TS_CITY","TS_STATE","PPG","NET_PPG","FUEL_GALS","FUEL_AMT","RFR_GALS","RFR_AMT","CASH","MISC","INV_TOTAL","FEE","DISC","INV_BALANCE",

然后保存.csv文件，然后重新打开它.

then save the .csv file and then reopen it.

但是我认为/希望还有另一种方法? 请帮忙... 谢谢.

But I think/hope there is another way ? Please help... Thank you.

推荐答案

当然:有几种方法可以解决此问题.正确的解决方案将取决于您专用于此问题的时间和精力，以及这是一次性导入还是要简化的流程.

Sure: there are a few ways you can work around this problem. The right solution for you will depend on the time and energy you have to dedicate to this problem, as well as whether this is a one time import or a process you want to streamline.

一些解决方案:

这很容易实现:

• 下载文本编辑器，例如 notepad ++
• 在此编辑器中打开CSV文件
• 执行查找/替换操作:

"BRANCH"，"CARD"，"BILL_TYPE"，"AUTH_CODE"，"INVOICE"，"UNIT"，"EMP_NUM"，"TRIP"，"TRAILER"，"HUB/SPEED"，"VEH_LICENSE"，"DRIVER"，"DATE"，"TIME"，"CHAIN"，"IN_NETWORK"，"TS#"，"TS_NAME"，"TS_CITY"，"TS_STATE"，"PPG"，"NET_PPG"，"FUEL_GALS"，"FUEL_AMT" "，"RFR_GALS"，"RFR_AMT"，现金"，"MISC"，"INV_TOTAL"，"FEE"，"DISC"，"INV_BALANCE"

"BRANCH","CARD","BILL_TYPE","AUTH_CODE","INVOICE","UNIT","EMP_NUM","TRIP","TRAILER","HUB/SPEED","VEH_LICENSE","DRIVER","DATE","TIME","CHAIN","IN_NETWORK","TS#","TS_NAME","TS_CITY","TS_STATE","PPG","NET_PPG","FUEL_GALS","FUEL_AMT","RFR_GALS","RFR_AMT","CASH","MISC","INV_TOTAL","FEE","DISC","INV_BALANCE"

替换为:"

• 最后将上面的行添加为标头-快速以与旧文件相同的格式重新格式化新文件.

• Finally add the line above as your header- quickly reformatting your new file in the same format as your old file.

• 注意:如果您是一次性导入，这可能是最好的选择.

由于每行的开头都包含您要忽略的字段，因此您可以根据字符数轻松截断每行. String.Replace 函数可以是用于在将行的初始(可忽略)部分插入String.Empty之前，将其插入数据库中.

Since the beginning of each line contains the fields you wish to ignore, you can easily truncate each line based on the number of characters. The String.Replace function can be used to replace the initial (ignorable) part of the line with String.Empty before it is inserted into the DB.

这篇关于从CSV文件批量插入到MS SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！