String size limitations

Hello - any thoughts on how to get around the string size limitations? I'm attempting to push a 20 million row output through this SFTP solution and the summarize tool that concatenates all values to a single record (before data is transformed to blob format) is causing the error:

Designer x64 Tool #3: Tool #26: Error String size limit reached: Strings are limited to 2147483648 bytes

Basically, is there any way to have an unlimited string data type?

Thanks!

Workflow

Transformation

Accepted answers

KevinP

@-Daniel-

Alteryx has a single field string limit of 2GB or approximately 2.14 billion characters. This is a significantly large amount of data to be contained in a single field. From the error you are encountering it appears that your concatenated data is exceeding this size. Unfortunately, there isn't anything we can do to increase the size limit per field beyond this amount and when you consider that you could have an unlimited number of rows or columns with each field having this size as a maximum you can pretty readily reach obscene data sizes with the current limit.

You also may want to consider that transferring this amount of data via ftp can take a significant amount of time. Keeping this in mind and since your data exceeds 2GB in size by an unknown factor it would likely be better if you took a different approach to accomplishing your goal. Perhaps a better route would be to output the data to a csv file, zip that file, then upload the compressed file instead of uploading the raw string data. There are a number of ways you could do this. One method would be to output the file to CSV then use Alteryx to create a batch file to compress the file and upload it via ftp. You could then execute this batch file in the same workflow by using the Run Command tool. Another options would be to output the file and then read it back in as a blob (use the Blob Input tool) that you then pass to the download tool to upload via ftp/sftp. These are just two examples, and there are a large number of references on the community you could use as examples. In fact the following links might be especially of interest to you:

Download and extract zip files (this could be easily re-engineered to zip and upload instead)

Can Alteryx unzip a file as part of the workflow?

Community created macros to zip and/or SFTP files:

ZIP and SFTP - ToFile / ToFileToZIP / ToFileToSFTP / ToFileToZipToSFTP (macros attached)

All comments

KevinP

@-Daniel-

Download and extract zip files (this could be easily re-engineered to zip and upload instead)

Can Alteryx unzip a file as part of the workflow?

Community created macros to zip and/or SFTP files:

ZIP and SFTP - ToFile / ToFileToZIP / ToFileToSFTP / ToFileToZipToSFTP (macros attached)

varundcs

This is what is was looking for a long time .I wanted to know the limitation on the amount of data a single row or a column can accommodate . Thanks it helped me overall.I can go back to my users and ask them to look for other alternative approach.

Thanks

Varun

lrico

One solution is to replace the Dynamic input tool for a Connect In-DB tool then add a join tool.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335