Java RSS Java RSS

EXCEL CSV FILES Handling

Thread Solved
1 2 Last »

•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

EXCEL CSV FILES Handling

 
0
  #1
Jul 28th, 2008
...
Java Syntax (Toggle Plain Text) 
  1.  Hello everyone i have some csv files that are the result of the inspection machine...
  2.  
  3. these CSV files have the following layout
Java Syntax (Toggle Plain Text) 
  1. ,SRFF File: D:\SPI Master Program List\200-34-02\200-34-02-B-01-R.SRF
  2. ,Panel Name: Panel Description
  3. ,Units: Microns
  4.  
  5. ,Date,Time,PanelId,Board,Location,Part,Package,HeightAvgResult,HeightAvg,HeightAvgUpFail,HeightAvgLowFail,HeightAvgTarget,HeightRangeResult,HeightRange,HeightRangeMax,AreaAvgResult,AreaAvg,AreaAvgUpFail,AreaAvgLowFail,AreaAvgTarget,AreaRangeResult,AreaRange,AreaRangeMax,VolumeAvgResult,VolumeAvg,VolumeAvgUpFail,VolumeAvgLowFail,VolumeAvgTarget,VolumeRangeResult,VolumeRange,VolumeRangeMax,XOffset,YOffset,Rotation,Scaling,HAVFailedFeatureResult,HAVFailedFeatures,HAVFailedFeatureMax,RegFailedFeatureResult,RegFailedFeatures,RegFailedFeatureMax,BridgeFailedFeatureResult,BridgeFailedFeatures,BridgeFailedFeatureMax
  6.  
  7. ,07/19/2008,00:48:55,#280,Module 1 ,IC2,TSOP8,SOP8_1,F,138.539642,238.000000,84.000000,140.000000,P,9.226400,140.000000,P,427747.781250,729933.140625,243311.046875,486622.090000,P,26717.400391,486622.093750,P,60986780.000000,122628772.800000,34063548.000000,68127092.600000,P,3087930.000000,68127096.000000,-22.886625,-23.317499,-0.039985,-0.131595,P,0,1,P,0,1,P,0,1
  8. ,07/19/2008,00:48:55,#280,Module 1 ,C38,0402,0402_1_3,P,131.077194,238.000000,84.000000,140.000000,P,12.636800,140.000000,P,265426.750000,470565.468750,156855.156250,313710.320000,P,5929.500000,313710.312500,P,36161180.000000,79054999.200000,21959722.000000,43919444.800000,P,2340260.000000,43919444.000000,-26.636499,-28.285000,-0.201710,-0.635564,P,0,1,P,0,1,P,0,1
  9. ,07/19/2008,00:48:55,#280,Module 1 ,C66,0402,0402_1_3,P,146.934555,238.000000,84.000000,140.000000,P,4.062300,140.000000,P,266860.500000,470565.468750,156855.156250,313710.320000,P,15511.000000,313710.312500,P,40048568.000000,79054999.200000,21959722.000000,43919444.800000,P,235260.000000,43919444.000000,-32.004501,-21.340000,-0.163571,-1.147347,P,0,1,P,0,1,P,0,1

so i need some help how to create a structure in order to maintain the values related to each column
Java Syntax (Toggle Plain Text) 
  1. Date,Time,PanelId,Board,Location,Part,Package,HeightAvgResult,HeightAvg,HeightAvgUpFail,HeightAvgLowFail,HeightAvgTarget,HeightRangeResult,HeightRange,HeightRangeMax,AreaAvgResult,AreaAvg,AreaAvgUpFail,AreaAvgLowFail,AreaAvgTarget,AreaRangeResult,AreaRange,AreaRangeMax,VolumeAvgResult,VolumeAvg,VolumeAvgUpFail,VolumeAvgLowFail,VolumeAvgTarget,VolumeRangeResult,VolumeRange,VolumeRangeMax,XOffset,YOffset,Rotation,Scaling,HAVFailedFeatureResult,HAVFailedFeatures,HAVFailedFeatureMax,RegFailedFeatureResult,RegFailedFeatures,RegFailedFeatureMax,BridgeFailedFeatureResult,BridgeFailedFeatures,BridgeFailedFeatureMax

