This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1A488.837ED25E
Content-Type: text/plain;
	charset="iso-8859-1"

I would not recommend this but that comes down to person preference.

You have a 2 step problem.
1. Format the file into a usable format.
2. Import the data into the database.

Rick's Access solution rolls the two steps into one. I prefer to keep them
distinct. The reason I prefer to keep them distinct is that while the first
problem needs a procedural language the second is more set based. In step 1
you are formatting the code into a format that is useful for DTS. In step 2
you import the data. I prefer to use SQL Server for whatever is set based.
SQL server performs better on sets than VB/VBA/VBS. From the code on the
link posted I am guessing that Rick's solution involved little data, where
yours involves or could involve a lot of data. You might well find a speed
gain if you split it into 2 steps.

As I said this comes down to personal preference. I would split the
processes apart for reasons of modularity, even if using DTS was a little
slower. Don't get me wrong, Rick's solution would work and work well. It
might be less in development time, but personally I prefer to split things
into tasks more.

Mind you I would stop and think about taking the advice of an unknown
programmer on a mailing list (me) against the advice of an author who has
published 3 books related to the topic.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: Rick Dobson [mailto:rddcab@i...]
Sent: Thursday, 24 January 2002 12:51 PM
To: sql language
Subject: [sql_language] RE: sql_language digest: January 23, 2002


Hi,

I have worked this problem several times in the past.  You can code your
solution with VB/VBA.  Basically, open two recordsets: 0ne pointing at your
text file and the other pointing at your table.  Read successive rows from
the recordset pointing at the text file until you get to the end of a row
for your table recordset (for example, when you encounter the <end> tag).
Perform any cleaning on the row that is necessary.  Then, add the row values
to the second recordset to append them to the table.  Repeat this process
until you run out of rows in the text file.  Then, you are done.

I have a code sample that illustrates this approach at my site.  The URL for
the code sample is

http://www.programmingmsaccess.com/Samples/VBAProcs/Related%20to%20Web%20Dev
elopment/VBAProcsToParseFrontPageGBForAccess.htm.

Since the URL will likely spread over two lines, remember to copy both lines
to your browser before trying to navigate to the sample.  If you would like
more context to understand the sample, my most recent book, Programming
Microsoft Access Version 2002, includes commentary and screen shots to help
you understand the process.  BTW, the sample is for an Access table, instead
of a SQL Server table.  However, the same approach works with either type of
database.  You can learn more about my books at
www.programmingmsaccess.com/thebook.

I hope that you find this reply helpful.

Rick Dobson
www.programmingmsaccess.com
www.cabinc.net
rickd@c... <mailto:rickd@c...>



-----Original Message-----
From: Dave Parkins [mailto:dparkins@m...]
Sent: Thursday, 24 January 2002 5:49 AM
To: sql language
Subject: [sql_language] RE: Adding the data from a text file to an exi
sting table


Denis, maybe you can help me out with this one then. I have a text file that
eventually needs to get put into a SQL database. Example of text file:
<type>I
<lname>Acme Co.
<ladd 1>250 East St.
<lcity>Anytown
<lst>NY
<amt#1>50000
<amt#2>3400
<end>
<type>I
<lname>Warner
<ladd 1>Water St.
<lcity>Thistown
<lst>NY
<amt#1>9987
<end>

As you can see, the text file has labels with greater/less than symbols
around them. <end>represents the end of each record and <type> the beginning
of each record. And as you can see, the number of fields in each record is
not the same(amt#1 and amt#2 in record 1 but only amt#1 in record 2). I
would like the table to look like this:

type   lname     ladd 1       lcity    lst  amt#1  amt#2
I      Acme Co.  250 East St. Anytown   NY   50000  3400
I      Warner    Water St.    Thistown  NY   9987

The DTS wizard in SQL2000 does not bring the file over correctly and I've
been unsuccessfull with FSO.

Much appreciated,
Dave


$subst('Email.Unsub').