On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
7 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Load string into datatable
Posted on 2008-06-17
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!!!
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!!!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
6
12 Comments
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?
The string is tab-delimited ... should each element be a different row? Or each a different field in a single row?
The table has the same column structure as the string:
DATE-VALUE1[float]-VALUE2[
the string has the same columns per row, but tab delimited.
Thanks!!
DATE-VALUE1[float]-VALUE2[
the string has the same columns per row, but tab delimited.
Thanks!!
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
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
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
No, you're adding 1000 parameters to the one statement.
Do you really have 1000 fields in your table?
Do you really have 1000 fields in your table?
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
Could you explain a little more how to add the parameters?
Thanks
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.
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.
4 columns -- that sounds good.
What are the 4 columns -- so the code I write will be close to correct for you?
What are the 4 columns -- so the code I write will be close to correct for you?
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
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s quite interesting for me as I worked with Excel using vb.net for some time.
Here are some topics which I know want to share with others whom this might help.
First of all if you are working with Excel then you need to Download the Following
…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Course of the Month7 days, 2 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.