Sign in to follow this  
Followers 0
asgarcymed

How to sort alphabetically a CSV file?

23 posts in this topic

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...

Share this post


Link to post
Share on other sites

_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.

Share this post


Link to post
Share on other sites

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?


MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites

Post some code with a sample csv file...

Share this post


Link to post
Share on other sites

#5 ·  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

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

It's like you took the words right out of...your mouth!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites

#12 ·  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.

Share this post


Link to post
Share on other sites

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


MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Here they are - nomes_original.csv and nomes_wanted.csv


MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites
:)

MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


MLMK - my blogging craziness...

Share this post


Link to post
Share on other sites

#19 ·  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.

Share this post


Link to post
Share on other sites

I got this error:

(14,77) : ERROR: $vDlimOut: undeclared global variable.

Return FileWrite($hFileOut, StringTrimRight($sWrite, StringLen($vDlimOut)


MLMK - my blogging craziness...

Share this post


Link to post
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
Sign in to follow this  
Followers 0