Replacing spaces in Ascii file (Nifi)

[#1]

Hi, I'm attempting to replace values in an ASCII file that im importing from a website. I've added the header with replace text but, I'm having a hard time replacing the spaces with commas so, I can convert the file into csv and later to json. Here is what I'm dealing with. 

Edit: I should add that sometimes there is one space other times there are two spaces. As of now I have two spaces in the search attribute and a comma in the replace attribute works for part of this problem.

Value

Search Value

Replacement Value

,

Character Set

UTF-8

Maximum Buffer Size

1 MB

Replacement Strategy

Regex Replace

Evaluation Mode

Line-by-Line

Line-by-Line Evaluation Mode

All

Before:

Year,Day,Hour,Minute,ID_for_SW_Plasma_spacecraft,Percent_of_interpolation,Timeshift,RMS_Timeshift,RMS_Min_var,Time_btwn_observation_sec,Field_magnutude_average_nT,BY_nT(GSM),BZ_nT_(GSM),RMS_SD_B_scalar_nT,RMS_SD_field_vector_nT,Speed_km/s,Alfven_mach_number,Magnetosonic_Mach_number,BSN_location_Xgse_Re,2019 1 0 0 51 100 2788 164 0.12 999999 5.11 2.00 2.73 0.08 1.03 451.0 9.8 6.5 13.15
2019 1 0 1 51 100 2810 159 0.12 37 5.10 2.33 2.58 0.11 1.04 451.3 9.8 6.5 13.10
2019 1 0 2 51 80 2852 109 0.09 18 4.86 2.37 2.56 0.12 0.56 454.7 10.3 6.7 13.07
2019 1 0 3 51 67 2951 66 0.06 -39 4.78 2.21 2.55 0.03 0.33 452.3 11.0 6.8 13.00
2019 1 0 4 51 100 3025 7 0.00 -13 4.80 2.17 2.37 0.03 0.14 451.4 11.2 6.8 13.00
2019 1 0 5 99 80 2973 111 0.09 111 4.94 2.68 2.39 0.13 0.55 99999.9 999.9 99.9 13.19
2019 1 0 6 51 67 3074 20 0.02 -40 4.88 2.54 2.01 0.02 0.28 451.0 9.8 6.5 13.27
2019 1 0 7 51 50 3114 9 0.00 19 4.82 2.37 2.93 0.02 0.14 451.0 9.9 6.5 13.29
2019 1 0 8 99 999 999999 999999 99.99 999999 9999.99 9999.99 9999.99 9999.99 9999.99 99999.9 999.9 99.9 9999.99
2019 1 0 9 99 100 3036 0 0.00 999999 5.16 3.34 2.44 0.00 0.00 99999.9 999.9 99.9 13.24
2019 1 0 10 99 100 3036 0 0.00 60 5.16 3.34 2.43 0.00 0.00 99999.9 999.9 99.9 13.24

after:

Year,Day,Hour,Minute,ID_for_SW_Plasma_spacecraft,Percent_of_interpolation,Timeshift,RMS_Timeshift,RMS_Min_var,Time_btwn_observation_sec,Field_magnutude_average_nT,BY_nT(GSM),BZ_nT_(GSM),RMS_SD_B_scalar_nT,RMS_SD_field_vector_nT,Speed_km/s,Alfven_mach_number,Magnetosonic_Mach_number,BSN_location_Xgse_Re,2019, 1,0,0 51 100, 2788,,164,0.12 999999,,5.11,,2.00,,2.73,,0.08,,1.03, 451.0, 9.8,6.5, 13.15
2019, 1,0,1 51 100, 2810,,159,0.12,, 37,,5.10,,2.33,,2.58,,0.11,,1.04, 451.3, 9.8,6.5, 13.10
2019, 1,0,2 51,80, 2852,,109,0.09,, 18,,4.86,,2.37,,2.56,,0.12,,0.56, 454.7,10.3,6.7, 13.07
2019, 1,0,3 51,67, 2951,, 66,0.06,,-39,,4.78,,2.21,,2.55,,0.03,,0.33, 452.3,11.0,6.8, 13.00
2019, 1,0,4 51 100, 3025,,,7,0.00,,-13,,4.80,,2.17,,2.37,,0.03,,0.14, 451.4,11.2,6.8, 13.00
2019, 1,0,5 99,80, 2973,,111,0.09,,111,,4.94,,2.68,,2.39,,0.13,,0.55 99999.9 999.9 99.9, 13.19
2019, 1,0,6 51,67, 3074,, 20,0.02,,-40,,4.88,,2.54,,2.01,,0.02,,0.28, 451.0, 9.8,6.5, 13.27
2019, 1,0,7 51,50, 3114,,,9,0.00,, 19,,4.82,,2.37,,2.93,,0.02,,0.14, 451.0, 9.9,6.5, 13.29
2019, 1,0,8 99 999 999999 999999 99.99 999999 9999.99 9999.99 9999.99 9999.99 9999.99 99999.9 999.9 99.9 9999.99
2019, 1,0,9 99 100, 3036,,,0,0.00 999999,,5.16,,3.34,,2.44,,0.00,,0.00 99999.9 999.9 99.9, 13.24
2019, 1,0 10 99 100, 3036,,,0,0.00,, 60,,5.16,,3.34,,2.43,,0.00,,0.00 99999.9 999.9 99.9, 13.24
2019, 1,0 11 99 999 999999 999999 99.99 999999 9999.99 9999.99 9999.99 9999.99 9999.99 99999.9 999.9 99.9 9999.99
2019, 1,0 12 99 999 999999 999999 99.99 999999 9999.99 9999.99 9999.99 9999.99 9999.99 99999.9 999.9 99.9 9999.99

[#2]

@ForrestGump I believe you are using ReplaceText.

I use it like this:

[#3]

Unfortunately this method does not work @stevenmatison 

I using Replace Text but the only method that has worked so far is below. this has only lead to the following results:

Year,Day,Hour,Minute,ID_for_SW_Plasma_spacecraft,Percent_of_interpolation,Timeshift,RMS_Timeshift,RMS_Min_var,Time_btwn_observation_sec,Field_magnutude_average_nT,BY_nT(GSM),BZ_nT_(GSM),RMS_SD_B_scalar_nT,RMS_SD_field_vector_nT,Speed_km/s,Alfven_mach_number,Magnetosonic_Mach_number,BSN_location_Xgse_Re2019,,,1,,0,,0,51,100,,,2788,,,,164,,0.12,999999,,,,5.11,,,,2.00,,,,2.73,,,,0.08,,,,1.03,,,451.0,,,9.8,,6.5,,,13.15,2019,,,1,,0,,1,51,100,,,2810,,,,159,,0.12,,,,,37,,,,5.10,,,,2.33,,,,2.58,,,,0.11,,,,1.04,,,451.3,,,9.8,,6.5,,,13.10,2019,,,1,,0,,2,51,,80,,,2852,,,,109,,0.09,,,,,18,,,,4.86,,,,2.37,,,,2.56,,,,0.12,,,,0.56,,,454.7,,10.3,,6.7,,,13.07,2019,,,1,,0,,3,51,,67,,,2951,,,,,66,,0.06,,,,-39,,,,4.78,,,,2.21,,,,2.55,,,,0.03,,,,0.33,,,452.3,,11.0,,6.8,,,13.00,2019,,,1,,0,,4,51,100,,,3025,,,,,,7,,0.00,,,,-13,,,,4.80,,,,2.17,,,,2.37,,,,0.03,,,,0.14,,,451.4,,11.2,,6.8,,,13.00,2019,,,1,,0,,5,99,,80,,,2973,,,,111,,0.09,,,,111,,,,4.94,,,,2.68,,,,2.39,,,,0.13,,,,0.55,99999.9,999.9,99.9,,,13.19,2019,,,1,,0,,6,51,,67,,,3074,,,,,20,,0.02,,,,-40,,,,4.88,,,,2.54,,,,2.01,,,,0.02,,,,0.28,,,451.0,,,9.8,,6.5,,,13.27,2019,,,1,,0,,7

Search Value

\s

Replacement Value

,

Character Set

UTF-8

Maximum Buffer Size

1 MB

Replacement Strategy

Regex Replace

Evaluation Mode

Line-by-Line

Line-by-Line Evaluation Mode

All

[#4]

Notice my settings for All Properties and I use Regex.

If you don't want to use Regex, change Replacement Strategy.

I would recommend doing it as I have shown, with regex on complete flow file. Not line by line.  Also, you can append and prepend more text and transformations in same replace too. For Example:

PREPEND
${'$1':replace('"',''):replace(',',' '):replace('\n',',\n')}
APPEND

[#5]

I've made these modification and all it does is route it to failed. 

[#6 · Accepted solution]

@ForrestGump  there must be some configuration difference then.   I created a simple flow and was able to get below output using exact proc I screen shot above on your Before data:

Year,Day,Hour,Minute,ID_for_SW_Plasma_spacecraft,Percent_of_interpolation,Timeshift,RMS_Timeshift,RMS_Min_var,Time_btwn_observation_sec,Field_magnutude_average_nT,BY_nT(GSM),BZ_nT_(GSM),RMS_SD_B_scalar_nT,RMS_SD_field_vector_nT,Speed_km/s,Alfven_mach_number,Magnetosonic_Mach_number,BSN_location_Xgse_Re,2019,1,0,0,51,100,2788,164,0.12,999999,5.11,2.00,2.73,0.08,1.03,451.0,9.8,6.5,13.15
2019,1,0,1,51,100,2810,159,0.12,37,5.10,2.33,2.58,0.11,1.04,451.3,9.8,6.5,13.10
2019,1,0,2,51,80,2852,109,0.09,18,4.86,2.37,2.56,0.12,0.56,454.7,10.3,6.7,13.07
2019,1,0,3,51,67,2951,66,0.06,-39,4.78,2.21,2.55,0.03,0.33,452.3,11.0,6.8,13.00
2019,1,0,4,51,100,3025,7,0.00,-13,4.80,2.17,2.37,0.03,0.14,451.4,11.2,6.8,13.00
2019,1,0,5,99,80,2973,111,0.09,111,4.94,2.68,2.39,0.13,0.55,99999.9,999.9,99.9,13.19
2019,1,0,6,51,67,3074,20,0.02,-40,4.88,2.54,2.01,0.02,0.28,451.0,9.8,6.5,13.27
2019,1,0,7,51,50,3114,9,0.00,19,4.82,2.37,2.93,0.02,0.14,451.0,9.9,6.5,13.29
2019,1,0,8,99,999,999999,999999,99.99,999999,9999.99,9999.99,9999.99,9999.99,9999.99,99999.9,999.9,99.9,9999.99
2019,1,0,9,99,100,3036,0,0.00,999999,5.16,3.34,2.44,0.00,0.00,99999.9,999.9,99.9,13.24
2019,1,0,10,99,100,3036,0,0.00,60,5.16,3.34,2.43,0.00,0.00,99999.9,999.9,99.9,13.24

I have dropped the template for you here:

https://github.com/steven-dfheinz/NiFi-Templates/blob/master/Replace_Text_Demo.xml

[#7]

Thanks for the effort @stevenmatison I imagine it does have to do with configuration.