How to split CSV into multiple files

Easy Data Transform provides 2 different ways to split a CSV file into multiple files (of CSV or other formats).

• The simple approach is ideal when you are splitting into a small number of files and you know, in advance, how many files.
• The advanced approach is better when you are splitting into a large number of files, or the number of files output may depend on the input dataset.

Simple example

In this example we have a CSV file with a Gender column that identifies the person in each row as M or F.

We want to split this into a CSV file with all the M rows and another CSV file with the all F rows.

• Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
• Start Easy Data Transform. Make sure the Auto Run button is pressed in.

• Drag the CSV file you wish to split onto Easy Data Transform. An input item will be added to the Center pane. In the Right pane the data will be displayed as a table.

• Ensuring the input item is selected, click on the Filter button in the Left pane. In the Right pane create a filter to keep only the rows with an M value. The data table will be updated to show only Gender=M rows.

• With the Filter transform item selected in the Center pane, scroll to the bottom of the Left pane and click To File. A file location window will appear. Set the new file name, location and file type.

• An output item is added and a new file is created containing only the filtered rows. No need to ‘run’ anything.
• Select the input item again in the Center pane, click Filter and create a second filter to keep only the rows with an F value. Then create a second output file, as above, for the Gender=F rows.

• The input file is now split into 2 separate output files.
• You can now File>Save the transform template file to use again.

Advanced example 1

In this example we have a CSV file with a column that shows the Department of each person.

We want to create a separate output file for each department.

• Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
• Start Easy Data Transform. Make sure the Auto Run button is pressed in.

• Drag the CSV file you wish to split onto Easy Data Transform. An input item will be added to the Center pane. In the Right pane the data will be displayed as a table.

• Now we need to create a new column with the output file each row should be written to. Make sure the show advanced checkbox is checked in the Left pane, so that the Substitute button is shown.

• Ensuring the input item is selected, scroll down the Left pane and click Substitute. Fill in the Right pane as shown and click Evaluate. A new Filename column is added.

• With the Substitute item selected in the Center pane, scroll to the bottom of the Left pane and click To File. A file location window will appear. Set a file name, location and file type.

• As we haven’t used an absolute file location in the Filename column, we need to save the transform template file, so it knows where to put the output files. Select File>Save from the main menu and choose a sensible file name and folder.

• With the output item selected in the Center pane, in the Right pane change Output to to Multiple files and change Files column to Filename. Note that the Filename column is not output (use the Copy Cols transform if you want it to be).

• You will be prompted before the files are created. Click OK to accept.

• File>Save the transform template file to use again.

Advanced example 2

If you want to split a large CSV file into multiple 1000 row Excel files you can do that similarly to Advanced example 1 (above), but using the Row Num transform.

Use a Remove Cols transform before the output if you don’t want to output the Row Num column.

If you have lots of files to convert you can do it using the batch processing feature.

As well as changing file format, Easy Data Transform also allows you to combine 66 transforms (such as join, stack and dedupe) to quickly create complex data transformations.