473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Running script before import

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.

Nov 13 '05 #1
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.

Nov 13 '05 #2
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.

Nov 13 '05 #3
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)
Nov 13 '05 #4
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

Nov 13 '05 #5
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

Nov 13 '05 #6
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.

Nov 13 '05 #7
Thanks.

Nov 13 '05 #8
Thanks.

Nov 13 '05 #9
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

Nov 13 '05 #10
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

Nov 13 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
5
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...
8
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...
8
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...
4
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.
13
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. ...
2
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....
24
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...
2
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
1
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)...
1
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....
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.