.net method that writes .csv file to SQL Server

[Rasta]

Hi,

I want to create a vb.net procedure that opens a .csv file from a specified
directory and then load that file into a SQL server table located on a
remote server. Can someone point me towards a code sample that does this?
Can I set this up to run at a specified time on windows 2000 server, and how
do I do this. I"m familiar with task schedular in XP but don't know what the
equivalent of this is in 2000 server.

Thanks!

[]

Re: .net method that writes .csv file to SQL Server

why use anything other than sql server for this? you can use dts or other
equivalent method to have sql server routinely import the file's data.


"Rasta" <aboehme@netgat e.net> wrote in message
news:emlr8GTmFH A.1968@TK2MSFTN GP14.phx.gbl...
| Hi,
|
| I want to create a vb.net procedure that opens a .csv file from a
specified
| directory and then load that file into a SQL server table located on a
| remote server. Can someone point me towards a code sample that does this?
| Can I set this up to run at a specified time on windows 2000 server, and
how
| do I do this. I"m familiar with task schedular in XP but don't know what
the
| equivalent of this is in 2000 server.
|
| Thanks!
|
|

[Rasta]

Re: .net method that writes .csv file to SQL Server

I need to do in-depth validation of each record in the csv file before
upload to SQL. This, among other reasons is why I am using .net.
I"m looking for a code sample that loops through the csv file and then
appends it to SQL tables

thanks
"" <a@b.com> wrote in message news:JivIe.4571 $_41.4354@fe02. lga...[color=blue]
> why use anything other than sql server for this? you can use dts or other
> equivalent method to have sql server routinely import the file's data.
>
>
> "Rasta" <aboehme@netgat e.net> wrote in message
> news:emlr8GTmFH A.1968@TK2MSFTN GP14.phx.gbl...
> | Hi,
> |
> | I want to create a vb.net procedure that opens a .csv file from a
> specified
> | directory and then load that file into a SQL server table located on a
> | remote server. Can someone point me towards a code sample that does
> this?
> | Can I set this up to run at a specified time on windows 2000 server, and
> how
> | do I do this. I"m familiar with task schedular in XP but don't know what
> the
> | equivalent of this is in 2000 server.
> |
> | Thanks!
> |
> |
>
>[/color]

[]

Re: .net method that writes .csv file to SQL Server

here's a c# snip:

