Script to sort csv files - way too hard for my little brain

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
hip2b2
Newbie
Posts: 9
Joined: Thu Nov 05, 2009 4:41 am

Script to sort csv files - way too hard for my little brain

Post by hip2b2 » Thu Nov 05, 2009 5:10 am

First I do not see a sort command on the macro editor, but even if I did I suspect this problem would be too complex for me (and my little brain). I also have not found any examples in the forum.

I am looking to sort various csv files that are similar but different. By different I mean that some files are comprised of 1 field per record, other files have 2 fields, etc. Data in the fields can be either alpha, or numeric, though the types are not mixed in any one field.

I would like to be able to sort my files based on selected fields, say field 1 as primary, field 3 as secondary, and field 2 as tertiary.

Any suggestions?

Thanks

hip

User avatar
Marcus Tettmar
Site Admin
Posts: 7255
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Post by Marcus Tettmar » Thu Nov 05, 2009 9:57 am

You could:

1. Use DBQuery:
http://www.mjtnet.com/blog/2009/03/12/r ... csv-files/

Modify the SQL to use an ORDER BY clause and then loop through the recordset and output the data back to a CSV file using WriteLn.

This would require having a header record and using HDR=YES in the connection string (otherwise you have nothing to identify the sort column)

2. Use Excel. If you have Excel installed then this is probably easier. Here's a VBScript function which will let you sort a CSV file by a specific column:

Code: Select all

VBSTART
Sub SortCSVFile(file,sort_column)
  Set XLApp = CreateObject("Excel.Application")
  XLApp.Workbooks.Open(file)
  
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
  Set SortCol = XLApp.Range(sort_column)
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol)
  Set Cell = XLApp.ActiveWorkbook.ActiveSheet.Cells.Find("*", XLApp.ActiveWorkbook.ActiveSheet.Cells(1, 1),,,,2)
  Set mYRange = XLApp.Range("A1", XLApp.ActiveWorkBook.ActiveSheet.Cells(Cell.Row, Cell.Column))
    
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SetRange(mYRange)
  XLApp.ActiveWorkbook.ActiveSheet.Sort.Apply
  
  XLApp.ActiveWorkBook.Save
  XLApp.ActiveWorkBook.Close false
  XLApp.quit
End Sub
VBEND
The function opens the CSV file into Excel, sorts it, then saves and closes it. All invisibly in the background.

Call it with, for e.g.:

Code: Select all

//Sort on column B if no header row:
VBRun>SortCSVFile,C:\Users\User\Documents\csv\my.csv,B1

//Sort on column A and I have a header row (so start sort on 2nd row)
VBRun>SortCSVFile,C:\Users\User\Documents\csv\my.csv,A2
So if you have a header row and want to sort on column B pass in B2 for the SortCol. No header row it would be B1.

You say you want to sort on additional columns. Just add more of these lines:

Code: Select all

  Set SortCol = XLApp.Range(sort_column)
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol)
E.g. to set the sort keys to columns A and C you could do:

Code: Select all

  Set SortCol1 = XLApp.Range("A1")
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol1)

  Set SortCol2 = XLApp.Range("C1")
  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol2)
(Make it A2 and C2 if you have a header row).

Hope this helps.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
Marcus Tettmar
Site Admin
Posts: 7255
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Post by Marcus Tettmar » Thu Nov 05, 2009 11:41 am

I've written this up here along with an example using the DBQuery ORDER BY and WriteLn method (which would work if you don't have Excel):

http://www.mjtnet.com/blog/2009/11/05/h ... -csv-file/
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

hip2b2
Newbie
Posts: 9
Joined: Thu Nov 05, 2009 4:41 am

Post by hip2b2 » Thu Nov 05, 2009 2:17 pm

I've tried the excel example but get an error:
MS VBScript runtime error:438
Object doesn't support this property or method: 'XLApp.ActiveWorkbook.ActiveSheet.Sort'
Line 6, Col 0

Is it me? Is there an Excel 2k3 switch that needs to be set?

One other thing, Will I be able to sort on multiple columns as Primary, Secondary, etc? using this technique?

I haven't tried the other code example as at this time I don't really understand it.

Thanks for the help.