in order then to export the data that have Fails - F - and reject the ones that have Passed - P - with the respective column header...

please give me some tips
[/TEX]
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 973
Reputation: Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough 
Solved Threads: 107
Alex Edwards's Avatar
Alex Edwards Alex Edwards is offline Offline
Posting Shark

Re: EXCEL CSV FILES Handling

 
0
  #2
Jul 28th, 2008
If you need a line to be read from a particular value (like a date), you could do one of many things--

-(Hard) create a regular-expression to match dates (in MM/DD/YYYY format) and separate lines once a new date has been analyzed.
-(Easy) create a method that takes a String argument and determines if it is a date by checking for the appropriate characters in a date-format String literal.

--I'd store each line in an ArrayList<String>.

From there I'd probably tokenize each comma separated value and for the particular line then store it in a <String, ArrayList<String> > Map.

Go to my profile, then code snippets and look at "Search Engine" to get an idea of why you'd do this.

You can easily change the separation from a space to a comma in my code snippet.

From there you can use "p" or "f" keys to locate all Modules that passed or failed.

Since the ones of interest are ones that failed, simply retrieve the ArrayList<String> associated with the key "f", and write the information to a file or do what you want with it.

Hopefully this post was helpful.


Edit: If you want to do this an easier way it may help to invest time in a Swing Application and implement a JTable with the columns and rows of the information then have some kind of iterator to retrieve particular rows of information of interest based on a value of a column/row (basically use a double array or an ArrayList of ArrayList<String> values to store the information relative to the JTable and use actionListeners to update the ArrayLists when an update to the JTable has been made.)
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

Re: EXCEL CSV FILES Handling

 
0
  #3
Jul 28th, 2008
•
•
•
•
Originally Posted by Alex Edwards View Post
If you need a line to be read from a particular value (like a date), you could do one of many things--

-(Hard) create a regular-expression to match dates (in MM/DD/YYYY format) and separate lines once a new date has been analyzed.
-(Easy) create a method that takes a String argument and determines if it is a date by checking for the appropriate characters in a date-format String literal.

--I'd store each line in an ArrayList<String>.

From there I'd probably tokenize each comma separated value and for the particular line then store it in a <String, ArrayList<String> > Map.

Go to my profile, then code snippets and look at "Search Engine" to get an idea of why you'd do this.

You can easily change the separation from a space to a comma in my code snippet.

From there you can use "p" or "f" keys to locate all Modules that passed or failed.

Since the ones of interest are ones that failed, simply retrieve the ArrayList<String> associated with the key "f", and write the information to a file or do what you want with it.

Hopefully this post was helpful.


Edit: If you want to do this an easier way it may help to invest time in a Swing Application and implement a JTable with the columns and rows of the information then have some kind of iterator to retrieve particular rows of information of interest based on a value of a column/row (basically use a double array or an ArrayList of ArrayList<String> values to store the information relative to the JTable and use actionListeners to update the ArrayLists when an update to the JTable has been made.)

Thank you for the tips...
I'm planning to do this... however i'm rusty again...what i have done so far was to count the number of F =fails that each csv file had... and assign to the name of the file and colect data regarding the date of creation of the file... i'm going to have a look on your sample.. i hope that u don't mind... by the way,,,, can you give a small sample how to

" Since the ones of interest are ones that failed, simply retrieve the ArrayList<String> associated with the key "f", and write the information to a file or do what you want with it."

its not so easy to me... i understand if you wont reply to this request... i'm learning!!

thanks by the tips
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 973
Reputation: Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough 
Solved Threads: 107
Alex Edwards's Avatar
Alex Edwards Alex Edwards is offline Offline
Posting Shark

Re: EXCEL CSV FILES Handling

 
0
  #4
Jul 28th, 2008
Ok I'd like to apologize - I didn't realize your values extended so far!

What exactly deems a unit as a failure? I see many "passes" values in each row. What is the condition for a unit to be a "fail?"
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

Re: EXCEL CSV FILES Handling

 
0
  #5
Jul 28th, 2008
You don't need to apologize ... i was not so explicit...

