Download CSV and Convert to Table

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.

Datasets

Help

Download

Accepted answers

Luke_C

Hi @Link86

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

New Workflow4.yxmd

All comments

Luke_C

Hi @Link86

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

New Workflow4.yxmd

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.

covid va cleaned.yxmd

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,

mceleavey_solution.yxzp

mceleavey_solution.yxmd

Import Multiple Schema CSV Files.yxmc

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703