1 Reply Latest reply on Jun 30, 2016 10:55 PM by Roger Hau

    Join a csv file (meta data) with a union of other csv files (data)

    Simo Tumelius

      Hi,

       

      I'm having problems joining two tables together by a string field. I have a union of csv files containing test data (all files have identical columns) and an additional column Table Name (filename) created as a result of the union. I also have a meta data csv file that contains as many records as there are data files. This meta data csv has a Table Name column containing the data filenames. So

       

           1. I've created a union of csv files, and

           2. I'm trying to left join another csv with the union (Table Name being the joining column).

       

      This results in a table containing all the columns from both joined tables but all the columns that originate from the meta data csv contain only null values. This is supposedly due to non-matching values in the joining column. However, I've checked that both of the tables contain the same values.

       

      Any suggestions how to resolve this? You can find the workbook and the csv files in the attached zip file.

        • 1. Re: Join a csv file (meta data) with a union of other csv files (data)
          Roger Hau

          Hi Simo,

           

          Looks like you've found a bug, and a good one at that. From our initial investigations, looks like we're not adding a collation to the generated columns like "Table Name", which is causing our joins to fail. I've opened a bug and assigned it out, so it will be fixed in a maintenance release.

           

          In the meantime...are you in the 10.0 Beta? In the Beta, we coerce collations if they don't match for cross-database joins if each set of files comes from a different connection (a lot of jargon, but hopefully the snapshot helps). This could be a workaround while we fix the bug:

           

          Note that I created one text connection for the left side of the join, and another for the right side. This will force collation coercion.

           

          Please reach out if you need Beta access or have more questions.

           

          Cheers!

          -Roger and the Data Prep and Integration Team

          1 of 1 people found this helpful