Sorting a CSV

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

ebam311

Sorting a CSV

Postby ebam311 » Wed Mar 02, 2005 12:26 pm

I am trying to merge 2 CSV files together and then sort the data by several of the fields.

The fields are either all alpha or all numeric. The numeric, however, is not decimal. I noticed in the help menu, it says for sorting the number must be in decimal format. How do I get around that?

I created a filter that doesn't appear to do anything, but merge the files together.

Any help would be appreciated.

Thank You.

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Thu Mar 03, 2005 9:23 am

The easiest approach is to copy the fields you want to sort on to the beginning of the line, and then sort on that.

What format is the number in?
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

ebam311

Postby ebam311 » Thu Mar 03, 2005 1:03 pm

The numbers are integers, account numbers really. Maybe that's the same as decimal.... : )

I have 2 files, one has STM in the first field the other has CFN in the first field.

The third field has the account number. I want to first sort by the account number to group all lines together, then sort by the first field to separate the STM lines from the CFN lines. I can put a line counter on the input files, which I would sort by to keep the transactions within STM and CFN, respectively, in the same order as the input.

Below is an example of the first part of a merged file the I sorted using excel. It worked, but I need to automate this task and excel has problems getting the data back to me in a usable format.

STM,REC01,10012,10000000001,
STM,REC04,10012,10000000001,
STM,REC09,10012,10000000001,
STM,REC01,10056,10000000001,
STM,REC04,10056,10000000001,
STM,REC04,10056,10000000001,
STM,REC04,10056,10000000001,
STM,REC04,10056,10000000001,
STM,REC09,10056,10000000001,
CFN,REC01,10056, 107198,
CFN,REC02,10056, 107198,
CFN,REC02,10056, 107198,
CFN,REC02,10056, 107198,
CFN,REC02,10056, 107198,
CFN,REC02,10056, 107198,

I hope this clarifies it a bit more. I just don't really understand TextPipes sorting feature that well, because it doesn't appear to do anything when I use it in conjuction with the Restricted fields filter.

Any help is greatly appreciated.

Thank You.

ebam311

Postby ebam311 » Thu Mar 03, 2005 1:06 pm

Also, on the sorting filter, when I use mutliple sorts it appears to do one at a time, utlimately sorting by the last sort filter only. I guess I need to understand how to nest multiple sorts together.

Thanks again.

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Thu Mar 03, 2005 2:06 pm

TextPipe doesn't do multiple sorts, and sorts don't work with restrictions (although this would be nice).

You need to copy your field3 to the start of the line, then perform a sort on columns 1 to 7 (you only need the first letter of 'S' or 'C'. This is also known as a compound key.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “TextPipe Tips and Tricks, Questions and Support”

Who is online

Users browsing this forum: No registered users and 2 guests