Importing TXT file with double quote
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Datasets
- Input
- Parse
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
see attached for sample file
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Please see below :
Attached the workflow,
Please save the txt input file in the same folder as the workflow.
Hope this helps !
Regards,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I still have error as my file doesn't have Field_1 column but yours have.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
the issue is now resolved. basically, I changed the dilimeter to tab and it resolved it.
thankss
