10 Replies Latest reply on Apr 16, 2008 5:35 PM by damianharvey.damianharvey.gmail.com

    Reading new database values from an uploaded csv

      Hello,


      has anybody done a fileUpload with parsing the file to fill the database and can post some code or references, which java or maybe even seam classes can be used for this task?


      Thanks in advance, Peter

        • 1. Re: Reading new database values from an uploaded csv
          nickarls

          Not really code but once you have the uploaded data available
          you can loop BufferedReader.readLine and pass the String line to the entity constructor where you do a String split on the separator char, and populate the entity fields and finally persist the entity.

          • 2. Re: Reading new database values from an uploaded csv

            oh, it's not that easy, because the separator can be contained in the fields itself, so you have to look, where the quotes start and end, to determine if the separator is a real one. The quotes can be used also inside the fields, so you have to check the escape chars, which can can be escaped too ... aaaargh!


            This isn't very nice to implement, but doing it at this level is mandatory, because this redmond spreadsheat program uses commas and doublequotes by default and users often type commas in the fields, for example in descriptions and they also use doublequotes to emphasize text passages. So you would have to instruct the users to change their save settings every time they save their file before uploading.


            It would be a nice feature in the seam tools to have another form with the CRUDs, where users upload a file with entries for a special entity and can use a checkbox to delete the previous db-entries. Someone told me, that phpadmin does such things, but I don't want to install and maintain an apache additionally.


            I'll now take a look at some classes available on the net, that can read in csv .. wish me luck!

            • 3. Re: Reading new database values from an uploaded csv

              Okay, now I found a class CSVReader and adopted it to a single function, that returns an Arraylist<String][]> from a BufferedReader in csv format.


              Now, I want to create entity elements/objects out of this list.


              I had the idea to use too the validations which the crud forms use, so I can give a list, which statments went wrong or better to say, would have went wrong, if I didn rollback the transaction. (I like hibernate)


              As far as I understand it, all parameters of e.g. the edit form come in as a simple String and when it's finished, they are int,double,date,dontknowwhat.


              I would like to understand, what all is done step by step, so where can I have a look at what and how the framework is doing this task?


              If you have a clue of what I am trying to do and the solution is simple I wouldnt say no to the solution or a simple sketch what to do. I am a afraid, that the solution might contain words like introspection or impossible.


              But I still hope, it might be that easy as:


                String[] values = new String[]{"pete","secret","admin","false"};
                Admin admin = new Admin();
                counter = 1; //zero for the id
                foreach(String value:values)
                  { admin.someCoolFunction((counter++),value); }



              or the same with:


                HashMap<String,String> values = new HashMap<String,String();
                  values.add("username","pete");
                  values.add("password","secret");
                  values.add("role","admin");
                  values.add("locked","false"};
                foreach( ...



              Thanks in advance for any insight, Peter

              • 4. Re: Reading new database values from an uploaded csv
                nickarls

                Create an all-strings constructor and convert/set the fields there or set them through reflection (not that difficult and perhaps more elegant).


                In both cases you have to handle bad input. Once the data is good enough to fit into the object you can use hibernate validator programmatically to check the finer details.

                • 5. Re: Reading new database values from an uploaded csv

                  Nicklas Karlsson wrote on Mar 06, 2008 09:20 AM:


                  Create an all-strings constructor and convert/set the fields there or set them through reflection (not that difficult and perhaps more elegant).


                  That's what I thought of, but I'm a little bit laz.. or better said, I believe, that code written by a big team with development experience should always be preferred to stuff that I can do in my limited time. ;-)


                  I already have the Integer.parseInt(csvLine[3]) but I also want to have the validation messages and the field access in a more generic way, so I can use this in further projects without writing all again each time.



                  In both cases you have to handle bad input. Once the data is good enough to fit into the object you can use hibernate validator programmatically to check the finer details.


                  So I would like to use most of the existing stuff, hence I need a route, where I can follow all the steps the seam code does and decide where to link in. Best would be a function, that gets a classname and a value hashmap and returns a list of messages, what went wrong, but that is surely utopic.


                  In the end I want to generate a list like:


                  Row 1: <some userdata> - okay
                  Row 2: <some userdata> - failed  - age can not be negative
                  Row 3: <some userdata> - ignored - empty line
                  Row 4: <some userdata> - failed  - parameter list to short
                  ...
                  summary: there were 42 errors, nothing was saved to database.
                  



                  What I see till now is only a xhtml-file, a page.xml-file, a pojo class, an entity home and an entity query, but I am missing the glue that fits all together.


                  Can you or someone else please tell me, which class(es) is/are responsible for the string conversion/error handling and where I can find the code, so I can take a look at it? I mean before the validator does the value checking.


                  Thanks for now, Pete

                  • 6. Re: Reading new database values from an uploaded csv
                    nickarls

                    Warning: untested code and some catching required etc.


                    
                         public List<String> populate(String classname, String[] fieldNames, List<String[]>data) {
                    
                              List<String> result = new ArrayList<String>();
                    
                              int row = 1;
                    
                              Class clazz = Class.forName(classname);
                    
                              ClassValidator validator = new ClassValidator(clazz);
                    
                              for(String[] datarow : data) {
                    
                                   Object instance = clazz.newInstance();
                    
                                   for (int col = 0; col < fieldNames.length; col++) {
                    
                                        Field field = clazz.getField(fieldNames[col]);
                    
                                        try {
                    
                                             field.set(instance, datarow[col]);
                    
                                        } catch (IllegalArgumentException e) {
                    
                                             result.add("Generate error here");
                    
                                             continue;
                    
                                        }
                    
                                        InvalidValue[] invalidValues = validator.getInvalidValues(instance, fieldNames[col]);
                    
                                        for (InvalidValue invalidValue : invalidValues) {
                    
                                             result.add("Generate error row here");
                    
                                        }
                    
                                        if (invalidValues.length == 0) {
                    
                                             result.add("Generate ok here");
                    
                                        }
                    
                                   }
                    
                              }
                    
                              return result;
                    
                         }     
                    
                    

                    • 7. Re: Reading new database values from an uploaded csv
                      nickarls

                      row++ missing etc.

                      • 8. Re: Reading new database values from an uploaded csv

                        Now I got it working, thank you very very much,  I wouldn't have figured it out in a month!


                        For those who want to try it at home, here's a little hint. The following line doesn't work on fields with private modifier, so a NoSuchFieldException is thrown:


                        field.set(instance, datarow[col]);



                        You can change them to public or try to find the appropriate setterMethod with Class.getMethod() and call them.


                        Thanks a lot again, Peter

                        • 9. Re: Reading new database values from an uploaded csv
                          sandman202

                          Peter, did you get this working? I was looking to do pretty much the same thing and was wondering if you wouldn't mind sharing the code.


                          Thanks,
                          Scott

                          • 10. Re: Reading new database values from an uploaded csv
                            damianharvey.damianharvey.gmail.com

                            Or you can call


                            field.setAccessible(true);
                            


                            before you call


                            field.set(instance, datarow[col]);
                            


                            Cheers,


                            Damian.