Importing TXT file with double quote

[Wafarian]

Hi,

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

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

any advice how I can get well structured 4 columns.

[messi007]

@Wafarian,

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

Regards,

[Wafarian]

Hi,

see attached for sample file

[messi007]

@Wafarian,

Please see below :

Attached the workflow,

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

Hope this helps !

Regards,

[Wafarian]

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

[messi007 · Accepted solution]

@Wafarian,

Please unceck the filed below in the input tool

Regards,

[Wafarian]

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]

@Wafarian,

Please see attached workflow.

Regards,

[ArnaldoSandoval]

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.

• 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.

 

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. 

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]

Hi,

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

thankss