Tim Morrison
|
| Posted: 03/11/2003, 2:39 PM |
|
CodeChargeStudio 1.0 (Dont have 2.0 yet)
How can I import a CSV file into a SQL table. On the form, I would like the
user to be able to enter a number and then import a CSV file into the
database.
I.e. The user enters a "1".
CSV file contains 3 fields, SQL Table contains 4 fields
SQL:Field1 = CSV:Field1
SQL:Field2 = CSV:Field2
SQL:Field3 = CSV:Field3
SQL:Field4 = User Entered Variable from Form
How is this done in CCS?
Tim Morrison
|
|
|
 |
KP
|
| Posted: 03/11/2003, 5:22 PM |
|
You can do this via Enterprise Manager - it has an import facility (like a
wizard) that's relatively easy to use.
KP
"Tim Morrison" <sales@kjmsoftware.com> wrote in message
news:b4loii$vnp$1@news.codecharge.com...
> CodeChargeStudio 1.0 (Dont have 2.0 yet)
>
> How can I import a CSV file into a SQL table. On the form, I would like
the
> user to be able to enter a number and then import a CSV file into the
> database.
>
> I.e. The user enters a "1".
>
> CSV file contains 3 fields, SQL Table contains 4 fields
>
> SQL:Field1 = CSV:Field1
> SQL:Field2 = CSV:Field2
> SQL:Field3 = CSV:Field3
> SQL:Field4 = User Entered Variable from Form
>
> How is this done in CCS?
>
> Tim Morrison
>
>
|
|
|
|
Tim Morrison
|
| Posted: 03/11/2003, 5:42 PM |
|
Yes, but I need to be able to do it via ASP code as the process is performed
by end users and not on the server.
Tim Morrison
"KP" <webmaster@e-safety.sa.gov.au> wrote in message
news:b4m252$jig$1@news.codecharge.com...
> You can do this via Enterprise Manager - it has an import facility (like a
> wizard) that's relatively easy to use.
>
> KP
>
>
> "Tim Morrison" <sales@kjmsoftware.com> wrote in message
>news:b4loii$vnp$1@news.codecharge.com...
> > CodeChargeStudio 1.0 (Dont have 2.0 yet)
> >
> > How can I import a CSV file into a SQL table. On the form, I would like
> the
> > user to be able to enter a number and then import a CSV file into the
> > database.
> >
> > I.e. The user enters a "1".
> >
> > CSV file contains 3 fields, SQL Table contains 4 fields
> >
> > SQL:Field1 = CSV:Field1
> > SQL:Field2 = CSV:Field2
> > SQL:Field3 = CSV:Field3
> > SQL:Field4 = User Entered Variable from Form
> >
> > How is this done in CCS?
> >
> > Tim Morrison
> >
> >
>
>
|
|
|
|
DonB
|
| Posted: 03/11/2003, 6:09 PM |
|
But you could implement the standard file-upload and combine that with
firing a SQL Agent job (use sp_add_job and sp_add_job_step to create it on
the fly).
DonB
"Tim Morrison" <sales@kjmsoftware.com> wrote in message
news:b4m3as$lu5$1@news.codecharge.com...
> Yes, but I need to be able to do it via ASP code as the process is
performed
> by end users and not on the server.
>
> Tim Morrison
>
> "KP" <webmaster@e-safety.sa.gov.au> wrote in message
>news:b4m252$jig$1@news.codecharge.com...
> > You can do this via Enterprise Manager - it has an import facility (like
a
> > wizard) that's relatively easy to use.
> >
> > KP
> >
> >
> > "Tim Morrison" <sales@kjmsoftware.com> wrote in message
> >news:b4loii$vnp$1@news.codecharge.com...
> > > CodeChargeStudio 1.0 (Dont have 2.0 yet)
> > >
> > > How can I import a CSV file into a SQL table. On the form, I would
like
> > the
> > > user to be able to enter a number and then import a CSV file into the
> > > database.
> > >
> > > I.e. The user enters a "1".
> > >
> > > CSV file contains 3 fields, SQL Table contains 4 fields
> > >
> > > SQL:Field1 = CSV:Field1
> > > SQL:Field2 = CSV:Field2
> > > SQL:Field3 = CSV:Field3
> > > SQL:Field4 = User Entered Variable from Form
> > >
> > > How is this done in CCS?
> > >
> > > Tim Morrison
> > >
> > >
> >
> >
>
>
|
|
|
|
Tim Morrison
|
| Posted: 03/12/2003, 5:33 AM |
|
Could you give me an example how I could allow an end user to upload a CSV
file to the web server, and import the contents of the CSV file into the SQL
tables including one of the fields being one that a user enters on a form.
Tim Morrison
"DonB" <7432D63DBB01D03A196B1EDD80E8@comcast.net> wrote in message
news:b4m4sg$p5u$1@news.codecharge.com...
> But you could implement the standard file-upload and combine that with
> firing a SQL Agent job (use sp_add_job and sp_add_job_step to create it on
> the fly).
>
> DonB
>
>
> "Tim Morrison" <sales@kjmsoftware.com> wrote in message
>news:b4m3as$lu5$1@news.codecharge.com...
> > Yes, but I need to be able to do it via ASP code as the process is
> performed
> > by end users and not on the server.
> >
> > Tim Morrison
> >
> > "KP" <webmaster@e-safety.sa.gov.au> wrote in message
> >news:b4m252$jig$1@news.codecharge.com...
> > > You can do this via Enterprise Manager - it has an import facility
(like
> a
> > > wizard) that's relatively easy to use.
> > >
> > > KP
> > >
> > >
> > > "Tim Morrison" <sales@kjmsoftware.com> wrote in message
> > >news:b4loii$vnp$1@news.codecharge.com...
> > > > CodeChargeStudio 1.0 (Dont have 2.0 yet)
> > > >
> > > > How can I import a CSV file into a SQL table. On the form, I would
> like
> > > the
> > > > user to be able to enter a number and then import a CSV file into
the
> > > > database.
> > > >
> > > > I.e. The user enters a "1".
> > > >
> > > > CSV file contains 3 fields, SQL Table contains 4 fields
> > > >
> > > > SQL:Field1 = CSV:Field1
> > > > SQL:Field2 = CSV:Field2
> > > > SQL:Field3 = CSV:Field3
> > > > SQL:Field4 = User Entered Variable from Form
> > > >
> > > > How is this done in CCS?
> > > >
> > > > Tim Morrison
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
wolfgang
|
| Posted: 03/12/2003, 1:14 PM |
|
My files are almost CSV, but each record is like this
abcde|asdf|1224|etc|etc|dfgh|yutrew
mama|mama|1234|etc|etc|ssss|333
That is, fields delimited with the "|" character and each record end is a
crlf
The sample below is for just a two field update, but the process is the same
for any number of fields.
Two CodeCharge functions are appended.
This script updates the MS SQL table from the text file.
May give some ideas.
HTH
Wolfgang
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<!-- #INCLUDE FILE ="adovbs.inc" -->
<%
' DailyUpdate.asp - PMITLookup application daily updates
' Updates SQL server with:
' Changed and New Locations
Server.ScriptTimeout = 1000
'On Error Resume Next
'===============================
' Constants
'-------------------------------
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
'===============================
' Database Connection Definition
'-------------------------------
' PMI Connection begin
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
'-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
' Vary this connection according to machine needs.
'-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
cn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=YOURSQL;" & _
"User ID=guess;" & _
"Password=abcdefgh"
'===============================
' Variables
'-------------------------------
'-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
' Vary these paths according to machine needs
'-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
Dim strPathInfo, strPhysicalPath
LocUpdatePath = "D:\pims\locupdate"
'==================================
' Process File of new Locations
'----------------------------------
Dim objFSOLoc, objFileLoc,arrTextLoc
Set objFSOLoc = CreateObject("Scripting.FileSystemObject")
Set objFileLoc = objFSOLoc.GetFile(LocUpdatePath)
Dim objFileTextStreamLoc
Set objFileTextStreamLoc = objFileLoc.OpenAsTextStream(ForReading,
TristateUseDefault)
Dim strLine
Do While objFileTextStreamLoc.AtEndOfStream <> True
strLine = objFileTextStreamLoc.ReadLine
' strLine now has the new location details, so burst it to array
arrTextLoc = Split(strLine,"|")
' insert or update requires separate handling.
SQLloc = DLookup("PMILOCATIONS","LOCCODE","LOCCODE = "&
ToSQL(ArrTextLoc(0),"Text"))
If SQLloc = "" Then
sSQLa = "INSERT INTO PMILOCATIONS (LOCCODE,LOCATION) VALUES (" &_
ToSQL(arrTextLoc(0),"TEXT") & "," &_
ToSQL(arrTextLoc(1),"Text") &_
")"
Else
sSQLa = "UPDATE PMILOCATIONS " &_
"SET LOCCODE = " & ToSQL(arrTextLoc(0),"Text") &_
",LOCATION = " & ToSQL(arrTextLoc(1),"Text") &_
" WHERE LOCCODE = " & ToSQL(arrTextLoc(0),"Text") & ""
End If
cn.execute sSQLa
Loop
objFileTextStreamLoc.close
Set objFileLoc = Nothing
Set objFSOLoc = Nothing
Response.write "<br>Locations update completed <br>"
'-------------------------------
' Convert value for use with SQL statement
'-------------------------------
Function ToSQL(Value, sType)
Dim Param : Param = Value
If Param = "" Then
ToSQL = "Null"
Else
If sType = "Number" Then
ToSQL = replace(CDbl(Param), ",", ".")
Else
ToSQL = "'" & Replace(Param, "'", "''") & "'"
End If
End If
End function
'-------------------------------
' Lookup field in the database based on provided criteria
' Input: Table (Table), Field Name (fName), criteria (sWhere)
'-------------------------------
Function DLookUp(Table, fName, sWhere)
On Error Resume Next
Dim Res : Res = cn.execute("select " & fName & " from " & Table & " where
" & sWhere).Fields(0).Value
If IsNull(Res) Then Res = ""
DLookUp = Res
If bDebug Then Response.Write err.Description
On Error goto 0
End Function
"Tim Morrison" <sales@kjmsoftware.com> wrote in message
news:b4loii$vnp$1@news.codecharge.com...
> CodeChargeStudio 1.0 (Dont have 2.0 yet)
>
> How can I import a CSV file into a SQL table. On the form, I would like
the
> user to be able to enter a number and then import a CSV file into the
> database.
>
> I.e. The user enters a "1".
>
> CSV file contains 3 fields, SQL Table contains 4 fields
>
> SQL:Field1 = CSV:Field1
> SQL:Field2 = CSV:Field2
> SQL:Field3 = CSV:Field3
> SQL:Field4 = User Entered Variable from Form
>
> How is this done in CCS?
>
> Tim Morrison
>
>
|
|
|
|
Tim Morrison
|
| Posted: 03/12/2003, 1:37 PM |
|
Thank you. I will play with this to see if I can get it to work for my
solution.
Tim Morrison
"wolfgang" <wolfg@messinglaus.de> wrote in message
news:b4o7vq$j1q$1@news.codecharge.com...
> My files are almost CSV, but each record is like this
> abcde|asdf|1224|etc|etc|dfgh|yutrew
> mama|mama|1234|etc|etc|ssss|333
>
> That is, fields delimited with the "|" character and each record end is a
> crlf
>
> The sample below is for just a two field update, but the process is the
same
> for any number of fields.
> Two CodeCharge functions are appended.
> This script updates the MS SQL table from the text file.
>
> May give some ideas.
>
> HTH
> Wolfgang
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> <!-- #INCLUDE FILE ="adovbs.inc" -->
> <%
> ' DailyUpdate.asp - PMITLookup application daily updates
> ' Updates SQL server with:
> ' Changed and New Locations
>
> Server.ScriptTimeout = 1000
> 'On Error Resume Next
> '===============================
> ' Constants
> '-------------------------------
> Const ForReading = 1, ForWriting = 2, ForAppending = 3
> Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
>
> '===============================
> ' Database Connection Definition
> '-------------------------------
> ' PMI Connection begin
> Dim cn
> Set cn = Server.CreateObject("ADODB.Connection")
> '-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
> ' Vary this connection according to machine needs.
> '-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
> cn.Open "Provider=sqloledb;" & _
> "Data Source=(local);" & _
> "Initial Catalog=YOURSQL;" & _
> "User ID=guess;" & _
> "Password=abcdefgh"
>
> '===============================
> ' Variables
> '-------------------------------
> '-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
> ' Vary these paths according to machine needs
> '-- ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! * ! *
> Dim strPathInfo, strPhysicalPath
> LocUpdatePath = "D:\pims\locupdate"
>
> '==================================
> ' Process File of new Locations
> '----------------------------------
> Dim objFSOLoc, objFileLoc,arrTextLoc
> Set objFSOLoc = CreateObject("Scripting.FileSystemObject")
> Set objFileLoc = objFSOLoc.GetFile(LocUpdatePath)
> Dim objFileTextStreamLoc
> Set objFileTextStreamLoc = objFileLoc.OpenAsTextStream(ForReading,
> TristateUseDefault)
> Dim strLine
> Do While objFileTextStreamLoc.AtEndOfStream <> True
> strLine = objFileTextStreamLoc.ReadLine
> ' strLine now has the new location details, so burst it to array
> arrTextLoc = Split(strLine,"|")
> ' insert or update requires separate handling.
> SQLloc = DLookup("PMILOCATIONS","LOCCODE","LOCCODE = "&
> ToSQL(ArrTextLoc(0),"Text"))
> If SQLloc = "" Then
> sSQLa = "INSERT INTO PMILOCATIONS (LOCCODE,LOCATION) VALUES (" &_
> ToSQL(arrTextLoc(0),"TEXT") & "," &_
> ToSQL(arrTextLoc(1),"Text") &_
> ")"
> Else
> sSQLa = "UPDATE PMILOCATIONS " &_
> "SET LOCCODE = " & ToSQL(arrTextLoc(0),"Text") &_
> ",LOCATION = " & ToSQL(arrTextLoc(1),"Text") &_
> " WHERE LOCCODE = " & ToSQL(arrTextLoc(0),"Text") & ""
> End If
> cn.execute sSQLa
> Loop
> objFileTextStreamLoc.close
> Set objFileLoc = Nothing
> Set objFSOLoc = Nothing
> Response.write "<br>Locations update completed <br>"
> '-------------------------------
> ' Convert value for use with SQL statement
> '-------------------------------
> Function ToSQL(Value, sType)
> Dim Param : Param = Value
> If Param = "" Then
> ToSQL = "Null"
> Else
> If sType = "Number" Then
> ToSQL = replace(CDbl(Param), ",", ".")
> Else
> ToSQL = "'" & Replace(Param, "'", "''") & "'"
> End If
> End If
> End function
>
> '-------------------------------
> ' Lookup field in the database based on provided criteria
> ' Input: Table (Table), Field Name (fName), criteria (sWhere)
> '-------------------------------
> Function DLookUp(Table, fName, sWhere)
> On Error Resume Next
> Dim Res : Res = cn.execute("select " & fName & " from " & Table & "
where
> " & sWhere).Fields(0).Value
> If IsNull(Res) Then Res = ""
> DLookUp = Res
> If bDebug Then Response.Write err.Description
> On Error goto 0
> End Function
>
>
>
>
>
>
> "Tim Morrison" <sales@kjmsoftware.com> wrote in message
>news:b4loii$vnp$1@news.codecharge.com...
> > CodeChargeStudio 1.0 (Dont have 2.0 yet)
> >
> > How can I import a CSV file into a SQL table. On the form, I would like
> the
> > user to be able to enter a number and then import a CSV file into the
> > database.
> >
> > I.e. The user enters a "1".
> >
> > CSV file contains 3 fields, SQL Table contains 4 fields
> >
> > SQL:Field1 = CSV:Field1
> > SQL:Field2 = CSV:Field2
> > SQL:Field3 = CSV:Field3
> > SQL:Field4 = User Entered Variable from Form
> >
> > How is this done in CCS?
> >
> > Tim Morrison
> >
> >
>
>
|
|
|
|
|
