Alteryx Designer Desktop Discussions

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

Importing TXT file with double quote

Wafarian
8 - Asteroid

Hi,

 

I am having issue importing TXT file that has double quotes. My file structure looks like this

imgTXT.PNG

 

and I have used below import settings without getting the desired outcome

 

ImportSettings3.PNG

 

any advice how I can get well structured 4 columns.

9 REPLIES 9
messi007
15 - Aurora
15 - Aurora

@Wafarian,

 

Could you share a sample file ? It will help to give you the solution.

 

Regards,

Wafarian
8 - Asteroid

Hi,

 

see attached for sample file

messi007
15 - Aurora
15 - Aurora

@Wafarian,

 

Please see below :

 

messi007_0-1683193393282.png

Attached the workflow,

Please save the txt input file in the same folder as the workflow.

 

Hope this helps !

Regards,

Wafarian
8 - Asteroid

I still have error as my file doesn't have Field_1 column but yours have.  

messi007
15 - Aurora
15 - Aurora

@Wafarian,

 

Please unceck the filed below in the input tool

 

messi007_0-1683270249166.png

Regards,

Wafarian
8 - Asteroid

Hi,

 

some progress but  now see the issue that I am having. I have tried with the actual data that I am working on and could see that some columns contains blank values hence the reason why I am not getting the desired output.

 

 

 

 

messi007
15 - Aurora
15 - Aurora

@Wafarian,

 

Please see attached workflow.

messi007_0-1683291657681.png

Regards,

ArnaldoSandoval
12 - Quasar

Hi @Wafarian 

 

You are very close, perhaps the "Flat ASCII" (*.flat)" interface, to define the fields is a little fuzzy; these are my steps:

 

  • Open the file as "Read it as fixed width text file.

Impot_TXT-01.png

  • Define the Flat File layout, using the "Flat File Layout" interface, which is a bit tricky, you click where your field start and end, by example, the Field_1 is the double quote at position 1, the click just before the number 1 at the QTY column, then there is another double quote, and so on, look at the image for reference.

 Impot_TXT-02.png

The preview panel (bottom left of the image above) help you to assign the name of the colum, which we do on the "Flat File Layout" on the right, just override the "Field_#' with the corresponding name, also select the field type.

  • The you complete the data cleansing steps within the workflow, like excluding all the fields with names like "Field_#", add a record ID, exclude the first row, apply a data cleansing and remove the trailing double quote from the string fields. 

Impot_TXT-03.png

NOTE:

Fixed width text files were very popular several years ago, until the late 80s, you may still find them; in this type of files, each field has a fixed lenght, you can appreciate that with the numeric fields in your sample data, harder with the string one. 

 

hth

Arnaldo

Wafarian
8 - Asteroid

Hi,

 

the issue is now resolved. basically,  I changed the dilimeter to tab and it resolved it.

 

thankss

Labels
Top Solution Authors