In fact what i need to colect is... lets say that i have a faillure... this failure is based in the cross analysis of the values provided... lets say that i have a faillure that is located in
"HeightAvgResult,HeightAvg,HeightAvgUpFail,HeightAvgLowFail"

F,238.539642,238.000000,84.000000,140.000000

what i need is only the value that is in front of the F.. in this case 238.539642 and what kind of faillure that is related to .. in this case... HeightAvgResult...

So the info will appear:
Module 1 ,IC2,TSOP8,SOP8_1,Height,F,138.539642...

i hope that you can understand what i mean.. thanks once again
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 973
Reputation: Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough 
Solved Threads: 107
Alex Edwards's Avatar
Alex Edwards Alex Edwards is offline Offline
Posting Shark

Re: EXCEL CSV FILES Handling

 
1
  #6
Jul 28th, 2008
There are a few ways we can do this.

In my opinion, the best way to do this is create a class that accepts these kind of values and stores them in appropriate data types (the types listed in each column in the first row (Date, time... etc), so it would be wise to use Strings).

For now we'll have 3 classes (maybe more) and have them hold all of the data we need.

When you read the lines from the file you'll store them in the classes the same way I mentioned before.

The classes toString method will be overridden to display all of the information for that particular line of data.

Now this may or may not complicate things but you can write methods that return if a particular column is a fail or not.

Now you can just use an iterator (or array of these classes) where each has different information and when a fail comes up upon invocation of the classes's method and it returns true you can display the toString method of the class that has the information you need.

This is probably the simplest way to do it. I can provide an example but it'll take time to sift through the different value per column.
Last edited by Alex Edwards; Jul 28th, 2008 at 2:05 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

Re: EXCEL CSV FILES Handling

 
0
  #7
Jul 28th, 2008
Thank u for the tips.. i will try to find examples in the Web in order to guid me... because i'm not so good..

the reason i'v choose only the fails was that sometimes i have 1500 files with i don't how many lines... so the data that i need is basically the faillures,,, F, so this will allow me to make my output smaller and provide the correct data regarding the failures...

So... the idea will be if the line has a faillures i will collect the data regarding that line split the line and place the needed data in "Classes" when you mean classe you are refering to a txt file ??
regarding the array... i'm not so good with controling arrays... i suck...i know... but slowly i can get there if well guided.. i want to learn....
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 973
Reputation: Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough Alex Edwards is a jewel in the rough 
Solved Threads: 107
Alex Edwards's Avatar
Alex Edwards Alex Edwards is offline Offline
Posting Shark

Re: EXCEL CSV FILES Handling

 
0
  #8
Jul 28th, 2008
I've provided a class that should help you with this project. Examine it closely--

