Solved

Importting CSV files to SQL 2005

Posted on 2010-09-21
6
768 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now