To many fields in record

[Tomer123]

Hi All,

I have a few CSV files that contains around 200,000 rows each.

I want to apply a formula that say: if you find a row with to many fields, go to Column E in that row and erase a cell in that row and shift cells left. (because the problem is always one cell in a row that got shifted one column to the right )

is that possible? 

Thanks! and appreciate your help  

[AngelosPachis]

Hi @Tomer123 ,

Out of the top of my head that's not possible but you can craft a logic that does that for you. For example you might read in the file and some rows will have one extra column. You can transpose each row after assigning a row id to it, and then check if column E (F5) from each row is null and should be removed. If yes, then you should isolate that row because you wanna offset all columns by moving them to the left by one column.

Finally you will union everything together and cross-tab your table back to its original form.

Hope that helps, we might be able to assist further if you can attach an example of those csvs.

Cheers,

Angelos

[Tomer123]

Thanks! i'll give it a try later and respond how it goes.

[BrianR]

Hi @Tomer123 , @AngelosPachis's suggestion is one that I too would recommend. I mocked up something to show you how this might work. If we assume that in cases where the data is shifted over, we can identify the "problem" rows and the suggestion to pivot the data via transpose, and back via crosstab works well. The logic to offset the data can take different forms. In the example below, I created a small lookup table since it's only 4 columns. Take a look, hopefully this gets you on your way.

[Tomer123]

Thank you! but i think i need to figure out another way of solving that cause the problem starts with the "input Data" part so the Alteryx doesn't take all the records in the file so i can't solve it that way. pivot will help after i'll manage to fix the record in the specific file (the problem is i have around 80 files and each one have the issue)