0

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

The code is:

    ' 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

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

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

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

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 ?

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",

then save the .csv file and then reopen it.

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

Improve this question
4
  • The new file you provided is not complete. It is missing the columns after INV_BALANCE, so what has changed exactly if you know? Commented Nov 11, 2013 at 22:45
  • @EmmadKareem The new file is all in one line. The title/header end right after "INV_BALANCE", .... Look at the CURRENT .CSV FILE EXAMPLE I PROVIDED TWO COMPLETE LINES Commented Nov 11, 2013 at 22:51
  • the header/title has 32 rows and then there is another 32 rows of data, and it repeats in each line with different data of course. Commented Nov 11, 2013 at 23:02
  • 1
    I assume that you have to remove repeated headers before you process the bulk insert. You can write some code for that specially if the number of repeated headers is always the same. However, repeating headers is pretty odd to me. Commented Nov 11, 2013 at 23:10

1 Answer 1

Reset to default
0

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.

A few solutions:

1. Change the formating of your CSV file to resemble the old version.

this can be done realtively easily:

  • Download a text editor like notepad++
  • Open your CSV file in this editor
  • Do a find/Replace operation for:

"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"

replace with: ""

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

    • Note: this may be the best option if you have a one time import.

2. Make the above changes in your code programatically

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.