Home » SQL & PL/SQL » SQL & PL/SQL » How to get the distinct count of records from a csv file? (oracle 9.2.0.3)
How to get the distinct count of records from a csv file? [message #447841] Thu, 18 March 2010 03:53 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id
"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"

I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this?
In the above case distinct transaction id count is 4
Thanks for the help
Re: How to get the distinct count of records from a csv file? [message #447845 is a reply to message #447841] Thu, 18 March 2010 03:59 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create an external table whose source is your CSV file and then count distinct records.
Re: How to get the distinct count of records from a csv file? [message #447853 is a reply to message #447845] Thu, 18 March 2010 04:33 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Is there no way using pl/sql utl_file without using external table?
I even can store the data in a temp table and later get the distinct count.

What I have done here is extracted the transaction id using substr
But finding no idea how to get the total distinct transaction id count
Re: How to get the distinct count of records from a csv file? [message #447855 is a reply to message #447853] Thu, 18 March 2010 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there no way using pl/sql utl_file without using external table?

Why don't you want to use the efficient way?

Quote:
I even can store the data in a temp table and later get the distinct count.

Yes you can.

Regards
Michel
Re: How to get the distinct count of records from a csv file? [message #447861 is a reply to message #447855] Thu, 18 March 2010 04:50 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
The requirement is to get the total no. of distinct recrds and should be returned as out parameter which is going to be used in front end.So , need your help if there is any solution in pl/sql
I have no privilege to go for external or temp table
Re: How to get the distinct count of records from a csv file? [message #447868 is a reply to message #447861] Thu, 18 March 2010 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have no privilege to go for external or temp table

So ask your DBA to create one external for you.
The lack of privilege is not a good reason to make bad job.

Regards
Michel
Re: How to get the distinct count of records from a csv file? [message #447876 is a reply to message #447861] Thu, 18 March 2010 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As far as I know, the only privilege you need to create an external table is READ on the directory object that corresponds to the OS directory with the file in it.
You also need this privilege to use UTL_FILE, so that shouldn't be a problem.

If you really can't bring yourself to use an external table, all the other solutions are variations on the theme of 'Read the table row by ro using utl_file and count things'

You could create a pl/sql associative arrays, and insert/update a row in it for each row read, using the transaction id as the index value.

You could create a Sql table type and insert each row read into that, and then use that as the data source for a query to get the distinct rows.

Re: How to get the distinct count of records from a csv file? [message #447904 is a reply to message #447876] Thu, 18 March 2010 06:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to implement it in oracle forms
Re: How to get the distinct count of records from a csv file? [message #447907 is a reply to message #447904] Thu, 18 March 2010 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, and another option is to make sure that CSV file doesn't contain duplicates (i.e. that it is created in a manner that wouldn't cause headache on your side, but on file creator's side).

Also, you could create a unique index on all those columns so loading would fail due to that constraint violation.


[EDIT: added another option]

[Updated on: Thu, 18 March 2010 07:06]

Report message to a moderator

Re: How to get the distinct count of records from a csv file? [message #447908 is a reply to message #447841] Thu, 18 March 2010 07:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
The alternative, is to admit to your team leader that you are massively underskilled fot the task at hand and see if he / she will pass the job on to someone with the appropriate skill level.
Re: How to get the distinct count of records from a csv file? [message #447909 is a reply to message #447908] Thu, 18 March 2010 07:26 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
pablolee wrote on Thu, 18 March 2010 07:20
The alternative, is to admit to your team leader that you are massively underskilled fot the task at hand and see if he / she will pass the job on to someone with the appropriate skill level.


And... be out of Job. Laughing
Re: How to get the distinct count of records from a csv file? [message #447919 is a reply to message #447909] Thu, 18 March 2010 08:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
rahulvb wrote on Thu, 18 March 2010 07:26
pablolee wrote on Thu, 18 March 2010 07:20
The alternative, is to admit to your team leader that you are massively underskilled fot the task at hand and see if he / she will pass the job on to someone with the appropriate skill level.


And... be out of Job. Laughing


Well, in the companies I have worked so far it was always better in the long run to say "I can't do it" early on, instead of trying for ages and then only admitting it one day before the deadline. Very Happy
Re: How to get the distinct count of records from a csv file? [message #447928 is a reply to message #447909] Thu, 18 March 2010 09:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rahulvb wrote on Thu, 18 March 2010 07:26
And... be out of Job. Laughing

Not if you have not been trained to the appropriate level. If you have been trained to the appropriate level (or if you have taken the job on, claiming to be at the appropriate level) and you are still unable to do the job, then you should be looking for another job anyway, one that better suits your level of ability.

[Updated on: Thu, 18 March 2010 09:03]

Report message to a moderator

Re: How to get the distinct count of records from a csv file? [message #447982 is a reply to message #447919] Thu, 18 March 2010 21:39 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ThomasG wrote on Thu, 18 March 2010 08:03

Well, in the companies I have worked so far it was always better in the long run to say "I can't do it" early on, instead of trying for ages and then only admitting it one day before the deadline. Very Happy


Thomas Probably other people are not that lucky as YOU are, People work in Service based IT company do not enjoy the privileged to say "I can't do it" because if they do it then indirectly they make it sure the their Manager looks idiot in front of their Customer ( indeed he is though Very Happy ).. and loss of revenue and eventually pink Slip

Smile of course I don't want you steal you luck though.

All the Best Anywasy Cool

[edit: Typo]

[Updated on: Thu, 18 March 2010 21:41]

Report message to a moderator

Re: How to get the distinct count of records from a csv file? [message #448009 is a reply to message #447982] Fri, 19 March 2010 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If I were a customer and you were working on-site for me, and I would find out you took on a task that was way over your head without telling me
- I would slap myself for not noticing during the interview
- I would tell you and probably your mananager.
However, as pablolee and ThomasG said, if you would tell me you did not know how to do this, but were prepared to do some investigation
- I would encourage it and keep track of your progress. Help you out when you were stuck
- compliment your manager on the fact that you are honest and keen to learn.

(of course, under the conditions mentioned by pablolee)

But that might be a cultural difference
Re: How to get the distinct count of records from a csv file? [message #448200 is a reply to message #447841] Sat, 20 March 2010 09:24 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
back to the original question. Assuming the OP has time to do some reading:

1) if you are doing this via Oracle Forms, then you need to read up on the oracle forms manuals about TEXT_IO. If the file is on the server and you are using web based forms then this will get you what you want. If the file is on the client then you will have to deal with WEBUTIL as well. Read about that.

2) if you are executing your logic from the database, then use UTL_FILE instead of TEXT_IO.

3) once you can read the file, you need to know if the file is in sorted order. If it is then you can do some simple logic by checking to see if the value just read is the same as the previous value. If not then add 1 to your distinct count. If however the data is not coming in sorted order, then you need to save keys and scan your list of saved keys. You would read a record from the file, check a list of saved keys (which starts out empty) to see if the value is in the list. If not then add the key to the list. When you have read and check all rows, take the count of rows in the list.

Good luck, Kevin
Previous Topic: Help in exists / not in
Next Topic: insert into temporary table
Goto Forum:
  


Current Time: Thu Dec 08 03:51:31 CST 2016

Total time taken to generate the page: 0.07893 seconds