2 Replies Latest reply on Dec 17, 2013 10:27 AM by philmodjunk

    Need help with a script between two csv files

    JustinI

      Title

      Need help with a script between two csv files

      Post

           Hi All-

           Working on a very difficult database issue.  I have 2 databases:  (File 1)    (File 2)

           (File 1)  Contains 1 column.  This column contains "Category Names" separated by a semicolon and a space proceeding the semicolon.

           File 1 Ex: 

                                                                                                                                                                         
                          Category-A; Category-C; Category-D
                          Category-B
                          Category-C;Category-D;Category-E
                          Category-D; Category-E

           (File 2) contains 2 columns.  The first part of the column is a "Category ID" which is numerical based. the second column is the "Associated Category Name" to the Category Number.

           File 2 Ex:

                                                                                                                                                                                                                                                                                                           
                          Category ID                     Category Name
                          37                     Category-A
                          38                     Category-B
                          39                     Category-C
                          40                     Category-D

           What I need to do, is to make the first file based solely on the "Category ID"  WITHOUT the space after the semicolon.  So the result SHOULD look like this:

                                                                                                                                                                         
                          37;39;40
                          38
                          39;40;41
                          40;41

            

           Now, File 1 is a HUGE file, with over 269,000 lines.  I was trying to do this manually, but there HAS to be a better way!   File 2 is only around 61 lines, starting at ID number "37"

           Any help would be GREATLY appreciated!

            

           Thanks, Best Regards & Happy Holidays!

           -Justin

            

        • 1. Re: Need help with a script between two csv files
          SteveStrickland_1

               Not sure if this needs to be automated or not but a starting idea would be to use Find/Replace. There's only 61 categories so this wouldn't take terriby long to do by hand. You could script it easily enough if it's a repeated task. Then do one last Find/Replace to eliminate the trailing semi-colon

               Just curious - are you converting a dBf file?

          • 2. Re: Need help with a script between two csv files
            philmodjunk

                 Define a calculation field with text as the result type in the table into which you import the data from File1:

                 Substitute ( ImportedDateColumnHere ; ";" ; ¶ )

                 Name it cCategoryNameList.

                 Define a relationship to the table that receives the csv data from the second table like this

                 Table1::cCategoryNameList = Table2::CategoryName

                 Now you can define another calculation field in Table1 as:

                 Substitute ( List ( Table2::CategoryID ) ; ¶ ; ";" )

                 to get your semi colon delimited list.