Semicolon delimited CSV file

Jeff_146001

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?

Thomas_937381

@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.

Jeff_146001

@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.