?
Solved

sql query using csv data

Posted on 2015-01-09
12
Medium Priority
?
133 Views
Last Modified: 2015-01-09
Hi -

I have a csv file like below

1000001, 20140101
1000002, 20110101
1000003, 20100101
...
...
1000023, 20040101

Now i wrote a sql query like below
select * from emp
where emp_id = '1000001' and jdate = '20110101'

this is for one member but is there a way to write a query for all the values in the csv.
if i use my query then i need to feed empid, jdate to the query and the DB is called lot of times.
but can I avoid it and make one single call for all the values in csv and still get output.

I am using perl script to call the stored procedure where the above query is written

THanks,
0
Comment
Question by:shragi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
12 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40540429
Why not construct the query in perl (from the csv data) and call it directly?

There are a couple of ways you can write a query that may do what you want (either as direct queries or stored procedure).

The simplest is imperfect but might be sufficient (it all depends on your data and how exact the query needs to be):
select * from emp
where emp_id in ('1000001','1000002',...,'1000023') and jdate in ('20140101','20100101',...,'20040101')

Open in new window


The problem with the above is that it will return results where emp_id = 1000001 and jdate is any of 20140101, 20100101, or 20040101.  If that is acceptable then you're done.

If not, then you need to create something like this and may run into limits of your db (either inherent or settings):
select * from emp
where (emp_id = '1000001' and jdate = '20140101')
or (emp_id = '1000002' and jdate = '20110101')
...
or (emp_id = '1000023' and jdate = '20040101')

Open in new window

0
 

Author Comment

by:shragi
ID: 40540437
I knew that the part I don't know how to get data from CSV.
I think I did not ask my question properly
I would like to know how do you read from that csv and run the query.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40540454
How many rows are in the csv file?  If it has a small number of rows, then you could parse it and build up the where clause to look like:
where (emp_id = 1000001 and jdate = '20140101')
   or (emp_id = 1000002 and jdate = '20110101')
   or (emp_id = 1000003 and jdate = '20100101')

But, if it has a large number of rows, then that wouldn't be very practical/efficient so I'd either execute 1 query perl row (using placeholders/bind values) or build the where clause as shown but limit the number of groups to a reasonable number and do the query in batches.
0
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 1800 total points
ID: 40540482
Here's one option for the parsing.
my @where;
while (<DATA>) {
    chomp;
    my ($id, $date) = split /, /;
    push @where, "(emp_id = $id and jdate = '$date')";
}

my $where = join ' or ', @where;
my $sql = "SELECT * FROM emp WHERE $where";

print $sql;

__DATA__
1000001, 20140101
1000002, 20110101
1000003, 20100101

Open in new window

0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40540547
Is the file in Microsoft CSV format or plain vanilla CSV format?  In other words, is it bare "words" that are comma separated or does it include additional formatting options (most commonly quotes around any strings)?

If it is vanilla/plain CSV, FishMonger's code should work.  If it is Microsoft format, then you have to either do more processing or (better) use Text::CSV.
0
 

Author Comment

by:shragi
ID: 40540557
@FishMonger

I got an error using the above perl script
"use of uninitialized value $date in concatenation"
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40540579
That would seem to indicate that there are lines that do not contain jdate (either just emp_id or possibly an empty line).  You can add this line between lines 4 and 5 in FishMonger's script:
next unless (defined $id and defined $date);

Open in new window


That will silently skip those lines.  You could alter it to warn you about them and then skip them.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40540581
I'm not receiving that warning.  What changes did you make to the code I posted?

c:\test>type Perl-1.pl
#!/usr/bin/perl

use strict;
use warnings;

my @where;
while (<DATA>) {
    chomp;
    my ($id, $date) = split /, /;
    push @where, "(emp_id = $id and jdate = '$date')";
}

my $where = join ' or ', @where;
my $sql = "SELECT * FROM emp WHERE $where";

print $sql;

__DATA__
1000001, 20140101
1000002, 20110101
1000003, 20100101

Open in new window


c:\test>Perl-1.pl
SELECT * FROM emp WHERE (emp_id = 1000001 and jdate = '20140101') or (emp_id = 1000002 and jdate = '20110101') or (emp_id = 1000003 and jdate = '20100101')

Open in new window

0
 

Author Comment

by:shragi
ID: 40540600
let me give the complete error message

"use of uninitialized value $date in concatenation
SELECT * FROM emp where (emp_id = '100001,20150101' and jdate = )

so bothe emp_id and jdate went to emp_id
so it is not properly split

my date file is as below:
100001,20150101
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 200 total points
ID: 40540607
Ah, change line 9 to be:
my ($id, $date) = split /\s*,\s*/;

Open in new window

0
 

Author Comment

by:shragi
ID: 40540625
that worked thanks
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40540630
Ah, I should have expected that the sample data was not formatted exactly the same as the actual data.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question