Input tool not reading big CSV file correctly

FSuharjo

Hi!

I have one CSV file with about 3.5M records (size around 1.5GB). I notice that some records are not read correctly by the input tool. 

I am using Alteryx designer 2018.3 (for server compatibility reason) 

FYI, the delimiter is pipe "|" and text identifier is a quote. 

I tried to import part of the records in Excel power query and it works. Does anyone have a similar experience? or maybe I missed something. 

Import in Excel Power Query (correct ones in red mark):

Import in Alteryx:

T_Willins

Hi @FSuharjo,

I've run into this problem with SAP files as well where the delimiter doesn't parse correctly.  In fact Weekly Challenge #188 came out of trying to parse an SAP text file.  There are a few different ways I have approached this.  First, since the pipe is uncommon in SAP text fields, if you change the Ignore Delimiters in to "Auto" or "None" does this correct the problem (without introducing additional problems)?  Second, try bringing in the data without delimiters (single field), then using a Text to Columns to parse the data by the pipe.  Sometimes this works when the input tool does not correctly parse even though it should produce the same results.  If neither of these work, you can bring in the file as a Flat ASCII format and use the File/Field layout to set the columns.  This is the most time consuming and will require additional data cleanup, but usually works with these files when nothing else will.  I don't think the size of the file or your Alteryx version should be causing this issue.  However, if you have admin rights on your computer you can download Designer 2019.3 and see if it makes a difference.  Instructions here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Two-version-of-Alteryx/td-p/56265