Sorting a CSV

ebam311 · Post by **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.

Post by **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?

ebam311 · Post by **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 · Post by **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.

Post by **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.