hip

User avatar
Marcus Tettmar
Site Admin
Posts: 7255
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Post by Marcus Tettmar » Thu Nov 05, 2009 2:51 pm

I only have Excel 2007 so can't test but I went back to the drawing board and made a simplified version. Try this:

Code: Select all

VBSTART
Sub SortCSVFile(file,sort_column)
  Set XLApp = CreateObject("Excel.Application")
  XLApp.Workbooks.Open(file)
 
  XLApp.ActiveWorkbook.ActiveSheet.Range("A1").Sort XLApp.ActiveWorkbook.ActiveSheet.Range(sort_column),,,,,,,0
  
  XLApp.ActiveWorkBook.Save
  XLApp.ActiveWorkBook.Close false
  XLApp.quit
End Sub
VBEND

VBRun>SortCSVFile,c:\my.csv,A1
It should also guess whether there is a header or not, so just give the top cell of the column you want to sort on.

If you want to sort by additional columns you can add up to more "keys" in the Sort method call. See:
http://msdn.microsoft.com/en-us/library ... e.11).aspx
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

hip2b2
Newbie
Posts: 9
Joined: Thu Nov 05, 2009 4:41 am

Post by hip2b2 » Thu Nov 05, 2009 8:31 pm

mtettmar,

Your code works perfectly with one Excel problem; Excel refuses to completely exit at the completion of the routine. This is evident iIf I run the script "n" times there are "n" Excel.exe processes running in the XP Process Manager.

This seems to be an Excel problem, and does not have anything to do with the script you wrote.

I will investigate and get back to the forum with a solution if/when I find one. Of course, if anyone knows something I don't....

Regards

hip

Me_again
Automation Wizard
Posts: 1101
Joined: Fri Jan 07, 2005 5:55 pm
Location: Somewhere else on the planet

Post by Me_again » Fri Nov 06, 2009 12:59 am

Opening and resaving a .csv file in Excel will lose its numeric formatting, so it's not a great idea if you value leading or trailing zeros :wink:

hip2b2
Newbie
Posts: 9
Joined: Thu Nov 05, 2009 4:41 am

Post by hip2b2 » Fri Nov 06, 2009 1:40 am

sigh....

OK, then I expect that I will have to explore a variation of the DBQuery script that mtettmar suggested; unless someone has another direction to suggest.

Wonderful ideas/support.

Thanks

hip

conjure
Pro Scripter
Posts: 63
Joined: Thu Jan 12, 2012 3:05 pm

Re:

Post by conjure » Sun Sep 13, 2020 7:48 am

Marcus Tettmar wrote:
Thu Nov 05, 2009 9:57 am
VBSTART
Sub SortCSVFile(file,sort_column)
Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open(file)

XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
Set SortCol = XLApp.Range(sort_column)
XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol)
Set Cell = XLApp.ActiveWorkbook.ActiveSheet.Cells.Find("*", XLApp.ActiveWorkbook.ActiveSheet.Cells(1, 1),,,,2)
Set mYRange = XLApp.Range("A1", XLApp.ActiveWorkBook.ActiveSheet.Cells(Cell.Row, Cell.Column))

XLApp.ActiveWorkbook.ActiveSheet.Sort.SetRange(mYRange)
XLApp.ActiveWorkbook.ActiveSheet.Sort.Apply

XLApp.ActiveWorkBook.Save
XLApp.ActiveWorkBook.Close false
XLApp.quit
End Sub
VBEND

VBRun>SortCSVFile,C:\Users\PC6\Desktop\Sorted.txt,A1
Hi Marcus.
You saved me with your code.
Is it possible to sort in descending order ?

User avatar
Marcus Tettmar
Site Admin
Posts: 7255
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Script to sort csv files - way too hard for my little brain

Post by Marcus Tettmar » Tue Sep 15, 2020 7:22 am

Yes. Looks like it:
https://docs.microsoft.com/en-us/office ... fields.add

But it is probably going to be much easier to record this in Excel and then copy over the code, and/or use XLRunCode as then you won't have to translate it to VBScript.

Looks like there's another way to sort using a range object first where you an apply the sort order directly:
https://docs.microsoft.com/en-us/office ... parameters
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts