[AccessD] Attention Gurus in Text file reading/writing - Paid job if you want it

Some air code here....

Dim f as Long
Dim strTemp as String
Dim strArray() as String
F=freefile
Open "C:\SomePath\Textfile.txt" for binary access read as f
Strtemp=space(lof(f))
Get f,,strtemp
Close f
Strarray=Split(strTemp,"GO" & vbcrlf)


That gives you an array of the CreateTable groups.  It's just a matter of
looping through the array, and manipulating each string then.

Drew

-----Original Message-----
From: Darren DICK [mailto:darrend at nimble.com.au] 
Sent: Sunday, July 16, 2006 10:51 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Attention Gurus in Text file reading/writing - Paid job
if you want it

Howdy all
 
I have a big project to do for my work
 
Am willing to pay if someone wants to take on providing a solution or if you
want to provide partial or whole solutions to the list - Feel free :-))
 
I have very large SQL files that create hundreds of SQL tables - 2 table
example
below.
and large text files that populate those tables - 2 line example below the
table
example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE [dbo].[AccountHostingOptions] (
 [HostingOptionID] [int] IDENTITY (1, 1) NOT NULL ,
 [HostingID] [int] NULL ,
 [HostingOptionCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [OptionQuantity] [int] NULL ,
 [PerOptionFee] [numeric](9, 2) NULL ,
 [RecurrenceCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[AccountInternetOptions] (
 [AccountInternetOptionID] [int] IDENTITY (1, 1) NOT NULL ,
 [AccountNo] [int] NOT NULL ,
 [OptionCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [StartDate] [smalldatetime] NULL ,
 [EndDate] [smalldatetime] NULL ,
 [MonthlyFee] [numeric](9, 2) NULL ,
 [ConnectionFee] [numeric](9, 2) NULL ,
 [MailBoxQty] [int] NULL ,
 [Username] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [OptionItemValue] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
 [CreatedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DateCreated] [smalldatetime] NULL 
) ON [PRIMARY]
GO
 
Another file may be an insert monster containing zillions of insert
statements
 
INSERT INTO [luDistanceMapping]
([luDistanceMappingID],[Description],[StartDistance],[EndDistance])VALUES(1,
'Up
to 50km',0,50)
INSERT INTO [luDistanceMapping]
([luDistanceMappingID],[Description],[StartDistance],[EndDistance])VALUES(2,
'50
- 85km',51,85)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
What I need to do is to be able to point to the text file - in the example
above
it would be: \\Some folder\Create AccountTables.sql
 
I then need the code to loop through and find each occurrence of (in this
case)
"CREATE TABLE" and find all the text between "CREATE TABLE" and "GO"
then output the text between "CREATE TABLE" and "GO" into a new text file
made
up of..."tables_AccountHostingOptions.sql" and
"tables_AccountInternetOptions.sql"
whilst pulling the details out of the BIG text file as in the example above
-
but of course there are hundreds of CREATE TABLE entries in the file - I am
only
showing 2 Create Table scripts here for demo
 
So what I need is...
Ability to point to a file (I can do this bit just leave me a hook in the
following code to reference a form Control)
Ability to tell where to start (Inside the text file) the export from EG
"CREATE
TABLE" or "INSERT INTO" etc and ability to determine the end text = EG 'GO'
etc
Ability to get the text between those 2 points
Ability to append write the text between the "CREATE TABLE" to a text file 
Ability to provide an output folder (I can do this bit - just leave me a
hook )
Ability to name the text file with a prefix - EG 'tables_' or 'config_' etc
(I
can do this bit - just leave me a hook in the code)
 
 
AirCode - anything in capitals is a variable set from the form
Point to a file
read the contents of that file
find every occurrence of INSERT INTO or CREATE TABLE in that file
Get all the text between INSERT INTO or CREATE TABLE and GO
for each occurrence of INSERT INTO or CREATE TABLE output the text between
INSERT INTO or CREATE TABLE and GO to a text file in a folder I have already
determined
name the resulting text file TABLE_ or CONFIG_ and the Name of the Table to
be
created or the name of the Table to insert into - as wall as dot sql on the
end
of the file name
and loop until there are no more INSERT INTO's or CREATE TABLE's
 
Phew - Hope it all makes sense
 
The trigger for a starting a read for the CREATE TABLE would be the
occurrence
of the word CREATE TABLE and the occurrence of the word GO
The trigger for a starting a read for the INSERT INTO bits would be the
occurrence of the word INSERT INTO and a table name differing from
say...tmpstrTableName
But I'll let you guys work on the machinations of it all :-))
 
Contact me off list (at  <mailto:darrend at nimble dot com dot au> darrend at
nimble dot com dot au) if you want to tackle this one as a paid jobbie - we
need
to negotiate fees me thinks - or feel free to reply on list if you want to
'provide' partial or whole solutions 
 
Either way many thanks in advance
 
Have a great day
 
Darren
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com