.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!

 

[]

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.


| 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]

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

 

[]

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.WriteLine(s.Substring(start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLine(s.Substring(start, s.Length - start));

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

hth,

me

 

[shriop]

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]

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

 

[]

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

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

;^)


| Sorry, I have to write this code myself rather than use a third party
| package. I'm still looking for a good code sample...
|
| | >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]

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

 

[shriop]

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.WriteLine(s.Substring(start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLine(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?

 

[Rasta]

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

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.WriteLine(s.Substring(start, m.Index - start));
start = m.Index + 1;
}
Console.WriteLine(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?

 

[Paul Clement]

¤ 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\TextFiles\OrderDetails.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

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


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[]

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...like a clue and/or just a LITTLE knowlege
of c# !!!

but for you c# (or otherwise) challenged:

Private Function splitFields(ByVal 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(record)
If Not fields Is Nothing Then
Dim field As String
For Each field In fields
Console.WriteLine(field)
Next
End If
Next
End Sub

 

[]

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

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


| | 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...like a clue and/or just a LITTLE
knowlege
| of c# !!!
|
| but for you c# (or otherwise) challenged:
|
| Private Function splitFields(ByVal 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(record)
| If Not fields Is Nothing Then
| Dim field As String
| For Each field In fields
| Console.WriteLine(field)
| Next
| End If
| Next
| End Sub
|
|
|

 

[Rasta]

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.OpenTextFile(Application.StartupPath & "\filename.csv")

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

pgBar.Maximum = lCount
pgBar.Value = 0

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

Do While Ta.AtEndOfStream = 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.SqlDataReader
Scrip = Trim(ArrA(1))
Series = Trim(ArrA(2))
Rate = ArrA(7)
Try
cmd = New SqlClient.SqlCommand("pts_updSRates 'I','" & Scrip &
"','" & CStr(Rate) & "','" _
& Now & "'," & CStr(Rate) & ",'AN'")
cmd.Connection = con
rsDR1 = cmd.ExecuteReader
rsDR1.Read()
If rsDR1.GetString(0) = "Success" Then
End If
rsDR1.Close()
Catch ex As Exception
MsgBox(ex.Message)
rsDR1.Close()
End Try

End Sub

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

 

[Rasta]

So this is you trying to 'help'- Do all of us a favor and stop.

 

[Rasta]

Thanks, I'll give that a try.

 

[]

| Private Function splitFields(ByVal 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:

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

 

[]

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

the insult was not to you, rasta. i'm trying to help you.

| 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'.

you asked for the c# code to be converted to vb.net...so i did. did you not
run the code and test it? did it not work? i'd usually stay away from biting
the hand that feeds you...i'm going to assume your response is a reaction to
the *perceived* insult you think i threw your way.

<snip lots of code>

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

true enough...however, i assumed you were familiar enough with c# to
correctly convert the example to vb.net. it compiles and runs fine in either
example i gave.

again, i'll ignore the "einstein" insult as i feel it is a reactionary
defensive comment. you'll be able to clear up this assumption in your next
post. if i'm correct, i'll be happy to help you with integrating the example
with your specific validation routine or even setting up a dts package...if
i'm NOT correct and you truly intend to be insulting, i'll quietly go away.

hth,

me