Regex r = new Regex(",(?=([^\"]*"[^"]*")*(?![^"]*"))");
string s = "\"a",b,"c, d, e",,f";
int start = 0;
foreach (Match m in r.Matches(s))
{
Console.WriteLi ne(s.Substring( start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLi ne(s.Substring( start, s.Length - start));

converts easily and makes for clean, fast, efficient coding.

hth,

me

[shriop]

Re: .net method that writes .csv file to SQL Server

I would suggest using a csv parser like the one I sell,
http://www.csvreader.com , to loop over the rows one by one, do your
extended validation, and then send the successful rows out to another
csv file. Then, you can send the data into the sql server using dts,
which should be the fastest way. Optionally, you can do single insert
statements on a row by row basis into the database like what you're
asking for, but it will go pretty slow.

[Rasta]

Re: .net method that writes .csv file to SQL Server

Sorry, I have to write this code myself rather than use a third party
package. I'm still looking for a good code sample...

"shriop" <shriop@hotmail .com> wrote in message
news:1123205120 .703296.287690@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I would suggest using a csv parser like the one I sell,
> http://www.csvreader.com , to loop over the rows one by one, do your
> extended validation, and then send the successful rows out to another
> csv file. Then, you can send the data into the sql server using dts,
> which should be the fastest way. Optionally, you can do single insert
> statements on a row by row basis into the database like what you're
> asking for, but it will go pretty slow.
>[/color]

[]

Re: .net method that writes .csv file to SQL Server

i promise the example i posted previously works like a charm.

i wouldn't pay for such a simple operation anyway.

;^)


"Rasta" <aboehme@netgat e.net> wrote in message
news:uyBpmNXmFH A.3288@TK2MSFTN GP09.phx.gbl...
| Sorry, I have to write this code myself rather than use a third party
| package. I'm still looking for a good code sample...
|
| "shriop" <shriop@hotmail .com> wrote in message
| news:1123205120 .703296.287690@ z14g2000cwz.goo glegroups.com.. .
| >I would suggest using a csv parser like the one I sell,
| > http://www.csvreader.com , to loop over the rows one by one, do your
| > extended validation, and then send the successful rows out to another
| > csv file. Then, you can send the data into the sql server using dts,
| > which should be the fastest way. Optionally, you can do single insert
| > statements on a row by row basis into the database like what you're
| > asking for, but it will go pretty slow.
| >
|
|

[J. Clay]

Re: .net method that writes .csv file to SQL Server

Wy not validate into a table in SQL and then use DTS. I do this often and
it works great. You can set up a stored procedure to do your processing and
at the end call the DTS package from within your stored procedure to create
the CSV file.

Jim


"Rasta" <aboehme@netgat e.net> wrote in message
news:enVQ3CUmFH A.2904@TK2MSFTN GP14.phx.gbl...[color=blue]
>I need to do in-depth validation of each record in the csv file before
>upload to SQL. This, among other reasons is why I am using .net.
> I"m looking for a code sample that loops through the csv file and then
> appends it to SQL tables
>
> thanks
> "" <a@b.com> wrote in message news:JivIe.4571 $_41.4354@fe02. lga...[color=green]
>> why use anything other than sql server for this? you can use dts or other
>> equivalent method to have sql server routinely import the file's data.
>>
>>
>> "Rasta" <aboehme@netgat e.net> wrote in message
>> news:emlr8GTmFH A.1968@TK2MSFTN GP14.phx.gbl...
>> | Hi,
>> |
>> | I want to create a vb.net procedure that opens a .csv file from a
>> specified
>> | directory and then load that file into a SQL server table located on a
>> | remote server. Can someone point me towards a code sample that does
>> this?
>> | Can I set this up to run at a specified time on windows 2000 server,
>> and
>> how
>> | do I do this. I"m familiar with task schedular in XP but don't know
>> what
>> the
>> | equivalent of this is in 2000 server.
>> |
>> | Thanks!
>> |
>> |
>>
>>[/color]
>
>
>[/color]

[shriop]

Re: .net method that writes .csv file to SQL Server

Am I missing something in your proposed solution? I had to assume a
bunch of escapes just to get it to compile.

Regex r = new
Regex(",(?=([^\\\"]*\\\"[^\\\"]*\\\")*(?![^\\\"]*\\\"))");
string s = "\"a\",b,\" c, d, e\",,f";
int start = 0;
foreach (Match m in r.Matches(s))
{
Console.WriteLi ne(s.Substring( start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLi ne(s.Substring( start, s.Length - start));

results in this:
"a"
b
"c
d
e"

f

To me, this it totally invalid and doesn't get anywhere. So what'd I
miss?

wrote:[color=blue]
> i promise the example i posted previously works like a charm.
>
> i wouldn't pay for such a simple operation anyway.
>
> ;^)
>
>
> "Rasta" <aboehme@netgat e.net> wrote in message
> news:uyBpmNXmFH A.3288@TK2MSFTN GP09.phx.gbl...
> | Sorry, I have to write this code myself rather than use a third party
> | package. I'm still looking for a good code sample...
> |
> | "shriop" <shriop@hotmail .com> wrote in message
> | news:1123205120 .703296.287690@ z14g2000cwz.goo glegroups.com.. .
> | >I would suggest using a csv parser like the one I sell,
> | > http://www.csvreader.com , to loop over the rows one by one, do your
> | > extended validation, and then send the successful rows out to another
> | > csv file. Then, you can send the data into the sql server using dts,
> | > which should be the fastest way. Optionally, you can do single insert
> | > statements on a row by row basis into the database like what you're
> | > asking for, but it will go pretty slow.
> | >
> |
> |[/color]

[Rasta]

Re: .net method that writes .csv file to SQL Server

I'm sorry, but I don't know C# enough for this solution to help me out at
all (and I"m in a real time crunch to get this done in vb.net). I would
really appreciate it if someone could point me to a code sample for vb.net
or provide a code snippet.

Thanks

"shriop" <shriop@hotmail .com> wrote in message
news:1123252165 .635174.259830@ g49g2000cwa.goo glegroups.com.. .
Am I missing something in your proposed solution? I had to assume a
bunch of escapes just to get it to compile.

Regex r = new
Regex(",(?=([^\\\"]*\\\"[^\\\"]*\\\")*(?![^\\\"]*\\\"))");
string s = "\"a\",b,\" c, d, e\",,f";
int start = 0;
foreach (Match m in r.Matches(s))
{
Console.WriteLi ne(s.Substring( start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLi ne(s.Substring( start, s.Length - start));

results in this:
"a"
b
"c
d
e"

f

To me, this it totally invalid and doesn't get anywhere. So what'd I
miss?

wrote:[color=blue]
> i promise the example i posted previously works like a charm.
>
> i wouldn't pay for such a simple operation anyway.
>
> ;^)
>
>
> "Rasta" <aboehme@netgat e.net> wrote in message
> news:uyBpmNXmFH A.3288@TK2MSFTN GP09.phx.gbl...
> | Sorry, I have to write this code myself rather than use a third party
> | package. I'm still looking for a good code sample...
> |
> | "shriop" <shriop@hotmail .com> wrote in message
> | news:1123205120 .703296.287690@ z14g2000cwz.goo glegroups.com.. .
> | >I would suggest using a csv parser like the one I sell,
> | > http://www.csvreader.com , to loop over the rows one by one, do your
> | > extended validation, and then send the successful rows out to another
> | > csv file. Then, you can send the data into the sql server using dts,
> | > which should be the fastest way. Optionally, you can do single insert
> | > statements on a row by row basis into the database like what you're
> | > asking for, but it will go pretty slow.
> | >
> |
> |[/color]

[Paul Clement]

Re: .net method that writes .csv file to SQL Server

On Thu, 4 Aug 2005 13:18:53 -0700, "Rasta" <aboehme@netgat e.net> wrote:

¤ Hi,
¤
¤ I want to create a vb.net procedure that opens a .csv file from a specified
¤ directory and then load that file into a SQL server table located on a
¤ remote server. Can someone point me towards a code sample that does this?
¤ Can I set this up to run at a specified time on windows 2000 server, and how
¤ do I do this. I"m familiar with task schedular in XP but don't know what the
¤ equivalent of this is in 2000 server.

You can use SQL w/BULK INSERT:

BULK INSERT Northwind.dbo.[Order Details] FROM 'e:\My Documents\TextF iles\OrderDetai ls.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

http://msdn.microsoft.com/library/de...ba-bz_4fec.asp

AFAIK, Windows 2000 Server does have a Schedule Tasks Control Panel applet.


Paul
~~~~
Microsoft MVP (Visual Basic)

[]

Re: .net method that writes .csv file to SQL Server

"shriop" <shriop@hotmail .com> wrote in message
news:1123252165 .635174.259830@ g49g2000cwa.goo glegroups.com.. .
Am I missing something in your proposed solution? I had to assume a
bunch of escapes just to get it to compile.

you indeed ARE missing something...lik e a clue and/or just a LITTLE knowlege
of c# !!!

but for you c# (or otherwise) challenged:

Private Function splitFields(ByV al csv As String) As String()
Dim exp As New Regex(",(?=(?:[^""]*\""[^""]*"")*(?![^""]*""))")
Return exp.split(csv)
End Function

Private Function splitLine(ByVal csv As String) As String()
Dim exp As New Regex("\r\n")
Return exp.Split(csv)
End Function

Public Sub Main()
Dim data As String() = splitLine( _
"""a"",""b"","" c"",""d"",""e"" ,"""",""f"""
& vbCrLf & _
"""g"",""h"","" i"",""j"",""k"" ,"""",""l"""
_
)
If data Is Nothing Then Return
Dim record As String
For Each record In data
Dim fields As String() = splitFields(rec ord)
If Not fields Is Nothing Then
Dim field As String
For Each field In fields
Console.WriteLi ne(field)
Next
End If
Next
End Sub

[]

Re: .net method that writes .csv file to SQL Server

oh, and need i keep you from bitching about compile errors by reminding you
to import system.text.reg ularexpressions ?

you truly need to tone down the attitude toward those trying to help others!


"" <a@b.com> wrote in message news:zcNIe.2146 $J7.1334@fe07.l ga...
| "shriop" <shriop@hotmail .com> wrote in message
| news:1123252165 .635174.259830@ g49g2000cwa.goo glegroups.com.. .
| Am I missing something in your proposed solution? I had to assume a
| bunch of escapes just to get it to compile.
|
| you indeed ARE missing something...lik e a clue and/or just a LITTLE
knowlege
| of c# !!!
|
| but for you c# (or otherwise) challenged:
|
| Private Function splitFields(ByV al csv As String) As String()
| Dim exp As New Regex(",(?=(?:[^""]*\""[^""]*"")*(?![^""]*""))")
| Return exp.split(csv)
| End Function
|
| Private Function splitLine(ByVal csv As String) As String()
| Dim exp As New Regex("\r\n")
| Return exp.Split(csv)
| End Function
|
| Public Sub Main()
| Dim data As String() = splitLine( _
|
"""a"",""b"","" c"",""d"",""e"" ,"""",""f"""
| & vbCrLf & _
|
"""g"",""h"","" i"",""j"",""k"" ,"""",""l"""
| _
| )
| If data Is Nothing Then Return
| Dim record As String
| For Each record In data
| Dim fields As String() = splitFields(rec ord)
| If Not fields Is Nothing Then
| Dim field As String
| For Each field In fields
| Console.WriteLi ne(field)
| Next
| End If
| Next
| End Sub
|
|
|

[Rasta]

Re: .net method that writes .csv file to SQL Server

Ah, the small minded resort to insults when they have nothing intelligent to
say...

Below is an example of what I'm looking for just for your enlightenment.
Maybe this will help you out next time you try to offer one of your pathetic
'solutions'.

Dim lCount As Integer = 0
Ta = fSys.OpenTextFi le(Application. StartupPath & "\filename.csv" )

Do While Ta.AtEndOfStrea m = False
Ta.ReadLine()
lCount += 1
Loop

pgBar.Maximum = lCount
pgBar.Value = 0

Ta = fSys.OpenTextFi le(Application. StartupPath & "\filename.csv" )

Do While Ta.AtEndOfStrea m = False
strRec = Ta.ReadLine
arrA = Split(strRec, ",")
FillData(arrA)
If pgBar.Value < pgBar.Maximum Then
pgBar.Value += 1
End If

DoEvents()
Loop
Catch ex As Exception
con.Close()
End Try
End Sub

Private Sub FillData(ByVal ArrA As Object)

Dim rsDR1 As SqlClient.SqlDa taReader
Scrip = Trim(ArrA(1))
Series = Trim(ArrA(2))
Rate = ArrA(7)
Try
cmd = New SqlClient.SqlCo mmand("pts_updS Rates 'I','" & Scrip &
"','" & CStr(Rate) & "','" _
& Now & "'," & CStr(Rate) & ",'AN'")
cmd.Connection = con
rsDR1 = cmd.ExecuteRead er
rsDR1.Read()
If rsDR1.GetString (0) = "Success" Then
End If
rsDR1.Close()
Catch ex As Exception
MsgBox(ex.Messa ge)
rsDR1.Close()
End Try

End Sub

ps: you may want to debug your 'code' before posting next time, Einstein.

"" <a@b.com> wrote in message news:zcNIe.2146 $J7.1334@fe07.l ga...[color=blue]
> "shriop" <shriop@hotmail .com> wrote in message
> news:1123252165 .635174.259830@ g49g2000cwa.goo glegroups.com.. .
> Am I missing something in your proposed solution? I had to assume a
> bunch of escapes just to get it to compile.
>
> you indeed ARE missing something...lik e a clue and/or just a LITTLE
> knowlege
> of c# !!!
>
> but for you c# (or otherwise) challenged:
>
> Private Function splitFields(ByV al csv As String) As String()
> Dim exp As New Regex(",(?=(?:[^""]*\""[^""]*"")*(?![^""]*""))")
> Return exp.split(csv)
> End Function
>
> Private Function splitLine(ByVal csv As String) As String()
> Dim exp As New Regex("\r\n")
> Return exp.Split(csv)
> End Function
>
> Public Sub Main()
> Dim data As String() = splitLine( _
>
> """a"",""b"","" c"",""d"",""e"" ,"""",""f"""
> & vbCrLf & _
>
> """g"",""h"","" i"",""j"",""k"" ,"""",""l"""
> _
> )
> If data Is Nothing Then Return
> Dim record As String
> For Each record In data
> Dim fields As String() = splitFields(rec ord)
> If Not fields Is Nothing Then
> Dim field As String
> For Each field In fields
> Console.WriteLi ne(field)
> Next
> End If
> Next
> End Sub
>
>
>[/color]

[]

Re: .net method that writes .csv file to SQL Server

| Private Function splitFields(ByV al csv As String) As String()
| Dim exp As New Regex(",(?=(?:[^""]*\""[^""]*"")*(?![^""]*""))")
| Return exp.split(csv)
| End Function

this function will still work, however i just noticed i left in 1
backslash...the corrected pattern is:

",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"