ABout extracting data

[prakashatutd]

Hello:

What is the best way to read / convert data in this csv file (with , delimiter). 

Thanks

[apathetichell]

Why can't you use an input data/csv/comma delimited? after brining in - you should turn date of delivery into a date field - use datetimeadd("1899-12-31",[Date of SDelivery],"days") - note that might be "1900-01-01" - I can't remember which one Alteryx uses.

[Bren_Spill]

@prakashatutd - see attached, including @apathetichell's date conversion. Thanks!

[prakashatutd]

I did use Comma delimiter.

I refreshed the configuration

But the output still had just same one column

I can do this in Excel easily --

[prakashatutd]

Yes, This worked.

I was thinking that this would not require any tool -- This assumption was based on the only example I learned in the class that used the following file. It did not require any additional tools. 

Curious as to why?

[Bren_Spill]

@prakashatutd - in the input configuration, try changing 9 - Ignore Delimiters in to None

[KGT]

One thing to add to this. If those are excel dates, you will need to use "1899-12-30" as the date in the formula. the Alteryx DateTimeAdd formula adds to a date and the excel number is meant to be the number counting up from "1900-01-01".

So, to use the datetimeadd, you need to move the date back one to 1899-12-31 to then count up.

But then, you need to account for the bug in excel that they can't fix because it's been there too long. Excel thinks 1900 was a leap year when in fact it wasn't (every 4, except every 100, except every 400), so you need to move the date back another one to "1899-12-30".

To verify, try putting a couple of 29th feb dates in to excel. 1904 will be a date, 1900 will be a date, 1903 will stay as general. However, there was no 29th feb in 1900.

[prakashatutd]

This solution worked! Many Thanks. 

This time, I have a .txt file. (attached) -- wondering if yo uhave any suggestions as to how to convert this into a dataset. I will appreciate it. Thanks!

[Bren_Spill]

@prakashatutd - can you please mark your original question as resolved?

[LindonB · Accepted solution]

@prakashatutd, when you select you file, just indicate that it is a tab separated text file type. It is the same as putting \t in the delimiter on option 5 of the input tool. See below.