DosTips.com

Posted: **08 Apr 2018 11:24**

Hi Folks -

I do some excel to csv conversion and ETL work so sometimes there are extra commas. (Yes, I could add a piece to the VB code to do this).

To remove these commas, I'm using the following syntax:

Code: Select all

CD /D "%~dp0"
call jrepl ",," "" /F "Test.csv" /O -
call jrepl ",$" "" /F "Test.csv" /O -

And here is my file:

Code: Select all

ACT,10000000363014,1,2017,11,6,,,,,,,,,,,,,,,,,,,,
ACT,10000000363014,8,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363053,1,2017,11,2.6,,,,,,,,,,,,,,,,,,,,
ACT,10000000363053,8,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363065,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363065,8,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363013,1,2017,11,2,,,,,,,,,,,,,,,,,,,,
ACT,10000000363013,8,2017,11,0.6,,,,,,,,,,,,,,,,,,,,
ACT,10000000363027,1,2017,11,4.8,,,,,,,,,,,,,,,,,,,,
ACT,10000000363027,8,2017,11,2,,,,,,,,,,,,,,,,,,,,
ACT,10000000363017,1,2017,11,3,,,,,,,,,,,,,,,,,,,,
ACT,10000000363017,8,2017,11,3,,,,,,,,,,,,,,,,,,,,
ACT,10000000363011,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363043,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363064,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000363035,1,2017,11,3.6,,,,,,,,,,,,,,,,,,,,
ACT,10000000363021,1,2017,11,2,,,,,,,,,,,,,,,,,,,,
ACT,10000000703500,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000113170,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000373821,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000853170,1,2017,11,2,,,,,,,,,,,,,,,,,,,,
ACT,10000000833102,1,2017,11,1.75,,,,,,,,,,,,,,,,,,,,
ACT,10000000833109,3,2017,11,0,,,,,,,,,,,,,,,,,,,,
ACT,10000000303701,1,2017,11,3,,,,,,,,,,,,,,,,,,,,
ACT,10000000302890,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000852968,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
ACT,10000000903250,1,2017,11,1,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,

I assume there may be a more efficient way?

Thanks!

Posted: **08 Apr 2018 12:38**

Well you can combine the two JREPL calls into one (the pipe character | means something like "or").

Code: Select all

CD /D "%~dp0"
call jrepl ",,|,$" "" /F "Test.csv" /O -

Meerkat

Posted: **08 Apr 2018 20:13**

It looks like you want to delete any 1 or more commas that immediately precede the end of line.
That translates to a very simple regular expression

Code: Select all

jrepl ",+$" "" /f "test.csv" /o -

Dave Benham

Posted: **14 Apr 2018 05:31**

Hi Dave & Meer -

Both are working great - thank you so much!

DosTips.com

JREPL Usage - working but is this correct?

JREPL Usage - working but is this correct?

Re: JREPL Usage - working but is this correct?

Re: JREPL Usage - working but is this correct?

Re: JREPL Usage - working but is this correct?