Alteryx not reading input data correctly

[eaparsek]

Hi All, 

I'm currently having an issue with a large amount of data where when I load in the file, it shows an incorrect amount of rows and data within those rows. I first discovered this when I was generating a field summary of one of the columns of my data, and I found that data from other fields was being counted in that field summary. I had originally thought this was an issue with the frequency table tool, but I tried just viewing my data directly from the input, and the data displays incorrectly there as well. For example (I can't share any datasets directly due to privacy issues), there are a list of 80 possible values all in the format [1111-blank-blank].blank. I checked the file itself and the column only contains values in this format. Then when I look at the summary tab in Alteryx it shows a bunch of other random values as having a 1 count in this column, which is incorrect and was confirmed directly in the file. When I searched for these incorrect values in the file, they were values from different columns, the column in question in these cases still contained a valid correct value. Alteryx also reports having around more rows than the file actually has. I thought maybe it could be the special characters, so I removed them all, but the issue still persists. Are they any logic issues or Alteryx issues I could be overlooking here that could cause this issue? 

[apathetichell]

what type of data are you reading in - is this a csv file with potentially a delimiter included in some quotes? sounds like it's splitting wrong. It also may be that the fields you are flagging as wrong are header/trailer rows.

[Amy_smart]

Hi,

Here is an answer to a similar question: Solved: Alteryx Processing Input Data With Incorrect Value... - Alteryx Community

Thanks,
Amy

[eaparsek]

Yes I did see and go through this, but the issue here was the way already existing data was being manipulated upon inputting. I'm getting whole new rows of invalid data upon inputting.

[eaparsek]

Hi thank you for your reply! It is currently in CSV, would it possibly help to convert the file to a different format? The fields that are coming through incorrectly are not header/trailer rows, they are random values for other rows of data that were processed correctly. 

[apathetichell]

This sounds like an issue with delimiters. two questions 1) what's the delimiter 2) are you ignoring delimiters in quotes (or another demarker)?  you can try bringing it in without delimiters (/0) - adding a record id - and drilling down to the errored row.  It's worth comparing what Alteryx sees with what you see in notepad.

[eaparsek]

It looks like the delimiter currently is a comma. When you say delimiter in quotes what do you mean by that? Sorry I'm not super familiar with delimiters. 

[apathetichell]

o.k. - you probably want to toggle on ignore delimiter in quotes let me give you an example"

"hello", "hi, this is me" - when split without ignore delimiters in quotes becomes 3 columns ("hello","hi, this is me")

when split with ignore delimiters in quotes tihs becomes two columns("hello","hi, this is me") the comma in "hi, this is me" is ignored because it is within quotes.

[eaparsek]

Ok, I found what you were referring too and tried running it with each of the quote options, but that only made everything a lot worse and jumbled a lot of data together. 

[apathetichell]

Could be that it's the wrong type of quotes. I'd recommend looking in notepad. Hard to tell without seeing your data.