Importting CSV files to SQL 2005

Hi,
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?

Thanks

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

<%@LANGUAGE="VBSCRIPT"%>
<!--#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
arrRows=objRS.GetRows()

objRS.close
set objRS = Nothing
objConnection.Close
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
   'MM_editCmd2.Execute
    MM_editCmd2.ActiveConnection.Close
	
 

next


%>

Open in new window

sebadlingtonAsked:
Who is Participating?
 
_bmendozaConnect With a Mentor Commented:
please refer to

http://msdn.microsoft.com/en-us/library/ms162802.aspx

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.
0
 
_bmendozaCommented:
try this

bulk insert [your_table]
from 'c:\SHP.txt'
with(
fieldterminator = ',',
rowterminator='\n'
)

or use bcp
0
 
sebadlingtonAuthor Commented:
That is what I was trying but it also imports the speech marks and makes a mess!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sebadlingtonAuthor Commented:
Sorry - what is BCP?
0
 
_bmendozaCommented:
create table #temp_uploader(
field1 [varchar](10),
.
.
.
)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.