|
rdouglass -> RE: Best approach to update server db from local machine (2/4/2008 13:51:48)
|
Is the spreadsheet just 1 worksheet? DO you have ASP.NET?
I have a .NET script I use that is straightforward in terms of dealing with a text file update source. It currently gets a tab-delimited text file (that can easily be a CSV file) and reads it into the TextStream object (.NET version of FSO's). Then runs a SQL connection and checks a current table. It then either UPDATE's or INSERT's based on whether their is a match or not.
I use 2 fields to determine a match and yours will probably be different. This is the full page and the exact code I use (with filenames and paths changed) and it's a page I execute as part of a daily updating sequence. Here it is if it helps any:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
sub Page_Load(sender as Object, e as EventArgs)
Dim FILENAME As String = Server.MapPath("/mydownloads/download.txt")
Dim filetoread As String
filetoread = (FILENAME)
Dim filestream As StreamReader
filestream = File.OpenText(filetoread)
Dim readcontents As String
readcontents = filestream.ReadToEnd()
Dim textdelimiter As String
textdelimiter = Chr(10)
Dim splitout = Split(readcontents, textdelimiter)
filestream.Close()
Dim sConnStr As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("SF").ConnectionString
Dim cnBKTest As New SqlConnection(sConnStr)
cnBKTest.Open()
Dim strSQL As String = ""
Dim i As Integer
Dim j As Integer
Dim myTempVal As Integer = 0
Dim myTempString As String = ""
For i = 1 To UBound(splitout) - 1
myTempVal = 0
Dim splitout2 = Split(splitout(i), vbTab)
For j = 0 To UBound(splitout2)
If splitout2(j).ToString & "" = "" Then
splitout2(j) = "0"
End If
Next
strSQL = "SELECT * FROM tlkp_NSD_Report WHERE (ReportDate ='" & splitout2(0) & "') AND (AdvertiserID = " & splitout2(1) & ")"
Dim cmdTest As New SqlCommand(strSQL, cnBKTest)
cmdTest.CommandType = Data.CommandType.Text
cmdTest.ExecuteScalar()
Dim reader As SqlDataReader = cmdTest.ExecuteReader()
If reader.Read() Then
myTempVal = 1
End If
reader.Close()
If myTempVal = 1 Then
strSQL = "UPDATE tlkp_NSD_Report SET ReportDate = '" & splitout2(0) & "', AdvertiserID = " & splitout2(1) & ", AdvertiserName = '" & splitout2(2) & "', AdvertiserCode = '" & splitout2(3) & "', CampaignID = '" & splitout2(4) & "', CampaignName = '" & splitout2(5) & "', CampaignSpend = " & splitout2(6) & ", CampaignAdjustment = " & splitout2(7) & ", CampaignFees = " & splitout2(8) & ", Visits = " & splitout2(9) & ", Impressions = " & splitout2(10) & ", Calls = " & splitout2(11) & ", Emails = " & splitout2(12) & ", Coupons = " & splitout2(13) & ", WebLinks = " & splitout2(14) & ", WebEvents = " & splitout2(15) & ", LastUpdated = '" & splitout2(16) & "' WHERE (ReportDate ='" & splitout2(0) & "') AND (AdvertiserID = " & splitout2(1) & ")"
Else
strSQL = "INSERT INTO tlkp_NSD_Report (ReportDate, AdvertiserID, AdvertiserName, AdvertiserCode, CampaignID, CampaignName, CampaignSpend, CampaignAdjustment, CampaignFees, Visits, Impressions, Calls, Emails, Coupons, WebLinks, WebEvents , LastUpdated) VALUES ('" & splitout2(0) & "'," & splitout2(1) & ",'" & splitout2(2) & "','" & splitout2(3) & "','" & splitout2(4) & "','" & splitout2(5) & "'," & splitout2(6) & "," & splitout2(7) & "," & splitout2(8) & "," & splitout2(9) & "," & splitout2(10) & "," & splitout2(11) & "," & splitout2(12) & "," & splitout2(13) & "," & splitout2(14) & "," & splitout2(15) & ",'" & splitout2(16) & "')"
End If
Dim cmdTest2 As New SqlCommand(strSQL, cnBKTest)
myTempString = myTempString & i & strSQL & "<br />"
cmdTest2.CommandType = Data.CommandType.Text
cmdTest2.ExecuteScalar()
Next
lblOutput.Text = myTempString
cnBKTest.Close()
End Sub
</script>
<html>
<head>
<title>Updating NSD Records...</title>
</head>
<body>
<form id="Form1" runat="server">
<asp:label runat="server" id="lblOutput" />
</form>
</body>
</html>
So if something like this works, you'd essentially save the Excel file as a text doc(TSV or CSV) and upload it to the server and then kick off this page / script. Just remeber all bets are off if the Excel file has multiple worksheets.
Hope it helps.
|
|
|
|
