string validation/ or something...

[Ray Godfrey]

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]]

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

 

[Agoston Bejo]

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.

 

[Ray Godfrey]

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

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

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]

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]

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]]

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&[email protected]).
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

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.

 

[Ray Godfrey]

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]]

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

 

[Bob Barrows [MVP]]

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