Jump to content

How to sort alphabetically a CSV file?

asgarcymed
 Share

Recommended Posts

asgarcymed Wayfarer

asgarcymed

Posted
Posted

I have a CSV (Comma-Separated Values) file:

Name,Surname

Terry,Price

Chris,Jones

Andy,Smit

David,Cole

And I want to sort it alphabetically (would be):

Name,Surname

Andy,Smith

Chris,Jones

David,Cole

Terry,Price

A CSV file is a text file; the delimiter is a comma; each line has one record.

Please help!

Thanks in advance.

Best regards.

MLMK - my blogging craziness...
Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted

_FileReadToArray

_ArraySort

_FileWriteFromArray

?

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted (edited)

I did:

_FileReadToArray

_ArraySort

_FileWriteFromArray

But I found a problem - the new CSV file has only one line (all original lines are "joined")... How to put a Chr(13) to separate each line?

What is the new delimiter between records in the file? If for example they put a pipe "|" for the delimiter, then you could use:

$sString = FileRead($File)

$avArray = StringSplit($sString, "|")

Then do the _ArraySort and _FileWriteFromArray as before.

:)

Edit: Actually, if you want to preserve their formatting, it would be:

$sString = _ArrayToString($avArray, "|", 1)

$hFile = FileOpen($File, 2) ; Open for overwrite

FileWrite($hFile, $sString)

FileClose($hFile)

;)

Edited by PsaltyDS
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
asgarcymed Wayfarer

asgarcymed

Posted
  • Author
Posted

The original CSV file (names1.csv) is:

Name,Surname

Terry,Price

Chris,Jones

Andy,Smit

David,Cole

The code I used is:

Dim $csv_to_array
    _FileReadToArray (@DesktopDir & "\names1.csv", $csv_to_array)
    _ArraySort ($csv_to_array)
    _FileWriteFromArray (@DesktopDir & "\names2.csv", $csv_to_array, 1)

I found a problem - the new CSV file has only one line (all original lines are "joined")...

What I want is names2.csv (the new, sorted file) to be:

Name,Surname

Andy,Smith

Chris,Jones

David,Cole

Terry,Price

MLMK - my blogging craziness...
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

I found a problem - the new CSV file has only one line (all original lines are "joined")... and I'm ignoring PsaltyDS, so I can't tell you what the new delimiter is between records...

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted

If I do:

$sString = FileRead($File)
$avArray = StringSplit($sString, "|")
$sString = _ArrayToString($avArray, "|", 1)
$hFile = FileOpen($File, 2) ; Open for overwrite
FileWrite($hFile, $sString)
FileClose($hFile)

The CSV file becomes empty...

Here's a suggestion.

1. Upload the csv file, or one identically delimited so we can see.

2. Show (I think you have) the exact output you want from the file after it's sorted.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted (edited)

Here they are - the Original.csv and the Wanted.csv

I thought you said the original was all one line? I'm so confused... What I said earlier would work for what you provided...

Anyway (I know there's going to be an issue somewhere I'm sure), but with what you had... this worked fine (which is what I was basically suggesting, just wrote the funcs out myself other than _ArraySort)

#include <array.au3>

_FileSortLineArray("Original.csv", "wanted.csv", 0, 2, @CRLF);2 is for starting sort on 2nd line so we don't get the header

Func _FileSortLineArray($hFileIn, $hFileOut, $nDescending = 0, $nStartSort = 1, $vDelim = "|")
    Local $sReadFile = FileRead($hFileIn)
    Local $avArray = StringSplit(StringStripCR($sReadFile), @LF)
    _ArraySort($avArray, $nDescending, $nStartSort)
    Local $sWrite
    For $iCC = 1 To $avArray[0]
        $sWrite &= $avArray[$iCC] & $vDelim
    Next
    FileClose(FileOpen($hFileOut, 2))
    Return FileWrite($hFileOut, StringTrimRight($sWrite, StringLen($vDelim)))
EndFunc
Edited by SmOke_N

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted

That works! But if I try another CSV file it does not work... How to sort this new CSV file (attached)?

Well... want to give me the new one... or am I to guess of it's format?

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted

Here they are - nomes_original.csv and nomes_wanted.csv

Maybe I'm missing the obvious :) ... Like where the file is to download?

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted (edited)

I have posted a new "Files.zip" file, containing "nomes_original.csv" and "nomes_wanted.csv"

Ahh, sorry... had to scarf down some pizza bytes, and didn't notice you posted 1 minute before I did....
#include <array.au3>
_CSVSortLineArray("Original.csv", "\wanted.csv", 0, 2, Chr(13));2 is for starting sort on 2nd line so we don't get the header
Func _CSVSortLineArray($hFileIn, $hFileOut, $nDescending = 0, $nStartSort = 1, $vDelimSort = ",", $vDelimOut = @CRLF)
    Local $sReadFile = FileRead($hFileIn)
    If StringRight($sReadFile, StringLen($vDelimSort)) = $vDelimSort Then _
        $sReadFile = StringTrimRight($sReadFile, StringLen($vDelimSort))
    Local $avArray = StringSplit($sReadFile, $vDelimSort, 1)
    _ArraySort($avArray, $nDescending, $nStartSort)
    Local $sWrite
    For $iCC = 1 To $avArray[0]
        $sWrite &= $avArray[$iCC] & $vDelimOut
    Next
    FileClose(FileOpen($hFileOut, 2))
    Return FileWrite($hFileOut, StringTrimRight($sWrite, StringLen($vDelimOut)))
EndFunc
Would have taken you about 60 seconds to change the function up a bit.

Edit:

Had to put in #include <array.au3> for _ArraySort

Also note, that blank strings will be first in line... I'm sure you can adjust around that.

Edit2:

The blank string was because there was a delimiter on the end of the file (Poor inserting on whatever created it for not checking).

I put an If/Then statement to remove it if it's there so you won't get a blank line because of it.

Edit3:

Fixed a spelling mistake in the code (Obviously I didn't run it before I posted it) :)

Edited by SmOke_N

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...