473,703 Members | 3,394 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Running script before import

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.

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

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

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.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

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

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

Similar topics

1
3756
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 python-interpreter and as EXE-prog goes well. running the service-module "FBservice.py" with python-interpreter also works fine but after comiling with py2exe (with no warnings or errors!) i get on every host the same error message
5
5316
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 these files, I wish to do the same using another python code instead of running each of these files one by one, which would be cumbersome giving the argv of each file every single time. This can be easily done using a shell script but I just...
8
2052
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 get errors. Instead of telling them, 'Upgrade you Python Install, I'd like to use sys.version or some other way of checking before running. Whatever I do, I need it to work on Linux, Mac and Windows. I thought of sys.version... but getting...
8
3034
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 what I really should do. I've had a look through Programming Python and the Python Cookbook, which have given me ideas, but nothing has gelled yet, so I thought I'd put the question to the community. But first, let me be a little more detailed...
4
1831
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
5998
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. The script is set to print a progress report to stdout every 3 seconds but I never see any output until the child process is killed. Here's what I have in my python script:
2
2847
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. But running the script as a child process only sets values for that process, which are lost after execution. So I thought I could simply tack on an 'env' command line to the script input lines as shown below. However, using subprocess.Popen gives...
24
2834
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 a linux/unix command line guy quite experienced in shell scripts etc. I have a heap of command line utility scripts which I run directly. What is the best way to create python command line scripts but exploit the (loadonly) speed-up benefit of...
2
1439
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 run the module from the Edit window (F5 - Run Module). The script that does the importing does not see the changes to the imported class, even if I delete the compiled files. For it to see the changes I must close all the edit windows and re-open...
0
8750
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9246
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9112
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9005
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3115
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2440
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2058
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.