Importting CSV files to SQL 2005

I need to import csv files into sql on a regular basis. Basically I have a trucking system that needs to get jobs from someone elses system. They send me a file every half hour via ftp that I auto download to a local folder, I need a script to then import this file into a sql table, the fields are always the same
JobNo, delivery Name, Delivery address1, Delivery Address2 etc
I have the code below which works fine to import a single file but I need to be able to step through a set of files (the filenames are basically Import+date+time.txt
Where i am coming stuck is that the fileds in the csv file have " " wrapped around each field and the parentheses are also being imported into sql and then breaking down
I use a piece of software called Automize which schedules all this and can happilly run any SQl queries for me, can i do all this from SQl?


"SHP","","C J LANG & SONS LTD","SPAR FOODSTORES","3 MAIN ROAD","CONDORRAT","CUMBERNAULD","STRATHCLYDE","G123 456","01236 xxxxxx","Tetron Point","William Nadin Way","","Swadlincote","Derbyshire","D123 456","01283 xxxxx","21/09/10","","Yes",2

Open in new window

<!--#include file="Connections/Clandestine.asp" -->
strPath = Server.MapPath(".")

set objConnection=Server.CreateObject("ADODB.Connection")
strConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" &  strPath & "\;Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
objConnection.Open strConnectionString
strSQL = "Select * From SHP.txt" 
set objRS=Server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL, objConnection,3,3

set objRS = Nothing
set objConnection = Nothing

for i=0 to UBound(arrRows,2)

ImportType = arrRows(0,i)
OrderNo =  arrRows(1,i)
CollName = arrRows(2,i)
CollName2 = arrRows(3,i)
CollAddr1 = arrRows(4,i)
CollAddr2 = arrRows(5,i)
CollAddr3 = arrRows(6,i)
CollAddr4 = arrRows(7,i)
CollPostCode = arrRows(8,i)
CollTel = arrRows(9,i)
DelName = arrRows(10,i)
DelAddr1 = arrRows(11,i)
DelAddr2 = arrRows(12,i)
DelAddr3 = arrRows(13,i)
DelAddr4 = arrRows(14,i)
DellPostCode = arrRows(15,i)
DelTel = arrRows(16,i)
DelDate = arrRows(18,i)
DelDate = arrRows(17,i)
DelTime = arrRows(18,i)
DelBooked = arrRows(19,i)
PalletQty = arrRows(20,i)

   SQLData2 = "'"& ImportType & "','" & OrderNo & "','" & CollName & "','"& CollName2 & "','"& CollAddr1 & "','"& CollAddr2 & "','"& CollAddr3 & "','"& CollAddr4 & "','"& CollPostCode & "','"& CollTel & "','"& DelName & "','"& DelAddr1 & "','"& DelAddr2 & "','"& DelAddr3 & "','"& DelAddr4 & "','"& DelPostCode & "','"& DelTel & "','"& DelDate & "','"& DelTime & "','"& DelBooked & "',"& PalletQty

Response.Write ( "<BR>" & "Detail: "& SQLData2 & "<BR>")
    MM_editConnection = MM_cs_STRING
    Set MM_editCmd2 = Server.CreateObject("ADODB.Command")
    MM_editQuery2 = "insert into SebSertUpdates (OrderNo,CollCustomer,CollCustomer2,CollAddr1,CollAddr2,CollAddr3,CollAddr4,CollPostCode,CollTel,DellCustomer, DelAddr1,DelAddr2,DelAddr3,DellAddr4,DellPostCode,DelTel,DelDate,DelTime,Booked,PalletQty) values (" & SQLData2 & ")"
    MM_editCmd2.ActiveConnection = MM_Clandestine_STRING
    MM_editCmd2.CommandText = MM_editQuery2



Open in new window

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

_bmendozaConnect With a Mentor Commented:
please refer to

in command line
C:\Documents and Settings\<userprofile>\Desktop>bcp <tablename> in test.csv -c -T -S<servername\instance>

you need to create a format file.

my another suggestion is to create a temp table (all are varchar/nchar type columns)
create #temp_uploader(
field1 [varchar](10),

use bulk insert to #temp_uploader and from the #temp_uploader you can do all the data type conversion and validation before you insert it into the live table.
try this

bulk insert [your_table]
from 'c:\SHP.txt'
fieldterminator = ',',

or use bcp
sebadlingtonAuthor Commented:
That is what I was trying but it also imports the speech marks and makes a mess!
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

sebadlingtonAuthor Commented:
Sorry - what is BCP?
create table #temp_uploader(
field1 [varchar](10),
All Courses

From novice to tech pro — start learning today.