• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

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

0
sebadlington
Asked:
sebadlington
  • 3
  • 2
2 Solutions
 
_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
 
sebadlingtonAuthor Commented:
Sorry - what is BCP?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
_bmendozaCommented:
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:
create table #temp_uploader(
field1 [varchar](10),
.
.
.
)
0
 
jagssiduralaCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now