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

[hip2b2]

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

[Marcus Tettmar]

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]

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/

[hip2b2]

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

[Marcus Tettmar]

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

[hip2b2]

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]

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

[hip2b2]

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]

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 ?

[Marcus Tettmar]

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