3 Replies Latest reply on Jul 26, 2017 11:47 AM by Tom W

    CSV File Import - Random Fields Left Null

    Luke Sirakos
    Luke Sirakos Jul 26, 2017 8:58 AM

      I am at a loss here and unfortunately I can't attached the data because it is company data but I have a csv file with really basic data in it. I can open the csv file in Excel or in notepad and it all looks good. However, when I connect to this file in Tableau there are just random measures that have null values where they should have numbers in them.  I can see in the raw file the numbers exist but for whatever reason Tableau is just not able to pick them up.  I have connected to CSV files countless times before and never experienced anything like this, I tried a live connection and an extract and it is the same thing.  I am doing no joins, unions, filters or anything other a basic connection to a text file.

       

      Another weird thing, it doesn't just make the entire measure null, only certain rows seemingly at random.  The data is time series so some months have data while others have none, it is quite sporadic.  There are no extra commas in the data file and when I look at it in notepad there is zero indication as to why Tableau would be showing a null value.

       

      I exported the same exact file as an xlsx file and Tableau picked up all of the values just fine. While I could just use this data type going forward I am more concerned with why it is doing what it is doing and I use csv files often so I would rather not have to just hope for the best that it pulled in all the data.

       

      I know that this is difficult since I can't include the file but I don't even think I could replicate this issue with mock data as I have never seen it happen before.

        • 1. Re: CSV File Import - Random Fields Left Null
          Tom W
          Tom W Ambassador Jul 26, 2017 11:35 AM (in response to Luke Sirakos)

          Can't help you without a sample.

          All it would take is one row from your dataset which has a problem with madeup headers .

          • 2. Re: CSV File Import - Random Fields Left Null
            Luke Sirakos
            Luke Sirakos Jul 26, 2017 11:45 AM (in response to Tom W)

            The weird thing is, when I do that it works.  I copied a bunch of rows that were having issues into a new text file and it came up fine. So then I tried copying the whole file into a new text file and it didn't work. 

             

            Another extremely odd issue is when I save the file as an xlsx and I try to change a numerical field to a string it changes all of the numbers to "ge0eral".  When I try to isolate this in a sample file it fixes itself as well.  There must be something corrupted with that data that I just can't figure out because I have done this exact same procedure countless times and never seen anything like this.

            • 3. Re: CSV File Import - Random Fields Left Null
              Tom W
              Tom W Ambassador Jul 26, 2017 11:47 AM (in response to Luke Sirakos)

              I'm guessing you've got line break characters in your data somewhere. Like a comment field from a web application or something where someone can effectively press enter and it creates a new line. This can be misinterpreted by Tableau or Excel and it can cause it to all shift down a line at that point.

              Make sure you're using a text qualifier in Tableau when you connect to the file.