string validation/ or something...

Discussion in 'ASP General' started by Ray Godfrey, Apr 19, 2005.

  1. Ray Godfrey

    Ray Godfrey Guest

    Hi there,

    Just a simple question, I think.

    I'm writing a program that takes information from a text file and uses
    that info to query a DB. Sounds simple enough.....

    The data would look something like this

    [t_customers]
    cust_ref=MOL01
    cust_addr_1=XYZ
    Cust_Addr_2=ABC
    Cust_Addr_3=LMN
    Cust_Addr_4=QRS
    Cust_Addr_5=DEF
    etc

    What I'd like to do is firstly remove the [] from the table name, and
    secondly break the subsequent strings in two at the '=' therefore
    having both a field name and data for each field.

    Any idea how I'd manage this?

    Cheers

    P
    Ray Godfrey, Apr 19, 2005
    #1
    1. Advertising

  2. This may do what you want, loosely.

    Dim aVariant
    Do While Not yourFileobject.AtEndOfStream
    aVariant = HandleLine(yourFileobject.ReadLine)
    If IsArray(aVariant) Then
    ''do what you want with the cust_addr_ type of data
    Response.Write aVariant(0) & " and " & aVariant(1)
    Else
    Response.Write "Table name: " & aVariant
    End If
    Loop


    Function HandleLine(line)
    If Left(line & " ",1) = "[" Then
    HandleLine = Replace(Replace(line, "[", ""), "]", "")
    Else
    HandleLine = Split(line, "=")
    End If
    End Function


    Ray at work


    "Ray Godfrey" <> wrote in message
    news:...
    > Hi there,
    >
    > Just a simple question, I think.
    >
    > I'm writing a program that takes information from a text file and uses
    > that info to query a DB. Sounds simple enough.....
    >
    > The data would look something like this
    >
    > [t_customers]
    > cust_ref=MOL01
    > cust_addr_1=XYZ
    > Cust_Addr_2=ABC
    > Cust_Addr_3=LMN
    > Cust_Addr_4=QRS
    > Cust_Addr_5=DEF
    > etc
    >
    > What I'd like to do is firstly remove the [] from the table name, and
    > secondly break the subsequent strings in two at the '=' therefore
    > having both a field name and data for each field.
    >
    > Any idea how I'd manage this?
    >
    > Cheers
    >
    > P
    Ray Costanzo [MVP], Apr 19, 2005
    #2
    1. Advertising

  3. Ray Godfrey

    Agoston Bejo Guest

    "Ray Godfrey" <> wrote in message
    news:...
    > Hi there,
    >
    > Just a simple question, I think.
    >
    > I'm writing a program that takes information from a text file and uses
    > that info to query a DB. Sounds simple enough.....
    >
    > The data would look something like this
    >
    > [t_customers]
    > cust_ref=MOL01
    > cust_addr_1=XYZ
    > Cust_Addr_2=ABC
    > Cust_Addr_3=LMN
    > Cust_Addr_4=QRS
    > Cust_Addr_5=DEF
    > etc
    >
    > What I'd like to do is firstly remove the [] from the table name, and



    See the Replace(...) VBScript function.


    > secondly break the subsequent strings in two at the '=' therefore
    > having both a field name and data for each field.



    See the Split(...) VBScript function.


    >
    > Any idea how I'd manage this?
    >
    > Cheers
    >
    > P
    Agoston Bejo, Apr 20, 2005
    #3
  4. Ray Godfrey

    Ray Godfrey Guest

    Hey Ray,

    Thanks for the few lines of code they work perfectly. My next problem
    though is turning the information I'm after taking from the file and
    formatting it into an SQL insert table. So....any ideas? Spare Change?

    P

    *** Sent via Developersdex http://www.developersdex.com ***
    Ray Godfrey, Apr 20, 2005
    #4
  5. Ray Godfrey wrote:
    > Hey Ray,
    >
    > Thanks for the few lines of code they work perfectly. My next problem
    > though is turning the information I'm after taking from the file and
    > formatting it into an SQL insert table. So....any ideas? Spare Change?
    >

    Show us the insert statement you want to create from the data you provided.
    That is always the place to start.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 20, 2005
    #5
  6. Ray Godfrey wrote:
    > Hey Ray,
    >
    > Thanks for the few lines of code they work perfectly. My next problem
    > though is turning the information I'm after taking from the file and
    > formatting it into an SQL insert table. So....any ideas? Spare Change?
    >

    Disregard my previous reply. I see what you're after.
    1. What is the type and version of database you are using.
    2. Your example data is all character (string) data. Will there be numeric
    or date/time data provided by these text files? Will the corresponding table
    fields be the same datatype (numeric or datetime)? Or are all the fields
    character fields?

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 20, 2005
    #6
  7. Ray Godfrey

    Ray Godfrey Guest

    (Ray Godfrey) wrote in message news:<>...
    > Hi there,
    >
    > Just a simple question, I think.
    >
    > I'm writing a program that takes information from a text file and uses
    > that info to query a DB. Sounds simple enough.....
    >
    > The data would look something like this
    >
    > [t_customers]
    > cust_ref=MOL01
    > cust_addr_1=XYZ
    > Cust_Addr_2=ABC
    > Cust_Addr_3=LMN
    > Cust_Addr_4=QRS
    > Cust_Addr_5=DEF
    > etc
    >
    > What I'd like to do is firstly remove the [] from the table name, and
    > secondly break the subsequent strings in two at the '=' therefore
    > having both a field name and data for each field.
    >
    > Any idea how I'd manage this?
    >
    > Cheers
    >
    > P



    I seem to be having trouble responding directly to your post bob, so
    i'll just have to reply to myself! this is what I tried to say earlier
    :

    Hi there Bob,

    To answer your questions:

    1. I'm using MS SQL Server V8.0
    2. Yeah there will be money, date/time data. All the DB will be
    money/datetime.

    Its pretty tricky. Using the code Ray gave me and a bot of my own I
    was able to populate the DB to an extent. The extent being *one*
    insert per loop. So I ended up filling one field in a row, moving to
    the next row and filling another field etc.

    I've written out what I think *may* work in pseudocode but I'm new to
    asp and can't turn it into code proper.

    something like

    <code>

    if readline includes "[" then
    this is a *tablename*
    so until next "["
    all readlines are "fieldame = data"
    -->smlArray = split(readline, =)
    -->store all smlArrays into bigArray
    -->so until loop finds another "["
    -->bigarray should be bigarray(smlarray0, smallarray1,smlarray2, etc)
    then using bigarray construct insert as
    insert into *tablename*(bigarray.smlarray1(0), bigarray.smlarray1(0),
    etc)
    values (bigarray.smlarray1(1), bigarray.smlarray1(1),etc)

    </code>

    Or maybe I'm completely wrong.
    I don't know if said or if i merely implied but there a numerous
    different tables. The file *will* hold data that will reflect the DBs
    structure.

    I'll post some more sample data in a differnt post, as this one seems
    a tad long for usenet!

    I hate having to be a brain leech like this, thanks for any help.

    P


    Bob said:

    Disregard my previous reply. I see what you're after.
    1. What is the type and version of database you are using.
    2. Your example data is all character (string) data. Will there be
    numeric
    or date/time data provided by these text files? Will the corresponding
    table
    fields be the same datatype (numeric or datetime)? Or are all the
    fields
    character fields?
    Ray Godfrey, Apr 20, 2005
    #7
  8. Ray Godfrey

    Ray Godfrey Guest

    Hey sorry everyone for reposting this message in its own thred, but
    usenet went mental last night and would'nt allow me respond to any
    posts directly. Here's what I've been trying to say

    Hi there Bob,

    To answer your questions:

    1. I'm using MS SQL Server V8.0
    2. Yeah there will be money, date/time data. All the DB will be
    money/datetime.

    Its pretty tricky. Using the code Ray gave me and a bot of my own I
    was able to populate the DB to an extent. The extent being *one*
    insert per loop. So I ended up filling one field in a row, moving to
    the next row and filling another field etc.

    I've written out what I think *may* work in pseudocode but I'm new to
    asp and can't turn it into code proper.

    something like

    <code>

    if readline includes "[" then
    this is a *tablename*
    so until next "["
    all readlines are "fieldame = data"
    -->smlArray = split(readline, =)
    -->store all smlArrays into bigArray
    -->so until loop finds another "["
    -->bigarray should be bigarray(smlarray0, smallarray1,smlarray2, etc)
    then using bigarray construct insert as
    insert into *tablename*(bigarray.smlarray1(0), bigarray.smlarray1(0),
    etc)
    values (bigarray.smlarray1(1), bigarray.smlarray1(1),etc)

    </code>

    Or maybe I'm completely wrong.
    I don't know if said or if i merely implied but there a numerous
    different tables. The file *will* hold data that will reflect the DBs
    structure.

    I'll post some more sample data in a differnt post, as this one seems
    a tad long for usenet!

    I appreciate any help you can give me.

    P





    "Bob Barrows [MVP]" <> wrote in message news:<#>...
    > Ray Godfrey wrote:
    > > Hey Ray,
    > >
    > > Thanks for the few lines of code they work perfectly. My next problem
    > > though is turning the information I'm after taking from the file and
    > > formatting it into an SQL insert table. So....any ideas? Spare Change?
    > >

    > Disregard my previous reply. I see what you're after.
    > 1. What is the type and version of database you are using.
    > 2. Your example data is all character (string) data. Will there be numeric
    > or date/time data provided by these text files? Will the corresponding table
    > fields be the same datatype (numeric or datetime)? Or are all the fields
    > character fields?
    >
    > Bob Barrows
    Ray Godfrey, Apr 21, 2005
    #8
  9. 1. You don't need to remove the brackets: they are perfectly legal in SQL,
    even Transact-SQL.
    2. Your problem is compunded by the differing datatypes. When constructing
    dynamic sql statements, you need to properly delimit the data depending on
    the datatype of the column into which the data is being inserted (see
    http://groups.google.com/groups?hl=...=1&selm=).
    My usual suggestion would be to create saved parameter queries for each of
    the tables involved so you have less problems with datatypes. However, even
    this step may still create problems due to the potential inability of
    vbscript to implicitly convert the data in the text file to the proper
    datatypes. Therefore, my suggestion is to use staging tables: create copies
    of all of the tables in your database, making all the columns varchar. Put
    triggers on these tables that will insert the newly inserted data into the
    true destination tables, using builtin TSQL functions to convert the data to
    the proper datatypes. Now all that's required is getting the data from the
    text files into the staging tables. You can utilize this technique:
    http://groups-beta.google.com/group/microsoft.public.vi.general/msg/0c76ae56f800dd59.
    Use an XML Document or Dictionary object to map the table names to the
    staging table names

    However, let's take a step back. ASP does not seem to be the proper tool for
    you to use for this functionality. If all you are doing is importing data
    from text files into your database, you should be looking into using DTS to
    perform this task. A scheduled job can be created to periodically poll a
    given folder and process any files it finds there, moving them into a
    "completed" folder when complete. The DTS package can contain an ActiveX
    script (vbscript or any other scripting laguage) to parse the data in the
    text file and

    Bob Barrows
    Ray Godfrey wrote:
    > Hey sorry everyone for reposting this message in its own thred, but
    > usenet went mental last night and would'nt allow me respond to any
    > posts directly. Here's what I've been trying to say
    >
    > Hi there Bob,
    >
    > To answer your questions:
    >
    > 1. I'm using MS SQL Server V8.0
    > 2. Yeah there will be money, date/time data. All the DB will be
    > money/datetime.
    >
    > Its pretty tricky. Using the code Ray gave me and a bot of my own I
    > was able to populate the DB to an extent. The extent being *one*
    > insert per loop. So I ended up filling one field in a row, moving to
    > the next row and filling another field etc.
    >
    > I've written out what I think *may* work in pseudocode but I'm new to
    > asp and can't turn it into code proper.
    >
    > something like
    >
    > <code>
    >
    > if readline includes "[" then
    > this is a *tablename*
    > so until next "["
    > all readlines are "fieldame = data"
    > -->smlArray = split(readline, =)
    > -->store all smlArrays into bigArray
    > -->so until loop finds another "["
    > -->bigarray should be bigarray(smlarray0, smallarray1,smlarray2, etc)
    > then using bigarray construct insert as
    > insert into *tablename*(bigarray.smlarray1(0), bigarray.smlarray1(0),
    > etc)
    > values (bigarray.smlarray1(1), bigarray.smlarray1(1),etc)
    >
    > </code>
    >
    > Or maybe I'm completely wrong.
    > I don't know if said or if i merely implied but there a numerous
    > different tables. The file *will* hold data that will reflect the DBs
    > structure.
    >

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 21, 2005
    #9
  10. Ray Godfrey

    Ray Godfrey Guest

    Hi again bob,

    Yeah I know I don't need to remove my barckets but wouldn't the insert
    statement fail if the table in the DB was called [t_customers], rather
    than just t_customers.

    And I'm pretty tied to ASP I'm afraid. Thanks for the links and the
    few comments. I'd be pretty happy even If I was just successfully
    reading into a test DB table, regardless of dates/money etc. At least
    it would be something I could build upon. Right now I feel like I'm
    building on mud!

    Any comments on the pseudocode even? Oh, your post ended on an
    Intriguing "and"


    "Bob Barrows [MVP]" <> wrote in message news:<>...
    > 1. You don't need to remove the brackets: they are perfectly legal in SQL,
    > even Transact-SQL.
    > 2. Your problem is compunded by the differing datatypes. When constructing
    > dynamic sql statements, you need to properly delimit the data depending on
    > the datatype of the column into which the data is being inserted (see
    > http://groups.google.com/groups?hl=...=1&selm=).
    > My usual suggestion would be to create saved parameter queries for each of
    > the tables involved so you have less problems with datatypes. However, even
    > this step may still create problems due to the potential inability of
    > vbscript to implicitly convert the data in the text file to the proper
    > datatypes. Therefore, my suggestion is to use staging tables: create copies
    > of all of the tables in your database, making all the columns varchar. Put
    > triggers on these tables that will insert the newly inserted data into the
    > true destination tables, using builtin TSQL functions to convert the data to
    > the proper datatypes. Now all that's required is getting the data from the
    > text files into the staging tables. You can utilize this technique:
    > http://groups-beta.google.com/group/microsoft.public.vi.general/msg/0c76ae56f800dd59.
    > Use an XML Document or Dictionary object to map the table names to the
    > staging table names
    >
    > However, let's take a step back. ASP does not seem to be the proper tool for
    > you to use for this functionality. If all you are doing is importing data
    > from text files into your database, you should be looking into using DTS to
    > perform this task. A scheduled job can be created to periodically poll a
    > given folder and process any files it finds there, moving them into a
    > "completed" folder when complete. The DTS package can contain an ActiveX
    > script (vbscript or any other scripting laguage) to parse the data in the
    > text file ***and****
    Ray Godfrey, Apr 22, 2005
    #10
  11. Ray Godfrey wrote:
    > Hi again bob,
    >
    > Yeah I know I don't need to remove my barckets but wouldn't the insert
    > statement fail if the table in the DB was called [t_customers], rather
    > than just t_customers.


    No. It should work fine. Brackets are never part of a table name. They are
    just a signal to the parser that it should accept whatever is inside the
    brackets.

    >
    > And I'm pretty tied to ASP I'm afraid.


    Just so you know that ASP is not really the correct tool for this task. Oh
    well, I tried.

    > Thanks for the links and the
    > few comments. I'd be pretty happy even If I was just successfully
    > reading into a test DB table, regardless of dates/money etc. At least
    > it would be something I could build upon. Right now I feel like I'm
    > building on mud!
    >
    > Any comments on the pseudocode even? Oh, your post ended on an
    > Intriguing "and"


    Oops. Just as well, since you can't go that route for some reason.

    I still think you should use staging tables with all varchar fields to make
    this task easier. I'm going to assume you take this advice. let's look at
    the text file again:

    [t_customers]
    cust_ref=MOL01
    cust_addr_1=XYZ
    Cust_Addr_2=ABC
    Cust_Addr_3=LMN
    Cust_Addr_4=QRS
    Cust_Addr_5=DEF

    Oh! Your database is badly designed! Let us know if you want further
    comments on this (or read this:
    http://databases.about.com/od/specificproducts/a/normalization.htm) Let's go
    with what you got, But I recommend a redesign ...


    Dim aVariant, aData, aCols(), aParms(), s, sSQL, i
    s=yourFileObject.ReadAll
    if instr(s,"[")> 0 then
    aVariant = Split(s,vbCrLf)
    sSQL="Insert Into " & aVariant(0) & " ("
    redim aCols(ubound(aVariant) -1)
    redim aParms(ubound(aVariant) -1)
    for i = 1 to ubound(aVariant)
    aSplit = Split(aVariant(i),"=")
    aCol(i-1) = aSplit(0)
    aParms(i-1) = aSplit(1)
    next
    sSQL = sSQL & Split(aCols,", ") & ") VALUES (?"
    sSQL = sSQL & String(ubound(aParms), ",") & ")"

    '****comment this out when finished debugging
    response.write sSQL
    '****************************************************
    set cn=createobject("adodb.connection")
    cn.open "<your sqloledb connection string>"
    set cmd = createobject("adodb.command")
    cmd.CommandText = sSQL
    set cmd.ActiveConnection = cn
    cmd.Execute ,aParms,129
    else
    'handle the missing-table error
    end if


    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Apr 23, 2005
    #11
  12. Bob Barrows [MVP] wrote:

    > sSQL = sSQL & Split(aCols,", ") & ") VALUES (?"
    > sSQL = sSQL & String(ubound(aParms), ",") & ")"
    >


    Whoa! nobody picked up on this? :)
    This was quite the goof. It should be:

    sSQL = sSQL & Split(aCols,", ") & ") VALUES (?"
    for i = 1 to ubound(aCols)
    sSQL = sSQL & ", ?"
    Next
    sSQL = sSQL & ")"

    The goal is to get a sql string that looks like this:
    insert into table (col1, col2,col3)
    values (?,?,?)

    I also neglected to finish what I was going to say about the staging tables.

    My suggestion is that you create duplicates of all your tables, making all
    the columns varchar. use a suffix such as "_stg" to identify them. This
    means the code I provided will also need to be modified like this:

    From
    sSQL="Insert Into " & aVariant(0) & " ("

    To
    sSQL="Insert Into " & aVariant(0) & "_stg ("

    So the sql string that get generated looks like:
    insert into table_stg (col1, col2,col3)
    values (?,?,?)

    Are you familiar with triggers? If so, you can put an "on insert" trigger on
    each staging table to
    1) move the newly inserted data into the true destination table, applying
    the appropriate conversion functions
    2) delete the data from the staging table

    Otherwise, you will need to do this task from your asp page. Do you need
    help with this part?


    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 25, 2005
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    4
    Views:
    475
    Guest
    Oct 13, 2004
  2. Pekka Järvinen
    Replies:
    2
    Views:
    663
    Richard Tobin
    Apr 29, 2008
  3. Ray Godfrey

    re String Validation/ or something...

    Ray Godfrey, Apr 20, 2005, in forum: ASP General
    Replies:
    0
    Views:
    126
    Ray Godfrey
    Apr 20, 2005
  4. Replies:
    4
    Views:
    213
    Tad McClellan
    Jun 1, 2007
  5. Replies:
    9
    Views:
    158
Loading...

Share This Page