Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CSV output issue from database input

Posted on 2013-11-11
16
Medium Priority
?
348 Views
Last Modified: 2013-12-20
I have requirement where I am getting data from database data and sending it to CSV file into multiple columns accordingly.

One of the column data coming as

abc,corp
def,inc

etc.

Due to the comma in the data in CSV instead of
'abc,corp' being sent to single column cell it is sending it two separate adjacent cells of two columns. Due to this all other column data that comes after that is being pushed as well.

I am trying to solve this issue for many days but no luck yet.  I cannot use any CSV Libraries etc.

Is there is simple proof of concept or working example which i can try and later solve my problem.
please advise
Any links resources ideas highly appreciated. Thanks in advance
0
Comment
Question by:gudii9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 36

Assisted Solution

by:mccarl
mccarl earned 800 total points
ID: 39640651
It depends what you are opening this CSV data in, as to what can be done to get around this. I will assume that you might be using MS Excel, and so in that case, you should be able to surrond your data with double quotes, ie. "abc,corp" and Excel should keep that as one column.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39640969
I cannot use any CSV Libraries etc.
Why not? The alternative is what - to use potentially buggy home-brewed code? Parsing CSV is not as easy as it looks at first glance - as you're already seeing
0
 
LVL 7

Author Comment

by:gudii9
ID: 39643239
can you send me some sample code/ link in which takes a simple string let us say
abc,def
and then that java program replaces the comma with a blank space so that i can see out put in the console as
abc def

I will try to extend on top of that. please advise
0
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 
LVL 86

Expert Comment

by:CEHJ
ID: 39643246
Well that could be as simple as

s = s.replace(",", " ");

Open in new window

0
 
LVL 7

Author Comment

by:gudii9
ID: 39646301
the challenge is the data base input values i am not sure which position the comma(,) has. That should be still fine right
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39646362
It doesn't matter what the position is
0
 
LVL 7

Author Comment

by:gudii9
ID: 39670363
any simple example link illustrating this scenario. Please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39670403
If you want to try to 'roll your own' then try

if (s.contains(",")) {
   s = String.format("\"%s\"", s);
}

Open in new window

0
 
LVL 7

Author Comment

by:gudii9
ID: 39676060
I see here CSV to mysql database not other way round. I am still searching to find example from database to CSV. Please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39676080
I'm not sure what the problem is of selecting values and appending commas ...
0
 
LVL 7

Author Comment

by:gudii9
ID: 39681987
some one fixed it already as below i guess

      StringBuilder builder = new StringBuilder();
            builder.append("\"\u00A0")                        
                        .append(getVendorName()).append("\",\"").append(", ")
                        .append("]");;

Not sure what above code means.what is u00A0 and what is  meaning of
.append(getVendorName()).append("\",\"").append(", ")


Now below kind of data coming into one cell of the spreadsheet only  as attached, even though  there is a comma.



abc,corp

Can you please advise how they are able to bring above data to one cell even there is comma. How they escaped comma from incoming data.

I tried looking below site
http://stackoverflow.com/questions/8501072/string-unicode-remove-char-from-the-string

but it is still not clear to me.

what is meaning of below regex
fax = fax.replaceAll("\\D", "");
            return fax.replaceFirst("(\\d{3})(\\d{3})(\\d+)", "$1-$2-$3");
please advise.

i tried to still go ahead and replace comma with space but did not work.


My code is as below which is appending each and every column

      StringBuilder builder = new StringBuilder();
            builder.append("\"\u00A0")                        
                        .append(getVendorName()).append("\",\"").append(", ")
                        .append(getVendorID()).append("\",\"").append(", ")
                          .append(getVendorFax()).append("\",\"").append(", ")
                        .append("]");;

Open in new window


Now i need to apply the condition you mentioned
if (s.contains(",")) {
   s = String.format("\"%s\"", s);
}

I tried as below but not workinng
      StringBuilder builder = new StringBuilder();
            builder.append("\"\u00A0")                        
                        .append(getVendorName()).contains(",").format("\"%s\"", s).append("\",\"").append(", ")
                        .append(getVendorID()).append("\",\"").append(", ")
                          .append(getVendorFax()).append("\",\"").append(", ")
                        .append("]");;

Open in new window



Getting 's' is unknown variable. please advise
VendorSpreadsheetSS.jpg
0
 
LVL 7

Author Comment

by:gudii9
ID: 39682217
I change as below by calling newly created separate method


 StringBuilder builder = new StringBuilder();
            builder.append("\"\u00A0")                        
                        .append(formatVendorName(getVendorName())).append("\",\"\u00A0")
                        .append(getVendorID()).append("\",\"").append(", ")
                          .append(getVendorFax()).append("\",\"").append(", ")
                        .append("]");;

Open in new window


I created separate new method
public static String formatVendorName(String vendorName) {
            if (vendorName.contains(",")) {
                  vendorName= String.format("\"%s\"", vendorName);
                  }
            return vendorName;
      }

The problem i faced is it removed the comma but it pushed 'def' part to next cell with "" as attached and pushing off the phone number also to next cell. please advise how to fix it
VendorSpreadsheetSS2.jpg
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 1200 total points
ID: 39682319
No your append code is confused. Just try to limit chaining to append() only - don't mix other calls into it. \u00A0 is just a non-breaking space - not sure why you're using it
0
 
LVL 7

Author Comment

by:gudii9
ID: 39699125
>>\u00A0 is just a non-breaking space

what is the meaning of non breaking space. what is the link where i can see all these different characters.

so use as below
 StringBuilder builder = new StringBuilder();
            builder.append("\"\u00A0")                        
                        .append(formatVendorName(getVendorName())).append("\",\"").append(", ")
                        .append(getVendorID()).append("\",\"").append(", ")
                          .append(getVendorFax()).append("\",\"").append(", ")
                        .append("]");;

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39699492
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39732857
:)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
The viewer will learn how to implement Singleton Design Pattern in Java.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question