-1

I am using OpenCSV 4.6 to read and write csv files. I have a java bean like

   public class CMMProjectInfo implements Serializable {

        private static final long serialVersionUID = 1L;

       @CsvBindByName(column = "ProjectName",required = true)
       private String projectName;

       @CsvBindByName(column = "ProjectCode",required = true)
       private String projectCode;

       @CsvBindByName(column = "Visibility",required = true)
       private String visibility;
  }

If user uploads valid csv file like below

ProjectCode;ProjectName;Visibility
T1;Test, 1;1
T2;Test2;0
TST;Test3;1

we are able to map the headers to object by using HeaderColumnNameMappingStrategy. If user edits the csv file using excel editor then file is changing like below(it's appending additional characs(, and "))

ProjectCode;ProjectName;Visibility,
T1;Test,1;1
T2;Test2;0,
TST;Test3;1,

Now if user uploads the file which was edited by excel file then failed to map the headers and getting error Error capturing CSV header! because when we try to map headers it will split based on delimiter(;) then values will be 1. ProjectCode 2. ProjectName and 3.Visibility, last header value has additional characters that's why it's failing to map the header name from Visibility to Visibility,

Note: Here problem with comma(,) in input file. I cannot restrict the user to upload file which has comma in values.

Is there any way to solve this issue while mapping the headers?

gprasadr8
  • 789
  • 1
  • 8
  • 12
  • 2
    It looks like when it's read in Excel, it's read without setting the delimiter to semicolon. – RealSkeptic Dec 11 '19 at 10:03
  • Ideally, if CSV is opened in Excel, semicolon should be used as a delimiter in 'text to columns' option in data tab. If this is too much for the users, then you are better off using excel file instead of CSV. – Smile Dec 11 '19 at 10:03
  • It's an old project now I can't change file format. earlier we were reading the file as a normal text file and hard coded file headers, now I am trying to use opencsv to make it generic code to read multiple files without duplicating it – gprasadr8 Dec 11 '19 at 10:11
  • You are not supposed to change the file format, you are supposed to tell the user to open (and save) the file explicitly setting the delimiter to semicolon. – RealSkeptic Dec 11 '19 at 10:24
  • we have tried but one or the other user uploading the file with additional characs – gprasadr8 Dec 11 '19 at 11:25

1 Answers1

0

Currently found some quick solution but not complete solution for the question. When we are writing to csv file I am adding additional delimiter(;) at the end of the line before(\n) end line.

StatefulBeanToCsv beanWriter = builder
                        .withApplyQuotesToAll(false)
                        .withSeparator(';')
                        .withMappingStrategy(new AnnotationStrategy(this.inputData.iterator().next().getClass()))
                        .withLineEnd(";\n")
                        .build();

changed from withLineEnd("\n") to withLineEnd(";\n"). This will add additional delimiter at the end of the line. After that file is downloading like below

ProjectCode;ProjectName;Visibility;
T1;Test, 1;1;
T2;Test2;0;
TST;Test3;1;

If we edit the file using excel editor then additional characters are appending at the end of the line not on the existing header. For Example after editing the file it's showing like below

ProjectCode;ProjectName;Visibility;,
T1;Test,1;1;
T2;Test2;0;,
TST;Test3;1;,

When we map to headers it's splitting headers line like below

  1. ProjectCode 2. ProjectName 3.Visibility and 4. ", even though we are getting additional header now HeaderColumnNameMappingStrategy is able to map the headers properly.

I know it's not the complete solution. If you have any other thoughts please share

gprasadr8
  • 789
  • 1
  • 8
  • 12