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!
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
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video.
If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
- Document Management
- Document Imaging
- Printers and Scanners
- Software-Other
- Images and Photos
- Photos / Graphics Software, OCR, *tiff, *PaperPort, *PDF
Suggested Courses
Course of the Month8 days, 18 hours left to enroll
604 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.