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
Solved

Parse and save

Posted on 2011-09-17
13
272 Views
Last Modified: 2012-05-12
Hi, Experts -
I need help with handling a (potentially) long character string that represents all of the results submitted by a survey taker.
Below is a sample of such a string.
In this particular survey, there are seven questions delimited by three (3) pound-signs (###).
The values passed for each question are separated by commas, and include:
1. Survey title,
2. Survey date,
3. Survey response time (unique for each question),
4. Survey Item,
5. Survey Item Type,
6. Survey Item Response(s) - multiple responses separated by |#|
Again - This is all passed to me as one single string of characters.
QUESTION:
I need to save these elements into a table that has columns defined with the same 6 elements as the values listed above.  Can someone provide me with suggested code (I use ASP/VBScript) that would parse through the character string and save the six values for adding to a table?  (No need to provide database connectivity or interaction code - I can do that part)  
Mahalo plenty!
###My Test Survey,9/17/2011,10:57:09,Question2_1,multipleresponse,read MyPaper (occasionally or as a subscriber)|#|wrote for MyPaper (articles or poetry)|#|wrote to MyPaper (letter to editor)|#|imaged for MyPaper (drawing, photos)|#|labored for MyPaper (volunteer staff/grunt doing layout, typesetting, distribution)|#|hung out at MyPaper (ate, used facilities, recreational activity...you know what we mean) ###My Test Survey,9/17/2011,10:57:22,Question3_1,essay,Testing, testing, testing. ###My Test Survey,9/17/2011,10:57:25,Question4_1,essay,Testing, testing, testing. ###My Test Survey,9/17/2011,10:57:27,Question5_1,essay,Testing, testing, testing. ###My Test Survey,9/17/2011,10:57:28,Question6_1,essay,Testing, testing, testing. ###My Test Survey,9/17/2011,10:57:30,Question7_1,essay,Testing, testing, testing. ###My Test Survey,9/17/2011,10:57:33,Question8_1,essay,Testing, testing, testing.

Open in new window

0
Comment
Question by:OGSan
  • 8
  • 5
13 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 36555425
Dim myFields()
Dim mySQL

myFields = Split(ResponseString, ",")

mySQL = "INSERT INTO myTable (myField1, myField2, myField3, myField4, myField5, myField6) VALUES ('" & myField(0) & "', '" & myField(1) & "', '" & myField(2) & "', '" & myField(3) & "', '" & myField(4) & "', '" & myField(5) & "')"

This is just the concept. You probably have key fields and such you'd want to add, or you may be using ADODB with a recordset and be setting field values that way, but the split and using the split array is the concept here.
0
 
LVL 1

Author Comment

by:OGSan
ID: 36555592
Wow, so the array is auto-magically created when the Split occurs?  That's nifty.  I'll test this out and report back soonest.  Thank you!
0
 
LVL 20

Expert Comment

by:dsacker
ID: 36556322
If possible, I would recommend that you use something else besides a comma for your delimiter. Perhaps the pipe character (|). That is because a comma often is in a string itself.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:OGSan
ID: 36556830
Thanks for the suggestion, I'll see if I can get that changed.
0
 
LVL 1

Author Comment

by:OGSan
ID: 36556865
Hi, dsacker - I used your instruction, but am getting an error on that line reading, "Type mismatch."
Here is what the code looks like leading up to your instruction:
 
vstrSurveyLine = Request.Form("strSurveyLine")
	if	vstrSurveyLine <> "" then

' D E B U G  - verify values passed from QM_ResultsFromQuiz.asp
'		response.Write("D E B U G  -  B G N" & "<br />")
'		response.Write("1. vstrSurveyLine 	= "  & vstrSurveyLine 	& "<br />")
'		response.Write("D E B U G  -  E N D" & "<br />")

		' Parse string and place elements into an array
		dim myFields()
		myFields = Split(vstrSurveyLine, "|")
' D E B U G
		response.Write("D E B U G  -  B G N" & "<br />")
		response.write("parse and split response string <br />")
		response.write(myFields.value(0)& "<br />")
		response.write(myFields.value(1)& "<br />")
		response.write(myFields.value(2)& "<br />")
		response.write(myFields.value(3)& "<br />")
		response.write(myFields.value(4)& "<br />")
		response.write(myFields.value(5)& "<br />")
		response.write(myFields.value(6)& "<br />")
		response.Write("D E B U G  -  E N D" & "<br />")

Open in new window

0
 
LVL 1

Author Comment

by:OGSan
ID: 36556867
Error references line 11 above
0
 
LVL 1

Author Comment

by:OGSan
ID: 36556870
...and I was able to change the delimiter character from commas to pipes.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 36558048
Did you Dim the myFields?
0
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 36558144
Also, besides the Dim myFields(), you may need to Set myFields = Split ...

Play with that, too (going on memory).
0
 
LVL 20

Expert Comment

by:dsacker
ID: 36558145
I forget sometimes this is classic ASP. With ASP.NET (and VB.NET) you don't do the "Set" command.
0
 
LVL 1

Author Comment

by:OGSan
ID: 36558492
I Dim'd myFields()...and tried using Set myFields = Split...but same error occurs on that instruction.
Shucks.
0
 
LVL 1

Author Comment

by:OGSan
ID: 36558521
I inserted () after myFields() = Split...and now I got a "Subscript out of range" error.  That's progress, I guess...!  :-)
0
 
LVL 1

Author Comment

by:OGSan
ID: 36558536
I dropped the use of () and just used myFields - and I got passed that Split instruction...yeehaw!  Now I'm getting something else - but I'm awarding points anyway.
Thanks for tossing out enough clues to get me going again, dsacker.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql to convert to date IF entry is in date format 4 93
Select distinct 25 115
Code Manager | Snippits 2 36
Set time on Session (ASP) 14 22
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question