Solved

Importting CSV files to SQL 2005

Posted on 2010-09-21
6
773 Views
Last Modified: 2013-11-11
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
Comment
Question by:sebadlington
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:_bmendoza
ID: 33730045
try this

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

or use bcp
0
 

Author Comment

by:sebadlington
ID: 33730172
That is what I was trying but it also imports the speech marks and makes a mess!
0
 

Author Comment

by:sebadlington
ID: 33730174
Sorry - what is BCP?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Accepted Solution

by:
_bmendoza earned 250 total points
ID: 33730739
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
 
LVL 3

Expert Comment

by:_bmendoza
ID: 33730746
create table #temp_uploader(
field1 [varchar](10),
.
.
.
)
0
 
LVL 12

Assisted Solution

by:jagssidurala
jagssidurala earned 250 total points
ID: 33731821
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: DanRollins
This article describes a JavaScript program that creates a maze made of hexagonal cells.  In Part 2 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7850-Hex-Maze-Part-2.html), we'll extend the program by adding a depth-…
Having worked on larger scale sites, we found out that you are bound to look at more scalable solutions to integrating widgets, code snippets or complete applications and mesh them into functional sites, in any given composition. To share some of…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question