CSV File too big, need to split it into smaller ones-CodePudding

I´m having trouble working with a big CSV File (4.000.000 rows aprox) and i´m looking for a way to divided such file into smaller ones.

Thank you for any ideas you could provide.

CodePudding user response：

You didn't mention which operating system you're using. In UNIX/Linux systems, there's the split command, you can use it as follows to split a file into chunks of two lines:

Prompt> split -l 2 file.txt testfile
Prompt> ls -ltra
...
-rwxrwxrwx 1 user user   64 Dec 22 15:47 testfileaa
-rwxrwxrwx 1 user user   64 Dec 22 15:47 testfileab
-rwxrwxrwx 1 user user    1 Dec 22 15:47 testfileac
...

In case you're working with Windows, you might install a WSL app (Windows Subsystem for Linux), which installs a Linux-like program on your computer, giving you access to all the wonders of Linux commandline :-)

CodePudding user response：

There are many other ways to work with similarly sized CSV files.

Following assumes that you must use Excel to work with this file.

Use Data>Get & Transform Data>From Text/CSV

After you have selected the file, select 'Transform Data' to open the query in the Power Query editor.

From here, you can either simply use Home>Close & Load To> and configure like this:

After this you will be able to manipulate the data in the Power Pivot Data Model and use Pivot Tables and Pivot Charts to analyse the data.

Alternatively, if you must load the data to the worksheet, you can filter the data to a manageable size in the Power Query editor before using Home>Close & Load and selecting 'Table'.

If you really must actually split the data, you can create separate queries that reference the main query, each of which has a filter on some column.

In the power query editor, right-click the query you created on the file, and select 'Reference'.

This will create a new query you can filter and load as described above.

You can repeat this as many times as you want.