• Post Reply Bookmark Topic Watch Topic
  • New Topic

Access each cell value in a csv file

 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a assignment in which i have to get path of a csv file from the user and then perform various operations on all the data items in it. Size of the data in a file is not fixed.

The data in the image is of a csv file.
I don't want to read the data of a csv file line by line and simply store lines in an array or a list. Instead i want to have access to each cell value of the file so that i can perform operations on the data. For example, calculating the average of the all the salaries, comparing each cell value with the other.
I feel something like storing the data in a 2d array, then access it as array[0][0], array[1][1] etc. I looked into "line.split" but it does not allow the access to each value.


I am a newbie.
Kindly help...
TestData.png
[Thumbnail for TestData.png]
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch

Please show us how much you have achieved already. Are you not allowed to use one of the CSV reading utilities? If not, what are you using as separator and are you sure that none of the entries will contain that separator (maybe comma)?
There is no such thing as a 2D array, only array of arrays, but anyway using such arrays would be a very bad solution. You should create a class whose fields can be initialised from a line in the filee.)

I am surprised that you cannot get line.split(",") to work. The comma is not (I believe) a metacharacter, but you can confirm that here.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Note : I have attached the actual test file for input and the image of the output. I am just able to store the data in a list and then display it. But i have to perform operations like comparing other values and then substituting the empty ones, finding the mean of the numeric values etc.

import java.io.*;
import java.util.*;
import javax.swing.*;
public class class1 {
public void file()
{
try
{
FileReader fr = null;
BufferedReader br = null;
String inputPath = JOptionPane.showInputDialog("File Path");
fr = new FileReader(inputPath);
br = new BufferedReader(fr);
String line = null;
ArrayList<Object> myList = new ArrayList<Object>();
line = br.readLine();
while(line!=null)
{
myList.add(line);
line = br.readLine();
}
for(int i=0; i<myList.size(); i++)
{
System.out.println(myList.get(i));
}
} //try end
catch(IOException ioex)
{
System.out.print(ioex);
}
} // file() method ending

public static void main(String args[])
{
class1 obj = new class1();
obj.file();
}
} //class1 ending

 
Knute Snortum
Sheriff
Posts: 3257
84
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A couple of note on your post: First, UseCodeTags (that's a link) to surround your code. I've done it for you this time.

Second, prefer text to posting images of text. If you need the text formatted, surround them with code tags.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Note : I have attached the actual test file for input and the image of the output. I am just able to store the data in a list and then display it. But i have to perform operations like comparing other values and then substituting the empty ones, finding the mean of the numeric values etc.
I am allowed to use the csv reading utilities if applicable (but i dont know what is that).

TestFile.png
[Thumbnail for TestFile.png]
consoleOutput.png
[Thumbnail for consoleOutput.png]
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you using a buffered reader when you can do it more simply with a Scanner? You can use the Scanner to find the different tokens in the file, too.
Why are you using an option pane to find the file? Use a file chooser instead. Somebody wrote on the Swing forum recently saying to use the old equivalent of file chooser.

Please don't post screenshots; they can be very difficult to read.
 
Winston Gutkowski
Bartender
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anam Abdul Razaq wrote:I have a assignment in which i have to get path of a csv file from the user and then perform various operations on all the data items in it. Size of the data in a file is not fixed.

OK, well that sounds like TWO problems to me:
1. Get path of a csv file from the user.
2. Perform various operations on all the data items in it.
And of the two the second sounds MUCH harder than the first, but both will benefit by separating them.

Question: What are these "data items"?

Advice: CSV parsing (unless it's some proprietary style for a class exercise) is NOT simple, but there are tons of 3rd party libraries out there for doing it.

Winston
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually i am successful at getting the file path from the user.
The problem is that i want to have access to each individual value of the file so that i can perform operations like finding the mean of the cholestrol column etc.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If maybe its possible by a 2d array.
For example : array[0][0] will have 1st ID, then array[0][1] can have the first name and so on.
 
Dave Tolls
Ranch Hand
Posts: 2271
23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you avoiding creating a class (or classes) that represents a row in the CSV?
You want to do number crunching on the data, so I would get that data out of the CSV and into a structure I can use to do my calculations.

Indeed, I would consider putting it into a db.
You can then do whatever you want with it quite easily, but failing that, I would definitely go down the class route.

Using arrays is just simply not the right way to go about it, IMO.
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anam Abdul Razaq wrote:If maybe its possible by a 2d array. . . .
Apart from the fact that “2D” arrays are not 2D, the thought of multiple arrays fills me with dread. We have already told you to create a class which encapsulates all the data in the line.

Dave Tolls' opinion is correct.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am very interested to know about how to create a class/classes that would store a row of csv??? I am also allowed a txt file for input.
As i am new to java so i have no idea of using a database with it.
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You do not create a class to hold a row. You create a class which holds name, salary, employer, etc., as in your text file.
 
Dave Tolls
Ranch Hand
Posts: 2271
23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Creating your class for a row is where modelling comes in.

A brief look at that CSV example above seems to show some data similarities. That is, a number of rows seem to apply to the same person. So you will need to decide if you want to model that relationship.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually user is allowed to enter any data. Its not fixed like only data related to an employee or a patient or a student is to be entered. So, the attributes of a class and their data types cannot be specified.
Data is supposed to be "generic" not "specific".
That is why i am facing a difficulty in how to store each row in a class
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Acemaria
I have no idea of what you are talking about?
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually
I have to make a java application which will:

1) Take an input file from the user
2) Perform One R , Imputation and Binning algorithms.
3) Display the result of the Algorithms.

