Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Input tool not reading big CSV file correctly

FSuharjo
5 - Atom

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):

FSuharjo_0-1573575431702.png

 

Import in Alteryx:

FSuharjo_1-1573575580791.png

FSuharjo_3-1573575741967.png

 

2 REPLIES 2
T_Willins
14 - Magnetar
14 - Magnetar

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

FSuharjo
5 - Atom

Hi @T_Willins

 

Thanks a lot for your reply. The second workaround works! I import as a single column and then split using text to columns. 

As you mentioned below, not sure how it can be a different result compared to the direct input tool. 

 

I also try to install 2019.3 (non-admin) and got the same issue, so indeed maybe not related to the version. 

 

Thanks!

Labels