parse file import into DB

[williamsba]

I got a word document thats 434 pages of drinking games that I want to import into my SQL Server DB. Here is the format of the text:

ABSOLUTE STRESS
1 oz Asolute Vodka / 1 oz Peach Schnapps
1/2 pineapple juice /1/2 cranberry juice
ice (shake) / rocks or blended \ frozen
ABSOLUT BLUE LEMONADE
1 oz Absolute / 1 oz Blue Curacao or Schnapps
fill with lemonade mix
ice (shake) / rocks or blended \ frozen
(Thanks Crystal Klecha)
AFFAIR
2 oz Strawberry Schnapps
1/2 orange juice / 1/2 cranberry juice
ice / rocks or up
AFFAIR
2 oz Strawberry Schnapps
1/3 orange juice / 1/3 cranberry juice / 1/3 club soda
ice / rocks or up


So you can see there is a pattern where the title of the game is always in CAPS.

So I wrote a script that determines the ANSI values, and if it is all CAPS then it recognizes it as a title:


<%
Dim arrTitle(1000), arrRecipe(1000)
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Open ="Provider=SQLOLEDB;Data Source=localhost;User Id=sa;Initial Catalog=cbn;"
conn.Execute("SELECT * FROM drink")

Set fso=Server.CreateObject("Scripting.FileSystemObject")
set f = fso.opentextfile("C:\inetpub\

http://wwwroot\blah.txt",1,false)

x = 1
do while f.AtEndOfStream <> true
mystring = f.ReadLine
myflag = true

for i = 1 to len(mystring)

mychar=mid(mystring,i,1)

If asc(mychar) >= 65 and asc(mychar) <= 90 Then

Else
myflag = false
End If

next

If myflag = true then
'Its a title
arrTitle(x) = mystring
Else
'Its a recipe
arrTitle(x) = mystring
End if
x = x + 1
loop

For J = LBound(arrTitle) To UBound(arrTitle)
response.write arrTitle(j) & "<BR>"
Next
%>

That works fine, My question is how can I insert the array into the database keeping the title and the recipe together in the same record? Any help would be awesome, thanks!

Brad Williams
Webmaster
2d Force Service Support Group
United States Marine Corps

 

[shaddow]

Should be something like this

xt = 1
xr = 1
do while f.AtEndOfStream <> true
    mystring = f.ReadLine
    myflag = true

    for i = 1 to len(mystring)

        mychar=mid(mystring,i,1)

        If asc(mychar) >= 65 and asc(mychar) <= 90 Then

        Else
            myflag = false
        End If

    next

    If myflag = true then
        'Its a title
        arrTitle(x) = mystring
        xt=xt+1
    Else
        'Its a recipe
        arrRecipe(x) = mystring
        xr=xr+1
    End if
loop

For J = LBound(arrTitle) To UBound(arrTitle)
        response.write arrTitle(j) & "<BR>"
Next
For J = LBound(arrRecipe) To UBound(arrRecipe)
        response.write arrRecipe(j) & "<BR>"
Next

But this will work if you knw how many recipes a title has...
Do you knw what recipes is for what titles?
It's something like title folowed by it's recipes then title and recipes again?

________
George, M