Use of Large CSV Files with Data Input Tool

Hi @mkeiffer ,

I am not sure why you cannot read all the data,

but to select only the first 10 columns,

you may want to try R tool or Python tool to read it, instead of Input Data tool.

I used R tool as a sample.

Code in R tool

Input Data

Output Data

It should be possible to do the same with Python tool if you prefer.

Good luck.

testR.yxmd

ran in about 15 seconds for me - make sure that you have delimiter set to \0 in your input data. also - looks like your first row contains data - not headers - so make sure "first row contains field names" isn't selected.

All comments

Qiu

@mkeiffer
It is an interesting issue.
I guess maybe it is not possible to have some sample data?

I dont know how to limit in the Input, but maybe we can set the delimiter to something else and bring the 6,000 fields as one column?

@Qiu I have tried changing the delimiter and the length of each field to see if that would help, but no success. Everything is slow and Alteryx locks up.

I was attempting to use Alteryx to do some linking and data blending with these files, and this is one of about 15 files or so that I am working on with this case study. I may have to use Python to try to deal with the large number of fields in the file, but I was curious to see if I could do it in Alteryx.

Thanks so much for responding!

Hi @mkeiffer ,

I am not sure why you cannot read all the data,

but to select only the first 10 columns,

you may want to try R tool or Python tool to read it, instead of Input Data tool.

I used R tool as a sample.

Code in R tool

Input Data

Output Data

It should be possible to do the same with Python tool if you prefer.

Good luck.

testR.yxmd

@Yoshiro_Fujimori Thank you so much for sharing this. This was extremely helpful. Hope you have a great day today!

@Yoshiro_Fujimori I did change the path when using the attached workflow. However, I got an error message when running the workflow and it said "Error in library(readr). This is no package called readr." I have installed the R predictive analytics with my version of Alteryx but I do not have R installed on my computer (I do have Python).

Do I need to install the latest version of R on my computer in order to get the R tool in Alteryx to work correctly?

Again, thank so much in advance for your help!

https://community.alteryx.com/t5/Community-Gallery/Install-R-Packages/ta-p/878756

Hi @mkeiffer ,

Please try to install the missing library like this:

if (!require(readr)) {
install.packages("readr")
stopifnot(require(readr))
}
library(dplyr)
df_sample <- read_csv("C:\\Users\\yoshi\\OneDrive\\ドキュメント\\1.Project\\202304\\Sample.csv")
df_sample <- select(df_sample, c(1:10))
write.Alteryx(df_sample, 1)

If it still doesn't help, please consider installing the missing library using this tool on the Community Gallery.

I am not sure if it works as I have not used it. Good luck.

@Yoshiro_Fujimori Thanks again! I tried both approaches, adjusting the Alteryx configuration to install the readr package and I have also run the Alteryx tool to install the R packages several times, but no luck. Is it possible the readr package is not included with the Alteryx downloads? Should I try installing R on my machine and then adding the readr package that way?

Also, is it possible to see specifically what packages the Alteryx tool/workflow has available to install?

Again, thanks so much for your help!

Mike

Hi @mkeiffer ,

Sorry to hear that my proposed solution didn't work.

The R package for Alteryx is installed here.

"C:\Program Files\Alteryx\R-4.1.3\"

You may want to check README.md in this folder and follow the procedure of

# A Recipe For Upgrading to a New R Version

Or if you are more familiar with Python, you may try Python tool to read the csv file.

I don't think the R/Python ways are really getting at the core issue here. The core issue is that your rows are 60,000 characters long - but you only want the first 1000 characters or whatever. I'd recommend bringing this in as non-delimited ((\0 delimiter) - expanding the size of your data to 250000 characters - and then truncating your data post read (you could use a left([field1],1000) to take only the first 1000 characters in a particular row. You can then parse the fields as you want. You could actually do this in your initial input data (and set your field length as 1000 characters) and just ignore the resulting truncated fields warning.

Having said that - you could be having a readr issue because you are running a different version of Alteryx (and different R) than @Yoshiro_Fujimori 's solution...

@apathetichell Thank you so much for your response and I will try this approach as well. In regards to the version of Alteryx, I am using the latest version of Alteryx and the latest version of the R package tools. I would like to see if I can get the R approach to work that was shared by @Yoshiro_Fujimori as I like this approach too. I also would like to make sure I have the full functionality of all of the R tools in Alteryx as well.

Thanks so much for responding!

@Yoshiro_Fujimori I do apologize for all of the posts. I was able to get my workflow to work in Alteryx by changing the "read.csv" portion of the configuration to "read.tsv" and that did the trick.

The only thing I need to fix now is the data is coming in with the first record as the column names/titles so now I just have to configure the R statement to not include the 1st row as the column titles. Any suggestions on how to change this would be most welcome.

Again, thanks so much for all of your help!

Mike

@Yoshiro_Fujimori Again so sorry for all of the posts, but I was able to get the R tool to successfully read my data files. Here is how I modified my configuration to read the file with no column titles:

library(readr)
library(dplyr)
df_sample <- read_tsv("C:\\Alteryx Workflows\\Data Club Project\\Source Data Original\\PS1.txt", col_names = FALSE)
df_sample <- select(df_sample, c(1:10))
write.Alteryx(df_sample, 1)

Thank you again so much for your help!

Mike

I know it sounds great "WE USED PYTHON TO SOLVE IT!" but you could try something like the attached and just dump whatever you don't need using a text to columns tool.

no python till brooklyn.yxmd

@apathetichell I will try this approach and see how it works. What I wanted to do, in order to speed up the time it takes to run the workflow, was to not even bring in all of the columns. The 6,000 plus fields was causing the workflow to run slowly and also it was causing Alteryx to freeze up.

Thanks for responding to the post!

You don't bring in all the columns in my approach. Alteryx stops reading at X characters in the input data. You process it as pure text. You don't create columns until the text to column tool. I use tab as the delimiter but it could be anything.

@apathetichell I did try your approach and workflow, but I am still getting the same issues with slow speed and Alteryx freezing up.

The test file I am using is a text file that is delimited with tabs and I could not see in the Input Tool in your workflow where the data that was being brought in was limited.

I was able to successfully modify the configuration in the solution that does use the Python tool so I was able to get my desired output using both the R Tool and the Python Tool.

I am posing a copy of the file I am using, if you would like to try to use this file with your workflow. Not sure I am configuring the tool wrong, but it does not work with this file.

Thanks again so much for your help!

PS3.txt