Editing data is necessary in the process of consuming data. From cleaning your data to calculating new values from it, editing data is a crucial step to analysis. CSV files are a great way to store data records. It’s an easy format to import and export to spreadsheets and databases, making it an accessible way to store data.

If you have a big CSV file larger than a few hundred thousand rows, it’d be pretty hard to access it in a spreadsheet program, let alone bulk edit its value. The reason behind this is because spreadsheets do not store large amounts of values well. Being stored in a grid of individual cells, these data records can take a lot of computational power to edit.

If your data is split over several CSV files, bulk editing is even more difficult. How can you merge your files without it exceeding spreadsheet row limits and how can you edit them all together?

1. Use a SQL database

SQL databases, such as MySQL or SQL Server, can organize a collection of relational data tables. Once your data is loaded into a SQL database, you can edit millions of rows across multiple columns and tables at a time. However, parsing your files into a SQL database can be painful, since CSVs come in all shapes and sizes and they’re not necessarily clean and structured. For more information, check out this article on how to open a large CSV file.

EditingSQL databases also requires knowledge on coding in SQL and database management, which can be a barrier to those who do not have this experience. For example, suppose you want to update some customers’ category in a customer table if their total sales exceed 10 thousand dollars. You can write the following SQL to achieve this:

In this case, you have to define the relationship between two tables via the JOIN syntax and then update the customer table through the UPDATE syntax. To achieve this, you need a deeper understanding of the structure of your data and get familiar with various SQL syntax.

2. Use a command prompt

Command-line, such as in your Terminal, can read your CSV file as a relational table. Being a Unix program, it’s a powerful and flexible way to process your files. However, as you can see in the image below, its lack of a GUI interface makes it hard to navigate through your table and files. You would also need to write scripts to edit your data from the command line which can get tricky if you’re not familiar already.

3. Use a cloud database platform

Another more accessible way to bulk edit CSV files would be to use a cloud database platform, such as Acho. One of the biggest strengths of a cloud database is that it can process large amounts of data at a faster speed than one computer. Another advantage is that it will help you parse and clean the CSV data easier than you writing code.

In this article, we’ll use Acho as an example to demonstrate how to bulk edit your CSV files.

Upload and preview the file

When you add a flat file resource, you can choose to upload either a single file or batch files altogether. Single files should be used if you have only one file or if your files have different columns. If all of your files have the same set of columns, use the batch files option and the system will combine the files for you.

Before you complete uploading, you’ll get a preview of the table. When previewing, you may run into issues because of the file. Check out this documentation if you do. The “Safe Mode” is designed to solve most of the problems. If none works, contact the team and they should parse the file for you.

When uploading batch files, Acho’s file management system allows you to add and remove files from the batch. If you’d like to include another file in your batch files, you can head to file management and hit “Add Files”. As long as your new file(s) have the same set of columns as the existing batch, they can be combined. There is also an option to delete files that have been previously included.

Bulk edit a table

When you have the table loaded in a project, you will have a variety of no-code tools to edit your files including filter, sort, pivot table, and cleanse. You can apply them to your table regardless of its sources or sizes.

If there's more advanced manipulation you want to do, you can either use the SQL editor or ask the team for help.

Exporting your table

Once your table is ready, you can use it to build a dashboard in Acho’s business apps, or you can export your table. You can download it as a file, send it to Google Sheets, or publish it to a variety of BI tools such as Tableau or PowerBI among many options.

If you’re proficient with SQL or Python, bulk editing your CSV files  might not be a big issue. However, even bulk editing CSV files via scripting can be prone to errors and time-consuming. Using a cloud database platform can alleviate the trouble of having to write your own code. If you have files that are harder to access or manipulate, feel free to chat with team Acho or email contact@acho.io