So, i have started the project and the first hurdle is "how to take input from the user".
 
Dave Tolls
Ranch Hand
Posts: 2271
23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, what is the format of the input file?
The two examples you have given are different, so you will need to explain how you determine which columns need to be processed.

How do you know what data to process?

Simply put, you cannot hand a file to some Java code without any idea of the structure and expect to be able to work on it.
You need rules.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) The user is allowed to input a csv or a txt file.
2) The application should handle any data set (big or small)
4) There is going to be only one user of the application.
 
Dave Tolls
Ranch Hand
Posts: 2271
23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the format of this CSV?
You have given two in this thread.
 
Liutauras Vilda
Marshal
Posts: 3173
127
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anam Abdul Razaq wrote:1) The user is allowed to input a csv or a txt file.
2) The application should handle any data set (big or small)
4) There is going to be only one user of the application.
Good start. You should always start by writing down the steps with your requirements. I'd say start from the very first tasks.

Task 1: Allow user to input (select) the file, csv or txt extension only. Campbell Ritchie gave you an advice earlier about using JFileChooser. Read about the file filters.
Addition: It suppose to be a method, which returns... Consider File, or maybe you can think of something else.

When you get first task done - you can think of moving towards the next task.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have achieved to get a file from the user using JFileChooser. This is what i achieved. No errors till now.
Thanks to Campbell Ritchie.


Now step 2 to be accomplished : Extracting data from file in such a way that i can start working on the algorithms.
 
Knute Snortum
Sheriff
Posts: 3257
84
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Reading a text file in Java is a little idiomatic, so here's a shell to work with (requires Java 7 or higher):

 
Les Morgan
Rancher
Posts: 717
17
C++ Java MySQL Database Netbeans IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You will need to read the file line by line, run String split on each line and copy the result into the appropriate row of your 2D array. You will then have access to each cell.

Aside from that you can load a 3rd party Class that will act as an Excel Spreadsheet or use the Java interface to OpenOffice.
 
Liutauras Vilda
Marshal
Posts: 3173
127
BSD
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anam Abdul Razaq wrote:I have achieved to get a file from the user using JFileChooser.
Not fully yet. Well, not in the way you stated in your requirements earlier.

You mentioned that user has to select either txt or csv only. In current case user can select .jpeg or any other file, which is not what you want to let do.
I have mentioned earlier to read about the file filters (<- link), so you can filter the files you want to show in JFileChooser for user to select. In your case would be .txt and .csv only.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for a link to fileFilter, but it is throwing a lot of exceptions.
 
