I am very new at this and need help 
Author Message
 I am very new at this and need help

Apologies in advance if I am in the wrong newsgroup but I need help and I am
not sure where to ask for it. I have two SQL databases that hold water
quality data and meteorological data. This data is currently submitted to us
in monthly text files which are in turn appended together to create yearly
txt files.  These yearly text files are then transformed nto a SQL database
for the purpose of online querying and analyzing.  This a very new project
and in the beginning stages of development.  The ultimate goal is to have
these monthly files submitted online and then automatically uploaded into
their corresponding databases.  Is this possible to do and where do I begin?
Please help. Thanks in advance.


Sun, 19 Sep 2004 01:10:29 GMT
 I am very new at this and need help

From the first half of your post, it appears that you are already importing
these files into SQL Server.  Is this correct?  If so, you can simply import
the data as needed.

If you are wondering HOW to import the data, you could use BCP or DTS

--
Keith, SQL Server MVP


Quote:
> Apologies in advance if I am in the wrong newsgroup but I need help and I
am
> not sure where to ask for it. I have two SQL databases that hold water
> quality data and meteorological data. This data is currently submitted to
us
> in monthly text files which are in turn appended together to create yearly
> txt files.  These yearly text files are then transformed nto a SQL
database
> for the purpose of online querying and analyzing.  This a very new project
> and in the beginning stages of development.  The ultimate goal is to have
> these monthly files submitted online and then automatically uploaded into
> their corresponding databases.  Is this possible to do and where do I
begin?
> Please help. Thanks in advance.



Sun, 19 Sep 2004 01:35:39 GMT
 I am very new at this and need help

I am currently using DTS to import the yearly files one at a time after
they are FTP to me.  What I want to know is there a way that the clients
can upload the files that will then be automatically inserted to the
appropriate database.  These files are being FTPd from clients all over
the country every motnh and the process of appending all of the monthly
files into one yearly file and then individually into a database I am
afraid is going to work out to be very painstaking.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 19 Sep 2004 01:47:07 GMT
 I am very new at this and need help
I assume that the "assembled" files are in the same structure as the monthly
updates, and each table that you are importing into has a different data
file [name/format] associated with it.

With that said, you just need to determine
(1) when to call an import
(2) what import to call (assuming that you have one import per table/data
file)
(3) did the import work?

You should be able to use the extended stored procedure xp_fileexist to
determine if a file has been uploaded and exists in a specific directory.
You will be able to read more about this extended stored procedure within
Google's archives of these newsgroups:
http://groups.google.com/groups?hl=en&q=xp_fileexist&meta=group%3Dmic....
public.sqlserver.*

Knowing what import (DTS routine) to call should be fairly easy....I imagine
that the files are named differently

Did it work -- you should be able to check this within the DTS package....or
within T-SQL after the import is finished.

You should be able to create a scheduled job that will run as often as you
need it to run in order to import the data.

--
Posted on Microsoft news server at msnews.microsoft.com
If you are reading this from any other location, skip the middleman and come
to the source of the information!

Keith, SQL Server MVP


Quote:

> I am currently using DTS to import the yearly files one at a time after
> they are FTP to me.  What I want to know is there a way that the clients
> can upload the files that will then be automatically inserted to the
> appropriate database.  These files are being FTPd from clients all over
> the country every motnh and the process of appending all of the monthly
> files into one yearly file and then individually into a database I am
> afraid is going to work out to be very painstaking.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sun, 19 Sep 2004 03:34:02 GMT
 I am very new at this and need help
You know files and text are pretty broad terms.  I assume you are taking a
set of standardized field measurements and converting them into records of
some sort, perhaps with a format of: location, date and time, temperature,
wind direction, barometer, humidity, etc.  As you can see I watch the
weather guy pretty closely.  Now you have people sending in this sort of
data to one 'clearing house', which is your SQL database.

The job is first to standardize the data 'fields' according to datatypes
most appropriate for the information they contain, ie, for a date and time
value you can use the datetime datatype as it would record both date and
time in one field.  For temperature you would use a decimal field of fairly
low precision due to the small number of significant digits in most
metereological measurements.  The location name, because its text, would be
a varchar with a max/length = the longest location name.  You make decisions
like this for each 'chunk' of your data that is being submitted and then
provide your 'station masters' with a list of the field names and data types
you are using for the fields.  Then they can use whatever database recording
system (including spreadsheets) and then provide you with a standardized
datafile, (tab delimited would probably be best, with the first row of data
being the field names, just to be sure.

As an importing method I'd suggest you use Visual Basic or even Access2000
with a simple SQL2000/SQL7/MSDE statement to take the tab delimited file
you've imported into Access/VB6 as a temporary filename (and then verified
as having the correct format, field names, datatypes, etc, otherwise garbase
in >> garbage out) and simply INSERT the data from the imported Tab
Delimited file over to your SQL file.  If the imported file has the correct
field names & datatypes that align with ur SQL files, you can re-use the
same INSERT over and over

If you do this with an Acess project file you have the added benefit of
being able to simply copy the xxxxxx.adp 'front end' to any computer on your
MS Network and they'll be able to also link up automatically to the SQL
server (of whatever flavor) and read the same SQL server files as you, as
well as being able to import Tab delimited files and then perform the simple
INSERT SQL Stored Procedure to copy the data to your SQL master files.

If that seems complex, it isn't really and I can't think any simpler way to
do what your talking about.

As for serving all that SQL data on the web, you'll need a copy of Visual
Studio.NET and WinXP Pro or Win2000 Server plus a fairly fast Web connection
and a very understanding ISP unless you rent or co-locate your server and
run the whole thing remotely.  But thats another story.

Chas Coleman
Pt.9 Software


Quote:
> Apologies in advance if I am in the wrong newsgroup but I need help and I
am
> not sure where to ask for it. I have two SQL databases that hold water
> quality data and meteorological data. This data is currently submitted to
us
> in monthly text files which are in turn appended together to create yearly
> txt files.  These yearly text files are then transformed nto a SQL
database
> for the purpose of online querying and analyzing.  This a very new project
> and in the beginning stages of development.  The ultimate goal is to have
> these monthly files submitted online and then automatically uploaded into
> their corresponding databases.  Is this possible to do and where do I
begin?
> Please help. Thanks in advance.



Sun, 19 Sep 2004 10:08:03 GMT
 I am very new at this and need help
Thank you.  I think that I understand.  I will look into it.


Sun, 19 Sep 2004 21:51:39 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. I am New to Databases: Need suggestions for reading

2. Please help, I am new to Jet Engine and SQL

3. I am New.Can you help me?

4. HELP!!!, I am so new that I do

5. HELP ME - I AM NEW !!!!

6. Help. I am new to database administration

7. Help I am new to SQL server

8. Please help, I am new here

9. HELP!!!, I AM SO NEW THAT

10. New line break - ( I am new to the world of sql server)

11. I am in dire need of help!


 
Powered by phpBB® Forum Software