Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1101
  • Last Modified:

Load string into datatable

Hi Experts!

I have a tab delimited string loaded from an http site. This is not a file, it is a string obtained trough and HttpWebRequest and contains serveral lines of tab delimited values.

Which would be the best method to load this into an SQL server table?

Thanks!!!
0
glopezz
Asked:
glopezz
Advertise Here
  • 6
  • 6
1 Solution
 
Daniel WilsonCommented:
What is the structure of your table?

The string is tab-delimited ... should each element be a different row?  Or each a different field in a single row?
0
 
glopezzAuthor Commented:
The table has the same column structure as the string:

DATE-VALUE1[float]-VALUE2[float]-VALUE3[float]

the string has the same columns per row, but tab delimited.

Thanks!!
0
 
Daniel WilsonCommented:
This should get you close ... I've got some syntax a bit off, but the concepts are here.
dim arVals() as string
dim SQL as string
arVals = Split(TabDelimitedString, vbTab)
 
'set up a command object w/ connection string ... details omitted
 
SQL = "Insert Into MyTable(Field1, Field2, ...FieldN) Values (@Val1, @Val2, ...@ValN)"
 
cmd.CommandText = Sql
 
'next block is psuedocode
foreach Val in arVals
   cmd.Parameters.Append New Param for this value
next
 
cmd.executeNonQuery

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
glopezzAuthor Commented:
Thanks Daniel

Could you be a little more specific on the line:

cmd.Parameters.Append New Param for this value

?

If we have, let's say, 1000 lines in the string, are we sending 1000 sql commands to the server?

Thanks
0
 
Daniel WilsonCommented:
No, you're adding 1000 parameters to the one statement.

Do you really have 1000 fields in your table?
0
 
glopezzAuthor Commented:
This is minute by minute information, at the end of one day we would have 60X60X24 fields.

Could you explain a little more how to add the parameters?

Thanks
0
 
Daniel WilsonCommented:
Please tell me that really is 60 * 60 * 24 ROWS, not fields!  

Your table is only going to support about 8000 bytes in a given row.

I'll come back in a few minutes w/ code based on the idea of having 1 Row per second, not one field per second.
0
 
glopezzAuthor Commented:
Sorry Daniel, I thought you were asking about rows! They are fields :P

0
 
glopezzAuthor Commented:
It is 60 * 60 * 24 ROWS, with 4 columns per row.. sorry again! This is how it should work.
0
 
Daniel WilsonCommented:
4 columns -- that sounds good.

What are the 4 columns -- so the code I write will be close to correct for you?
0
 
glopezzAuthor Commented:
first one is a datetime, the others are float values (numbers with 3 decimals).
0
 
Daniel WilsonCommented:
This one should be close
dim arVals() as string
dim SQL as string
dim i as Int32
 
arVals = Split(TabDelimitedString, vbTab)
 
'set up a command object w/ connection string ... details omitted
 
SQL = "Insert Into MyTable(Field1, Field2, Field3, Field4) Values (@Val1, @Val2, @val3, @Val4)"
 
cmd.CommandText = Sql
 
cmd.Parameters.append (new SqlClient.SqlParameter("@Val1", SqlDBType.DateTime
cmd.Parameters.append (new SqlClient.SqlParameter("@Val2", SqlDBType.Float
cmd.Parameters.append (new SqlClient.SqlParameter("@Val3", SqlDBType.Float
cmd.Parameters.append (new SqlClient.SqlParameter("@Val4", SqlDBType.Float
 
for i = 0 to (arVals.LBound-1 ) /4
   cmd.Parameters("@Val1).Value = arVals(i*4)
   cmd.Parameters("@Val2).Value = arVals(i*4) +1
   cmd.Parameters("@Val3).Value = arVals(i*4) +2
   cmd.Parameters("@Val4).Value = arVals(i*4) +3
 
   cmd.ExecuteNonQuery
next i

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Go Premium
  • 6
  • 6
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now