CODEWITHSUNDEEP
jqueryregexcsvreplace
James Mathieson
James Mathieson

Reputation: 439

Clean up a CSV file with jQuery

I have staff exporting data into CSV from one system, and using it to import content in our CMS.

The problem is that the CSV exports aren't formatted cleanly enough for our CSV import scripts.

Is jQuery capable of the following?

I'd like to convert, for example, a string like:

Hello,"Goodbye, Goodbye","Goodbye",Hello,Hello,Hello,"Goodbye, Goodbye"

To:

"Hello","Goodbye, Goodbye","Goodbye","Hello","Hello","Hello","Goodbye, Goodbye"

--so that every entry is consistently wrapped in quotations, not just entries that contain commas (,).

I've been using .replace(), but can't discover a consistent method to handle this problem.

Upvotes: 0

Views: 260

Answers (2)

ngrashia
ngrashia

Reputation: 9904

var value = $("#text").val();

// Hello,"Goodbye, Goodbye","Goodbye",Hello,Hello,Hello,"Goodbye, Goodbye"

value = value.replace(',', '","');

// Hello",""Goodbye"," Goodbye"",""Goodbye"","Hello","Hello","Hello",""Goodbye"," Goodbye"

value = '"' + value + '"';

// "Hello",""Goodbye"," Goodbye"",""Goodbye"","Hello","Hello","Hello",""Goodbye"," Goodbye""

value = value.replace ('""','"');

// To replace all the double "" to single ". You will get your desired output

*Updated answer:*

Replace replaces only the first occurance of a particular string. Try the following instead:

   var value =  $('#test').val();
   value = value.replace(/,/g, '","');
   value = '"' + value + '"';
   value = value.replace (/""/g,'"');
   $('#result').html(value);

Upvotes: 1

sanjeev
sanjeev

Reputation: 4631

Check this solution, it will first find all correct occurence and replace them with a particular string

correct the improper string and then give the correct result

See the javascript Console to see the changes

        <script type='text/javascript'>//<![CDATA[ 
        $(window).load(function(){

          OurTestData = 'Hello,"Goodbye, Goodbye","Goodbye",Hello,Hello,Hello,"Goodbye, Goodbye"';
          console.log(OurTestData);
            var pattern = /"([^"]+)"/g;
            var match;
            var MatchedArray = [];
            while(match = pattern.exec(OurTestData)) {
                MatchedArray.push(match[1]);
            }

             OurTestData = OurTestData.replace(/"([^"]+)"/g, "CorrectSavedForLaterConversion");

             splittedArray = OurTestData.split(',');
             var preOutputString ="";

              preOutputString= '"' + splittedArray.join('" , "') + '"';



         /**
           Replace With Correct String
         */
           i=0;

         while(preOutputString.indexOf('CorrectSavedForLaterConversion') >= 0){

                preOutputString=  preOutputString.replace(/CorrectSavedForLaterConversion/,MatchedArray[i]) 
            i++;
          }

          console.log(preOutputString);

        });//]]>  

        </script>

Upvotes: 1

Related Questions