Any sql and excel gurus here in OT?

[Arkitech]

I've been recently assigned a task of checking to see if certain files have loaded into the SQL database (we have a SQL db on a unix box) for my group. Normally this would'nt be a big deal as I check files against our DB every other day. The problem I'm having is that I'm dealing with hundreds of files at a time and since I don't have a background in SQL (I just know very basic commands) this is driving me up the wall. I was wondering if someone in OT can give me a little advice on the best way to handle this kind of task.

So here's my dilemma. As of today I'm going to receive emails with hundreds of files that look like this:

SATTRTLRAMT_FUSAGE_ID5973_T20071031013029.USAGE_AMT.dat
SATTRTLRAMT_FUSAGE_ID5973_T20071031013029.duc_USAGE_AMT.dat
SATTRTLRAMT_FUSAGE_ID5973_T20071031013029.ugd_AMT.dat
SATTRTLRAMT_FUSAGE_ID5974_T20071031013036.USAGE_AMT.dat

As I mentioned before with just one or two files I can just copy the filename from the email and run a query in SQL. An example of that kind of query would look like this:

select extrct_hist_idnt,extrct_strt_dt_tme,prc_file_idnt from dw240 where prc_file_idnt like 'SATTRTLRAMT_FUSAGE_ID5973_T20071031013029.USAGE_AMT.dat';

Since I'm dealing with multiple files I realize that I need to create a script and I'm thinking the best way to write out the script is to copy the files into Excel and then add my query statements around the file name. So for example in excel I would attempt to do the following:

Column A: select extrct_hist_idnt,extrct_strt_dt_tme,prc_file_idnt from dw240 where prc_file_idnt like '

Column B: file names

Column C: ';

The next step would be to copy the contents of all 3 columns into a notepad and save the file to be run in SQL. (filesearch.sql). The problem for me is when I copy the data from Excel into notepad the formatting is screwed up. I have gaps and spaces where there should'nt be and I'm not sure why.

If you're still with me at this point my questions are the following:

1. How can I transfer the data I need from Excel into notepad without formatting issues?

2. Is there a way I can write the script so that the results are placed in a text file that I can read through later?


Many thanks to anyone who can help me out with this one.

Moved to Programming
Anandtech Moderator hzl

 

[tfinch2]

Wrong section

 

[gsethi]

I am little rusty now (my last sql class was couple of years ago). Write a PL program that uses either function or procedure. Open a file and read the input from that file into your program and then do an output to another file. Its not that hard, just read a good PL/SQL book.

 

[mugs]

Since you're not using any wildcards, you could change the LIKE to =. Or better yet, change it to IN, and then provide a list of filenames so you get it all in one query.

Copy/paste the list of filenames into a text editor and do a find on \n and replace it with ', '

That'll give you the list to put into your query like so:

select extrct_hist_idnt,extrct_strt_dt_tme,prc_file_idnt from dw240 where prc_file_idnt IN (<list of names>);

The find/replace won't put the quote before the first filename or after the last one, so you'll have to do that manually.

 

[Fritzo]

heheheheheheh......he said "query".

 

[Ktulu]

Wrong section.

Why not use the Concatenate function for Column C like this:

= Concatenate(Column A,"'",Column B,"';")

Next highlight all of Column C and dump it into a a new text file and save it as a .sql file.

Always works for me.