Merging CSV Files Fast Using DOS COPY Command (or any type of file)

Why not PowerShell?

PowerShell might be the new and recommended way to do everything in the Windows command-line (and I am a hard-core PowerShell fan) but this is so easy and handy that it will probably even work in Windows 3.11 (Windows for workgroups)! Besides, I doubt that any current method in PowerShell is as fast as this. It might be a good idea to create a PowerShell wrapper on top of this and swap it out if there is something promising in the future.

The traditional “>>” vs. “COPY” to merge files

This is such a handy tip when working with large .csv files (in the Windows environment) that may need to be merged together either before loading to (or) after extracting out as pieces from a database.

As a long time DOS user, I had always been using “>>” (which worked in LINUX too)

For example, If I had the following files

  • Extract_Header.csv
  • Extract_Piece_001.csv
  • Extract_Piece_002.csv
  • Extract_Piece_003.csv

..

To merge them into a single file named FinalExtract.csv, I could do this in the DOS CMD prompt

COPY Extract*.* FinalExtract.csv

Which is in the format

COPY [qualifying_source_files] [target_file]

This has a few advantages over the other methods of merging

  • It is very fast
  • They syntax is so much simpler even to merge a lot of files

Variations

You can also do the following variations to combine several files into one. They don’d much explanation.

Variation 1:

COPY csvHeader
        +Extarct_Piece_001.csv
        +Extarct_Piece_002.csv
        +Extarct_Piece_003.csv
     FinalExtract.csv

Variation 2:

COPY
   csvHeader+Extarct_*.csv
   FinalExtract.csv

Caution:

Typically, the first line in extract files you are trying to merge will have the column header. Using this method will include that line within the records and might mess-up your upload to the database.  Remember to remove the header line before merging (or) better yet, do not export the header when exporting and keep the header line in a separate file as shown in the example above.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s