Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
COURSE of the MONTH
12 days, 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
Just a quick little trick I learned recently. Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:
(CODE)
I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform postback. Many times you might need to perform some action on an ASP.NET po…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month12 days, left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.