Running script before import

[shumaker]

I don't need a detailed description of a solution(althou gh I wouldn't
mind), but I am hoping someone could tell me in general the best path
to go about accomplishing a task, since I don't know all the
capabilities of what I have available. I can learn the details myself
I think. I am trying to set this up to be as simple to use as possible
since others will be importing data on a weekly or daily basis.

I need to import some text files, but have a perl script that must be
run on them prior to importing, and some logic must be used in the
importing process also.

I am using an "Access Project" as the front end to an SQL server
database.

After running the perl script:
When the rows in the text file are imported, then I need to first
examine the table to determine if any primary keys in my current table
are not in the text file being imported, and if they are not, then I
need to update the value of a certain column in the record that is in
my table but not in the imported data. I was either thinking having a
"IsInImport " column that acts as a flag, and prior to import, setting
this to false for all records, and as I import data, I would set this
flag to true when a record in the import data writes to a record in my
table based on a primary key match. This would mean at the end of
import all records that were in my table, but not in the import would
still have a value of false, and I could run my logic for them that
does the needed updates.

Or perhaps I should import the data into a temporary table, and use an
update query to do the above logic by comparing my current table and
the import table?

I speculate that I will probably need to write this in VB using the
command for running an external app and passing command line parameters
to my perl script.

My other speculation is using a dts package. I am thinking it would be
easier to setup the above updating logic than doing this in vb.
Perhaps in vb from access I can run my external perl script, then call
the DTS package?

Thanks in advance for any help at all. It should be obvious that I
have little experience in anything mentioned above, but I learn quickly
and just don't won't to waste time learning something to find that it
can't do what I want it to, or obliviously doing it the hard way.

[shumaker]

I've worked on this some. I am trying to use the method of creating a
temporary database with imported data, then use a nested NOT IN query
to delete items in the original table that are not in the imported
data.

The thing is they are related by a compound primary key.

I'm a little confused on how to write the query.

[shumaker]

I've figured out the select query that gives me the items I want to
delete:

SELECT dbo.nedpitfield s.*
FROM dbo.nedpitfield s LEFT OUTER JOIN
dbo.testpitfiel ds ON dbo.nedpitfield s.Field3 =
dbo.testpitfiel ds.Field3 AND dbo.nedpitfield s.Field10 =
dbo.testpitfiel ds.Field10
WHERE (dbo.testpitfie lds.Field10 IS NULL)

But I am now trying to make this into a delete query. But can't figure
out how.

[MGFoster]

sh******@cs.fsu .edu wrote:

I've figured out the select query that gives me the items I want to
delete:

SELECT dbo.nedpitfield s.*
FROM dbo.nedpitfield s LEFT OUTER JOIN
dbo.testpitfiel ds ON dbo.nedpitfield s.Field3 =
dbo.testpitfiel ds.Field3 AND dbo.nedpitfield s.Field10 =
dbo.testpitfiel ds.Field10
WHERE (dbo.testpitfie lds.Field10 IS NULL)

But I am now trying to make this into a delete query. But can't figure
out how.

Replace "SELECT dbo.nedpitfield s.* FROM" with "DELETE FROM"

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[jimfortune]

sh******@cs.fsu .edu wrote:

I don't need a detailed description of a solution(althou gh I wouldn't
mind), but I am hoping someone could tell me in general the best path
to go about accomplishing a task, since I don't know all the
capabilities of what I have available. I can learn the details myself I think. I am trying to set this up to be as simple to use as possible since others will be importing data on a weekly or daily basis.

I need to import some text files, but have a perl script that must be
run on them prior to importing, and some logic must be used in the
importing process also.

I am using an "Access Project" as the front end to an SQL server
database.

After running the perl script:
When the rows in the text file are imported, then I need to first
examine the table to determine if any primary keys in my current table are not in the text file being imported, and if they are not, then I
need to update the value of a certain column in the record that is in
my table but not in the imported data. I was either thinking having a "IsInImport " column that acts as a flag, and prior to import, setting
this to false for all records, and as I import data, I would set this
flag to true when a record in the import data writes to a record in my table based on a primary key match. This would mean at the end of
import all records that were in my table, but not in the import would
still have a value of false, and I could run my logic for them that
does the needed updates.

Or perhaps I should import the data into a temporary table, and use an update query to do the above logic by comparing my current table and
the import table?

I speculate that I will probably need to write this in VB using the
command for running an external app and passing command line parameters to my perl script.

My other speculation is using a dts package. I am thinking it would be easier to setup the above updating logic than doing this in vb.
Perhaps in vb from access I can run my external perl script, then call the DTS package?

Thanks in advance for any help at all. It should be obvious that I
have little experience in anything mentioned above, but I learn quickly and just don't won't to waste time learning something to find that it
can't do what I want it to, or obliviously doing it the hard way.

Since perl is so much better than VBA at text manipulation I marginally
recommend that you call a perl script from Access that gets the data
ready. It's not worth learning perl to do this but since you already
know it you might as well use the better tool. You could run an Update
Query and an Append Query based on the results of matched and unmatched
queries on the primary key afterwards (soon after the code to shell to
the perl script). I think that would eliminate the need for an
IsInImport field. I'd check that the shells are finished before
running the queries. Perhaps others have better ideas.

James A. Fortune

[jimfortune]

MGFoster wrote:

sh******@cs.fsu .edu wrote:

I've figured out the select query that gives me the items I want to
delete:

SELECT dbo.nedpitfield s.*
FROM dbo.nedpitfield s LEFT OUTER JOIN
dbo.testpitfiel ds ON dbo.nedpitfield s.Field3 = dbo.testpitfiel ds.Field3 AND dbo.nedpitfield s.Field10 =
dbo.testpitfiel ds.Field10
WHERE (dbo.testpitfie lds.Field10 IS NULL)

But I am now trying to make this into a delete query. But can't figure out how.

Replace "SELECT dbo.nedpitfield s.* FROM" with "DELETE FROM"

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Where did that quote come from? It wasn't from this thread was it?

James A. Fortune

Back at Worcester Polytech Institude (Whoopi) when someone said
something that seemed to come out of nowhere we'd say something like
"What wall?", "Wall?" or "Wall. What wall?" -- Wayne Noss

[shumaker]

I removed that post from this thread after I posted it because it
wasn't really relevant to my original question about capabilities.

I guess he was already in the process of replying when I had removed
it.

Sorry.

[shumaker]

Thanks.

[shumaker]

Thanks.

[shumaker]

I wanted to note this for anyone who might look at this post in the
future.

I found the best way for me to do this was with a DTS package. You can
actually write a DTS package task in Perl Script.

Create a DTS package, and in Design View add an ActiveX Script Task.
In the ActiveX Script Properties you can change the language to Perl
Script, then click Auto Gen to get a starting skeleton for your script.

You can drag any global vars from the browser on the left into the code
and it will generate perl script statement to access the value of the
variable.

I actually pasted the code from my perl script into the body of the
Main function and it almost worked right off, except I was used to
running my script straight from the directory and have it operate on
files in the directory. I had to update it to use fully qualified
network directory names(which I actually am specifying in global
variables, not hard coding). I'm not sure where the default directory
is at, but somewhere on our SQL server there's a text file now that was
created by my script. LOL