0

We have survey data for each survey on a server. The data is separated with one method I can download the column names (via pooling) and with another I can download the data (via SSE). I have accomplished to write a procedure that creates a datatable dynamically for each survey I choose to download. Now I have to get the data into that table. For this I have streamed the data via SSE into a List(of String) where each element comprises a comma separated string.

This looks like

For Each x As String In stringList
    Console.WriteLine(x)
Next

would give me

(1)data:[1,2,1,5,2,6,John,Winchester,234]
(2)data:[5,3,2,4,1,6,Mike,Lenchester,555]
...

Each Element of the List is a dataset I have to put into one column of my datatable. So I guess I have to loop through the List Object and the pick each element between the comma and write them into the columns.

So my problem now is to get the data into the database.

Usually I provide an approach but this time I have no clue how to start.

I tried to experiment with this 

.Parameters.Add("@id", SqlDbType.varchar(max)).Value = x

but ended up in frustration.

Could anyone give me something to start with? The data size is up to 500MB if I store it in a .txt file.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ruedi
  • 5,365
  • 15
  • 52
  • 88
  • You'll need to parse the string and put the value in a proper dataset. Example, splitting it up by coma (if that is the rule) with the [Split method](https://learn.microsoft.com/en-us/dotnet/api/system.string.split?view=netframework-4.7.2) – the_lotus Dec 12 '18 at 17:27
  • Not 100% clear on your question, but if you are looking to parse a delimited string into columns, take a peek at https://stackoverflow.com/questions/52100376/how-to-extract-values-from-column-and-update-result-in-another-column/52100846#52100846 Here, we show with or without a function. – John Cappelletti Dec 12 '18 at 17:30
  • You need to provide more details about what you are trying to do here. – Sean Lange Dec 12 '18 at 17:31
  • Thank you for the answers. I will have a look at what you suggest. I have edited my question to provide more details. I hope this is fulfilling – ruedi Dec 12 '18 at 17:39
  • You could serialize the data into XML (or JSON post 2016) and then shred the data within a stored procedure. If it's a lot of data, something like SqlBulkCopy, bulk insert, or SSIS might be a better apporoach. – Xedni Dec 12 '18 at 17:40
  • I think you said you dynamically create the table. I would just have a survey table, a question table and an answer table. this would work for any survey. – the_lotus Dec 12 '18 at 17:47
  • The alternative is to have one table where each row holds the column names (questions) of a survey and to hold all survey data into another table. Each query we want to work with would have to create a new table where you have to transfrom the row data into column names and import the data accordingly. With my approach I have the datatables ready to go already. That was why I did it that way. – ruedi Dec 12 '18 at 17:58
  • Is the data always six integers, two strings, and an integer, and always in that order? – Andrew Morton Dec 12 '18 at 18:54
  • Unfortunatly not. Each Survey has a different structure. Even a different column count – ruedi Dec 12 '18 at 19:47
  • So you need to transfer large DataTable to a DB table? – IvanH Dec 13 '18 at 13:48

0 Answers0