Download CSV and Convert to Table

[Link86]

Hi,

I am trying to download the data from a link that starts and automatic download for the VA COVID Data by Locality: https://data.virginia.gov/api/views/bre9-aqqr/rows.csv?accessType=DOWNLOAD.

I am able to download this data to a file no problem with the download tool. However, where I am struggling is getting the data into a table format without the need to save to a file and then import that file. When using the text to column tool, it doesn't come out at all. I can also do this as an XML from https://data.virginia.gov/api/views/bre9-aqqr/rows.xml?accessType=DOWNLOAD but I am not sure how to work with XML files in Alteryx. My desired output is to have a nice table that I can perform further analysis on.

Any advice here would be helpful.

[Luke_C · Accepted solution]

Hi @Link86 

See attached. The key is to first split the downloaded data to rows, then to columns. 

[apathetichell]

Text to columns - delimiter is /n (split to rows)

second text to columns - use , change splits to 7.

Once you split to rows it begins to look normal - and you can go about the rest of the steps - attached is an example workflow which after the second text to column renames some fields, drops some fields and converts some fields from text to number and then Alteryx format dates.

[mceleavey]

Hi @Link86 ,

I've attached the workflow which automatically loads in the downloaded file:

I've attached the bespoke tool I built in case you are running an older version and can't install the exported package (the .yxzp file).

Hope this helps,

M

[dfurlow]

This is a useful answer and the workflow in the next comment includes \n as a delimiter, rather than /n as shown here.  The \n in the workflow works great!  Thank you for sharing this insight and explanation!  It helped me to solve this same challenge...

[apathetichell]

correct - I made a typo. \n - not /n.