{"id":853,"date":"2009-11-05T11:39:59","date_gmt":"2009-11-05T11:39:59","guid":{"rendered":"http:\/\/www.mjtnet.com\/blog\/?p=853"},"modified":"2009-11-05T15:21:35","modified_gmt":"2009-11-05T15:21:35","slug":"how-to-sort-a-csv-file","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2009\/11\/05\/how-to-sort-a-csv-file\/","title":{"rendered":"How to Sort a CSV File"},"content":{"rendered":"

This morning, forum user hip asked<\/a> how a script could sort a CSV file. Seems CSV files, sorting and Excel has become a bit of a hot topic here lately. Since Excel can read and save to CSV and also has a Sort function we might as well make use of it. Here’s a little VBScript function I put together which will sort a CSV file by a given column:<\/p>\n

VBSTART\r\nSub SortCSVFile(file,sort_column)\r\n  Set XLApp = CreateObject(\"Excel.Application\")\r\n  XLApp.Workbooks.Open(file)\r\n  \r\n  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear\r\n  Set SortCol = XLApp.Range(sort_column)\r\n  XLApp.ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(SortCol)\r\n  Set Cell = XLApp.ActiveWorkbook.ActiveSheet.Cells.Find(\"*\", XLApp.ActiveWorkbook.ActiveSheet.Cells(1, 1),,,,2)\r\n  Set mYRange = XLApp.Range(\"A1\", XLApp.ActiveWorkBook.ActiveSheet.Cells(Cell.Row, Cell.Column))\r\n    \r\n  XLApp.ActiveWorkbook.ActiveSheet.Sort.SetRange(mYRange)\r\n  XLApp.ActiveWorkbook.ActiveSheet.Sort.Apply\r\n  \r\n  XLApp.ActiveWorkBook.Save\r\n  XLApp.ActiveWorkBook.Close false\r\n  XLApp.quit\r\nEnd Sub\r\nVBEND<\/pre>\n
The function opens the CSV file in Excel – without making Excel visible – then performs the sort, saves the file and closes Excel.  You won’t see Excel appear on the screen.<\/p>\n
Here’s how to use it.  If you want to sort your file on column B and don’t have a header row, use:<\/p>\n
VBRun>SortCSVFile,C:\\Documents\\my.csv,B1<\/pre>\n
To sort on column A and you DO have a header row use:<\/p>\n
VBRun>SortCSVFile,C:\\Documents\\my.csv,A2<\/pre>\n
UPDATE: Here’s a simplified version which should work in Excel XP\/2003 and should be able to detect whether or not your data has a header row:<\/em><\/p>\n
VBSTART\r\nSub SortCSVFile(file,sort_column)\r\n  Set XLApp = CreateObject(\"Excel.Application\")\r\n  XLApp.Workbooks.Open(file) \r\n  XLApp.ActiveWorkbook.ActiveSheet.Range(\"A1\").Sort XLApp.ActiveWorkbook.ActiveSheet.Range(sort_column),,,,,,,0  \r\n  XLApp.ActiveWorkBook.Save\r\n  XLApp.ActiveWorkBook.Close false\r\n  XLApp.quit\r\nEnd Sub\r\nVBEND<\/pre>\n
What if you don’t have Microsoft Excel?<\/strong><\/p>\n
As mentioned in my reply on the forum<\/a> another method you could use is to open the CSV file as a recordset using DBQuery and apply an “ORDER BY” clause to your SQL statement.  That would sort it into memory.  Then you would need to loop through the array and write the data back out to a CSV file using WriteLn.   <\/p>\n
Something like:<\/p>\n
\/\/new file to write to\r\nLet>new_file=%SCRIPT_DIR%\\example_sorted.csv\r\nIfFileExists>new_file\r\n  DeleteFile>new_file\r\nEndif\r\n\r\n\/\/set WriteLn NOT to add line breaks (we'll add those ourselves)\r\nLet>WLN_NOCRLF=1\r\n\/\/The CSV delimiter\r\nLet>comma=,\r\n\r\n\/\/Connect to and query the CSV using ORDER BY PRICE to sort on Price column\r\nLet>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%;Extended Properties='text;HDR=YES;FMT=Delimited'\r\nDBConnect>ConStr,dbH\r\nDBQuery>dbH,select * from example.csv ORDER BY PRICE,fields,num_recs,num_fields\r\n\r\n\/\/If we have records, loop through records and fields, writing out to new file\r\nIf>num_recs>0\r\n  \/\/first need to copy the header record ...\r\n  ReadLn>%SCRIPT_DIR%\\example.csv,1,header_line\r\n  WriteLn>new_file,wlnres,%header_line%%CRLF%\r\n\r\n  \/\/the loop\r\n  Let>rec=0\r\n  Repeat>rec\r\n    Let>rec=rec+1\r\n    Let>field=0\r\n    Repeat>field\r\n      Let>field=field+1\r\n      Let>this_field=fields_%rec%_%field%\r\n      \/\/write the field out to the new file\r\n      WriteLn>new_file,wlnres,this_field\r\n      \/\/add the comma delimiter if needed\r\n      If>field<num_fields\r\n        WriteLn>new_file,wlnres,comma\r\n      Endif\r\n    Until>field=num_fields\r\n    \/\/add CRLF at end of line\r\n    WriteLn>new_file,wlnres,%CRLF%\r\n  Until>rec=num_recs\r\nEndif\r\nDBClose>dbH\r\n\r\n\/\/if you want, delete the input file and replace with the new sorted one:\r\n\/\/DeleteFile>%SCRIPT_DIR%\\example.csv\r\n\/\/RenameFile>%SCRIPT_DIR%\\example_sorted.csv,%SCRIPT_DIR%\\example.csv<\/pre>\n","protected":false},"excerpt":{"rendered":"
This morning, forum user hip asked how a script could sort a CSV file. Seems CSV files, sorting and Excel has become a bit of a hot topic here lately. Since Excel can read and save to CSV and also has a Sort function we might as well make use of it. Here’s a little […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/853"}],"collection":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/comments?post=853"}],"version-history":[{"count":8,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/853\/revisions"}],"predecessor-version":[{"id":862,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/853\/revisions\/862"}],"wp:attachment":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/media?parent=853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/categories?post=853"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/tags?post=853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}