Liutauras Vilda
Marshal
Posts: 3173
127
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anam Abdul Razaq wrote:Thanks for a link to fileFilter, but it is throwing a lot of exceptions.
Please show us relevant part from your most recent code and what kind of exceptions you're getting? (stack trace becomes handful here).
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
java.lang.IllegalArgumentException: Extensions must be non-null and not empty
at javax.swing.filechooser.FileNameExtensionFilter.<init>(FileNameExtensionFilter.java:76)
at class1.actionPerformed(class1.java:30)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6516)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6281)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4872)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4698)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:747)
at java.awt.EventQueue.access$300(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:706)
at java.awt.EventQueue$3.run(EventQueue.java:704)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:720)
at java.awt.EventQueue$4.run(EventQueue.java:718)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:717)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know how to simply read Lines from the file but i don't know how to split and store them in an array.
 
Liutauras Vilda
Marshal
Posts: 3173
127
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check what parameters it accepts and what you supplied.

Anam Abdul Razaq wrote:
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do i have to supply at String description parameter?
 
Liutauras Vilda
Marshal
Posts: 3173
127
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Liutauras Vilda wrote:I have mentioned earlier to read about the file filters (<- link)
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:Reading a text file in Java is a little idiomatic, so here's a shell to work with (requires Java 7 or higher):

Slightly simpler with a Scanner.
With a Scanner you can pull ints, BigDecimals etc directly from a text file. Obviously that entails a particular format for the file; if it varies from that format you will suffer an Exception. Another thing you can do with Scanners is to use two of them:I believe you can use the comma on its own as a delimiter because it is not a metacharacter, but it is worth checking metacharacters here. Yes, you can use a Scanner to scan a single String. You will have to read the Scanner documentation to find out more because I have introduced a spelling error into that code and it won't compile until you find that error.
 
Anam Abdul Razaq
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot everybody.

I fixed my File Filter problem.

Step 2 :
I have used Scanner for reading from the file. But i could only figure out how to read lines.
I am facing a problem on how to split the line and store it in an array.(Actually i don't understand what does a split/useDelimiter do ). I have read about it at a lot of places but i don't understand its concept
 
Knute Snortum
Sheriff
Posts: 3257
84
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose you have a line of data like this:

California, Oregon, Washington

You have the names of three states in the US separated by a comma and a space. In this situation your separator would be ", ".

In your situation, the separator may be a tab, in which case you can use "\\t".
 
Campbell Ritchie
Marshal
Posts: 52532
119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you read the Scanner documentation? It tells you about delimiters there.
 
Winston Gutkowski
Bartender
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:Suppose you have a line of data like this:
California, Oregon, Washington
You have the names of three states in the US separated by a comma and a space. In this situation your separator would be ", ".

@Anam: Except that that's not where the problems with CSVs end, because the data could be:
  "Modesto, California", "Eugene, Oregon", "Edmonds, Washington"
ie, the data includes the delimiter you want to use.

Which is just one reason that TAB-delimiting (which is also a form of "csv") is quite popular - because text data is far less likely to contain a TAB than a comma. Another one is a "vertical bar", viz:
  Modesto, California|Eugene, Oregon|Edmonds, Washington
because it's quite "visual".

So, since it appears you're allowed to choose your own format, my suggestion would be to choose a delimiter that you know will never appear in your data - but make sure you document it.
And just FYI, the statement to "split" the line above would be something like:
  String[] fields = line.split("[|]");
but you really should read up on the method because there's quite a lot to know about it.

1) The user is allowed to input a csv or a txt file....

OK, well the format of a CSV file is quite well documented, and if you stick to something simple, like a vertical bar (and - very important - assume that the data won't contain it), you should be able to handle it quite easily; otherwise, as I said, csv parsing can be quite tricky.

But what's a "txt" file? In general, all it means is that it could contain any kind of text - although it usually means 7-bit ASCII - but it doesn't tell you anything about the format.
So, unless you KNOW what the format will be in advance, it'll be impossible to break it into anything but "lines".

Do you see the problem? You need to understand the parameters of your problem before you can solve it.

HIH

Winston
 
Franklin Okeme
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


 
Carey Brown
Bartender
Posts: 2234
27
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't like this

The number of employeeData Strings could be zero. Obviously this would throw and Exception if the number of Strings is anything less than 7. You need some error handling.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!