I don't need a detailed description of a solution(although 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. 10 2528
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.
I've figured out the select query that gives me the items I want to
delete:
SELECT dbo.nedpitfields.*
FROM dbo.nedpitfields LEFT OUTER JOIN
dbo.testpitfields ON dbo.nedpitfields.Field3 =
dbo.testpitfields.Field3 AND dbo.nedpitfields.Field10 =
dbo.testpitfields.Field10
WHERE (dbo.testpitfields.Field10 IS NULL)
But I am now trying to make this into a delete query. But can't figure
out how. sh******@cs.fsu.edu wrote: I've figured out the select query that gives me the items I want to delete:
SELECT dbo.nedpitfields.* FROM dbo.nedpitfields LEFT OUTER JOIN dbo.testpitfields ON dbo.nedpitfields.Field3 = dbo.testpitfields.Field3 AND dbo.nedpitfields.Field10 = dbo.testpitfields.Field10 WHERE (dbo.testpitfields.Field10 IS NULL)
But I am now trying to make this into a delete query. But can't figure out how.
Replace "SELECT dbo.nedpitfields.* FROM" with "DELETE FROM"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA) sh******@cs.fsu.edu wrote: I don't need a detailed description of a solution(although 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
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.nedpitfields.* FROM dbo.nedpitfields LEFT OUTER JOIN dbo.testpitfields ON dbo.nedpitfields.Field3
= dbo.testpitfields.Field3 AND dbo.nedpitfields.Field10 = dbo.testpitfields.Field10 WHERE (dbo.testpitfields.Field10 IS NULL)
But I am now trying to make this into a delete query. But can't
figure out how.
Replace "SELECT dbo.nedpitfields.* FROM" with "DELETE FROM"
-- MGFoster:::mgf00 <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
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.
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
Thanks for the information. I have never used the "multiphase data
pump" so I was unaware of its perl capability. I found that awk is
more my speed than perl since perl, like Access, does just about
everything. I still use awk sometimes for data transformation. Since
you found DTS useful I'll have to get more information about it from
Microsoft's web site to see if it can be useful for me as well.
James A. Fortune This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Peter Teniz |
last post by:
hi, i'm trying to generate a service for win XP / win 2k with
python2.3.3 + win32all-163 + py2exe0.5.0
(also tried with pywin32-200.win32-py2.3)
running the console-script "FBxmlserv.py" with...
|
by: Shalen chhabra |
last post by:
Hey,
Can anyone give me a snippet for running a python program over all the files
in the directory.
For ex: I have ten files in a directory and I want to run a python program
against all of...
|
by: rbt |
last post by:
Is there a recommended or 'Best Practices' way of checking the version
of python before running scripts? I have scripts that use the os.walk()
feature (introduced in 2.3) and users running 2.2 who...
|
by: Paul Cochrane |
last post by:
Hi all,
I've got an application that I'm writing that autogenerates python code
which I then execute with exec(). I know that this is not the best way to
run things, and I'm not 100% sure as to...
|
by: Michael Malinowski |
last post by:
Is there a way to read the directory that the currently running python file
is located in?
Cheers
Mike.
|
by: wattersmt |
last post by:
Hello,
I am trying to write a python cgi that calls a script over ssh, the
problem is the script takes a very long time to execute so Apache
makes the CGI time out and I never see any output. ...
|
by: Gerard Flanagan |
last post by:
Hello,
I have a third party shell script which updates multiple environment
values, and I want to investigate (and ultimately capture to python)
the environment state after the script has run....
|
by: Mark |
last post by:
Hi, I'm new to python and looking for a better idiom to use for the
manner I have been organising my python scripts. I've googled all over
the place about this but found absolutely nothing.
I'm...
|
by: gtb |
last post by:
I am testing a simple script by running it in the Tk shell. It imports
a class from another module. I edit and save the file from which I
import. When I want to re-run I delete the Tk window and...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |