Semicolon delimited CSV file
I have a set of large semicolon delimited CSV files (15-50MB) that I would like to use with our CSV actions, but it doesn't appear that the actions recognize semicolon delimited files.
I tried the Tables: Convert text to data table action, but that will only take a text field (not a file). I can't save it to a text field, because it would be too large.
Any suggestions?
Answers
-
@Jeff_291769 Since we handle comma-delimited files currently with the CSV actions, what about this:
In a starter workflow:
CSV: Apply formulas to columns
I would use RegEx to temporarily replace any commas present in your data (should be treated as a single column if semicolon-delimited) with some other character.CSV: Add or remove columns
You could use this to add a Grouping column to split the file.CSV: Apply formulas to columns
Use the below formula where X is equal to however many rows we can safely handle within our field limits:columns['Grouping'] = Math.ceil(rowIndex / X)
CSV: Create spreadsheet for each unique value
Use this to create a separate .csv file for each Grouping.Excel: Start workflow for each row
Create an inline workflow to manipulate each one of those .csv files.In an inline child workflow:
CSV: Add or remove columns
You need to now remove the Grouping column.File: Rename a file
Change the extension to .txt if need be.Files: Convert file to text
This will save the semicolon-delimited data to a text field that we can manipulate.Fields: Field formulas or Text: Find and replace
Replace all semicolons present in the text with commas.Text: Create text file
Turn your now comma-delimited text back into a .csv file.Fields: Field formulas
In addition to a data retention policy, the text field resulting from the above Fields: Convert file to text should be cleared using single name space:result = ''
Back in your starter workflow, you can leverage your Successful Runs Data Table ID field.
Tables: Add a column
We need a place to insert a Row Index column in the Successful Runs Data Table ID table field.Tables: Apply formulas to columns
Use the below formula to insert the row index:columns['Row Index'] = rowIndex
After that, I would build a loop using the below actions to consolidate these files back together:
Tables: Look up data in a column
To get the file ID for row 1.Numbers: Perform basic math
Set a value of a counter field (to be paired with the Row Index) to 2.Tables: Look up data in a column
To get the file ID for 2 to start, but increasing in increments by 1.Excel: Copy data from one spreadsheet to another
Add the data from the second, third, etc .csv files to the first.Numbers: Perform basic math
Add +1 to your counter field.Workflow: Reopen task and reset dependent tasks
Loop back to the Tables: Look up data in a column step above; add a condition to only start if the counter value is less than or equal to the successful runs from the Excel: Start workflow for each row step.0 -
@Thomas_937381 These techniques might work for smaller files, but due to the large size of these files, we'll run into field size limits.
0