Hi,
I have Data_original.csv which have many columns of | or _ or none. In this example, I'm showing only 3 columns of |.
I tried the data preview to see if I can split but my column got messed up.
Data_original.csv
Fruit,ID,Colors,Weigth,CountryCode
Apple,123456,5_2|1_4,3|3|3|oz,1_2_5|3_2_1_123_2343_342|1_2
Apricot,5555666,9_0|55_298,5|3|2|1|lb,0_9_1|23
Grape,98746,12_1|23_99,1|2|1|7|6|4|2|1|8|g,9_1_3|23_45_4
Orange,25897,8_8|0_9,2|3|fl,1_0_4|99_999_888|0_8
Pear,113355,9999_77|78_123,4|6|l,-9999_76_123|9
Plum,8855446,32_-8888|00_12,5|9|lb,123_1_55|123_90
Strawberries,981254,981_123|34_123,8|3|g,89_12_45|67_1_23_8|90
Open(
"C:\JSL\Data_original.csv",
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, Other( "|" ), CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
First, I want to open up Data_original.csv. Delete any columns of | split (if there any and keep the first value split only). I was thinking once I split, I should be able to delete any column header name with Column* and save it out to csv.
See the output from Data_original_EDIT1.csv.
Fruit,ID,Colors,Weigth,CountryCode
Apple,123456,5_2,3,1_2_5
Apricot,5555666,9_0,5,0_9_1
Grape,98746,12_1,1,9_1_3
Orange,25897,8_8,2,1_0_4
Pear,113355,9999_77,4,-9999_76_123
Plum,8855446,32_-8888,5,123_1_55
Strawberries,981254,981_123,8,89_12_45
Second, I want to open the previous save csv, Data_original_EDIT1.csv. Split by _ column (if any) and rename the column header as the same column header but concatenate a sequence 0..n.
See Data_original_EDIT2_Final.csv, the final output export to csv.
Fruit,ID,Colors0,Colors1,Weigth,CountryCode0,CountryCode1,CountryCode2
Apple,123456,5,2,3,1,2,5
Apricot,5555666,9,0,5,0,9,1
Grape,98746,12,1,1,9,1,3
Orange,25897,8,8,2,1,0,4
Pear,113355,9999,77,4,-9999,76,123
Plum,8855446,32,-8888,5,123,1,55
Strawberries,981254,981,123,8,89,12,45
Let me know how I start the JSL coding. Thanks.