java Syntax (Toggle Plain Text) 
  1. public class InspectionResults{
  2.  
  3. 	public static final byte  HEIGHT_AVG_RESULT = 0,
  4. 							  HEIGHT_RANGE_RESULT = 1,
  5. 							  AREA_AVG_RESULT = 2,
  6. 							  AREA_RANGE_RESULT = 3,
  7. 							  VOLUME_AVG_RESULT = 4,
  8. 							  VOLUME_RANGE_RESULT = 5,
  9. 							  HAV_FAILED_FEATURE_RESULT = 6,
  10. 							  REG_FAILED_FEATURE_RESULT = 7,
  11. 							  BRIDGE_FAILED_FEATURE_RESULT = 8;
  12. 	private String retrievedData[];
  13. 	private boolean failed[];
  14.  
  15. 	/**
  16. 	 * Constructs this InspectionResult with the data stored in the args.
  17. 	 * This class expects 44 values within the range of the args.
  18. 	 */
  19. 	public InspectionResults(String... args){
  20. 		retrievedData = args;
  21. 		boolean temp[] ={
  22. 			((retrievedData[7].equalsIgnoreCase("F")) ? true: false),
  23. 			((retrievedData[12].equalsIgnoreCase("F")) ? true: false),
  24. 			((retrievedData[15].equalsIgnoreCase("F")) ? true: false),
  25. 			((retrievedData[20].equalsIgnoreCase("F")) ? true: false),
  26. 			((retrievedData[23].equalsIgnoreCase("F")) ? true: false),
  27. 			((retrievedData[28].equalsIgnoreCase("F")) ? true: false),
  28. 			((retrievedData[35].equalsIgnoreCase("F")) ? true: false),
  29. 			((retrievedData[38].equalsIgnoreCase("F")) ? true: false),
  30. 			((retrievedData[41].equalsIgnoreCase("F")) ? true: false)
  31. 		};
  32. 		failed = temp;
  33. 	}
  34.  
  35. 	/**
  36. 	 * Returns true if the given value has failed, returns false otherwise.
  37. 	 * It's preferred to use the constants defined within this class to get the
  38. 	 * desired information, and not regular ints.
  39. 	 */
  40. 	public boolean hasFailed(byte result) throws Exception{
  41. 		if(result >= 0 && result < failed.length)
  42. 			return failed[result];
  43. 		else{
  44. 			throw new Exception("Attempt to access invalid result type! Use the Result Constants to avoid this error!");
  45. 		}
  46. 	}
  47.  
  48. 	/**
  49. 	 * Returns the value next to the specified result.
  50. 	 */
  51. 	public String getAdjacentValue(byte result) throws Exception{
  52. 		if(result >= 0 && result < retrievedData.length - 1)
  53. 			return retrievedData[result + 1];
  54. 		else throw new Exception("Error! Attempt to access column with either no adjacent value or outside of data-range!");
  55. 	}
  56.  
  57. 	/**
  58. 	 * Simply returns a String representing the data for each value in this class.
  59. 	 */
  60. 	@Override
  61. 	public String toString(){
  62. 		String temp = "";
  63. 		for(String element : retrievedData){
  64. 			if(element.toString() != retrievedData[retrievedData.length - 1])
  65. 				temp += element + ", ";
  66. 			else temp += element;
  67. 		}
  68. 		return temp;
  69. 	}
  70. }

If you can manage to retrieve each line of information from the file and store it in a new object of this class you should be able to eliminate a world of trouble.

I took the time to look through which values had a P or F next to it. The Constructor of this class is expecting an Array of Strings, so you will need to use a tokenizer for each line you examine to tokenize a line of value separated by commas.

If you want to turn a line of information into an Array you can use the
String.split(",") command (where String is replaced by the actual value that contains the String-value of the line of information you retrieve), to turn your line into an array of Strings. From there you can create a new object of this class that will handle the comparisons for you (checking if a particular value is P or F) and also enables you to retrieve the Adjacent value (which actually needs to be edited - more constants are needed to get the appropriate adjacent values OR you could just enter the numbers of the column to get the adjacent values which may be annoying but I forgot to implement that).

Hopefully this helps you out.
Last edited by Alex Edwards; Jul 28th, 2008 at 3:05 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

Re: EXCEL CSV FILES Handling

 
0
  #9
Jul 28th, 2008
Uau.. you are fast.. i will give a look and tryout with my program... i have evrything done regarding access the folders read/access the csv files, count the number of ocurrences... store the data in the txt file and also send the data to a DB.... this is what is missing i will try and tomorrow i will give you the feedback.... thank you once again... C u
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 45
Reputation: jorgeflorencio is an unknown quantity at this point 
Solved Threads: 0
jorgeflorencio jorgeflorencio is offline Offline
Light Poster

Re: EXCEL CSV FILES Handling

 
0
  #10
Jul 28th, 2008
regarding
"so you will need to use a tokenizer for each line you examine to tokenize a line of value separated by commas."

I have that as well...
Reply With Quote Quick reply to this message  
Reply
1 2 Last »

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Java Forum
Thread Tools Search this Thread



-xlint actionlistener android api applet application array arrays automation bi binary blackberry block bluetooth character class client code compile compiler component consumer database desktop developmenthelp eclipse error fractal freeze ftp game gameprogramming givemetehcodez graphics gui health html ide image integer j2me j2seprojects java javac javaee javaprojects jetbrains jni jpanel jtable julia learningresources lego linked linux list login loops mac main map method methods mobile netbeans notdisplaying number online printf problem program project properties qt recursion researchinmotion rotatetext rsa scanner screen server set singleton sms sort sql string swing system textfields threads time title tree tutorial-sample update variablebinding windows working xor
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC