Reverse line order in cvs file
Moderators: Cat_7, Ronald P. Regensburg
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Reverse line order in cvs file
Well, I suppose this is as far off topic as it can be.
Maybe one of the bright heads in this forum can give a solution.
My bank offers csv files that are written bottom to top, but my financial software (CheckBook) reads the file from top to bottom. The application sorts correctly by date, but transactions within one day are displayed in the wrong order, which makes comparing with the balance on paper statements difficult.
I will write the developer of CheckBook, but in the meantime it would be nice if I could use a script that could read the file and export to another new file with the lines in reverse order. Even better if the first line (with headers) would be left alone:
line 1
line 2
line 3
line 4
line 5
exported as:
line 5
line 4
line 3
line 2
line 1
or even better, exported as:
line 1
line 5
line 4
line 3
line 2
I am on OSX Yosemite. I have TextWrangler (has many of BBEdit's scripting possibilities), MS Office 2011 Excel, Apple's Numbers 3.5, Xcode 6.1.1, and, of course, all commands that come with OSX.
Anyone?
Maybe one of the bright heads in this forum can give a solution.
My bank offers csv files that are written bottom to top, but my financial software (CheckBook) reads the file from top to bottom. The application sorts correctly by date, but transactions within one day are displayed in the wrong order, which makes comparing with the balance on paper statements difficult.
I will write the developer of CheckBook, but in the meantime it would be nice if I could use a script that could read the file and export to another new file with the lines in reverse order. Even better if the first line (with headers) would be left alone:
line 1
line 2
line 3
line 4
line 5
exported as:
line 5
line 4
line 3
line 2
line 1
or even better, exported as:
line 1
line 5
line 4
line 3
line 2
I am on OSX Yosemite. I have TextWrangler (has many of BBEdit's scripting possibilities), MS Office 2011 Excel, Apple's Numbers 3.5, Xcode 6.1.1, and, of course, all commands that come with OSX.
Anyone?
Last edited by Ronald P. Regensburg on Sun Dec 21, 2014 4:27 pm, edited 1 time in total.
Reason: corrected cvs -> csv
Reason: corrected cvs -> csv
Re: Reverse line order in cvs file
Hi,
Can your give an example of a single record?
Do your data have a timestamp in the record, beside a date? If, so you might be able to import (menu: Data/from text) the cvs with Excel, tell it to treat the first line as header and then inverse sort the other records based on timestamp.
Best,
Cat_7
Can your give an example of a single record?
Do your data have a timestamp in the record, beside a date? If, so you might be able to import (menu: Data/from text) the cvs with Excel, tell it to treat the first line as header and then inverse sort the other records based on timestamp.
Best,
Cat_7
Last edited by Cat_7 on Sun Dec 21, 2014 1:15 pm, edited 1 time in total.
Reason:
Reason:
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Last edited by Ronald P. Regensburg on Sun Dec 21, 2014 1:44 pm, edited 1 time in total.
Reason:
Reason:
Re: Reverse line order in cvs file
sent you a message through gmail.
Best,
Cat_7
Best,
Cat_7
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Thanks, but your suggestion is not the solution. I do not mean sorting by date reversed, but all lines in the reverse order.
I sent you reply by email with example.
I sent you reply by email with example.
Re: Reverse line order in cvs file
When you insert a column with an index number from the second line on down and reverse sort on that, you get all entries per date reversed. Will that solve the issue?
Best,
Cat_7
Best,
Cat_7
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Probably, but I do not want to do that manually again and again every week. Can that be automated? That's what I would like to be able to do. Download the csv file from the bank, throw it at a script and have the converted csv file with entries in reversed order that I can import in CheckBook.
Edit: Or maybe this could be easily done with a template that could be used to import the original csv file and export it to a converted csv file with changed order? At east with reversed order of entries within each date?
I am really not good at working with these kind of things.
Edit: Or maybe this could be easily done with a template that could be used to import the original csv file and export it to a converted csv file with changed order? At east with reversed order of entries within each date?
I am really not good at working with these kind of things.
Last edited by Ronald P. Regensburg on Sun Dec 21, 2014 7:35 pm, edited 1 time in total.
Reason:
Reason:
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Removed all the Dutch.
I use computers longer than most people, but I am a complete newbie where software like Excel is concerned. I simply never needed it. Cat_7 helped me to get the reverse sorting of the lines, with the exception of the headers, using Excel. It roughly goes like this:
In a new book import the comma-separated csv file
Add a new column A
Type "1" in A2
Fill the cells in column A from A2 downward with steps of 1
Sort everything from row 2 onward by the numbers in column A in reverse order
Remove column A
Save again as comma-separated csv file
(The file is actually saved semicolon-separated, but I suppose that is a bug in Excel.)
The result works fine as intended (after replacing the semicolons by commas).
Question that remains: Is there any way to automate (part of) this process?
I use computers longer than most people, but I am a complete newbie where software like Excel is concerned. I simply never needed it. Cat_7 helped me to get the reverse sorting of the lines, with the exception of the headers, using Excel. It roughly goes like this:
In a new book import the comma-separated csv file
Add a new column A
Type "1" in A2
Fill the cells in column A from A2 downward with steps of 1
Sort everything from row 2 onward by the numbers in column A in reverse order
Remove column A
Save again as comma-separated csv file
(The file is actually saved semicolon-separated, but I suppose that is a bug in Excel.)
The result works fine as intended (after replacing the semicolons by commas).
Question that remains: Is there any way to automate (part of) this process?
Re: Reverse line order in cvs file
Hello Ronald,
You can throw the file on an AppleScript in order to get it processed. I don't know how to do that (maybe someone else could help). However, I can provide an AppleScript that should work once you have opened the file in Excel (please don't click on any cell but leave the top left one activated for the script to work).
There are probably more advanced ways to do this with AppleScript. Any suggestions/corrections are welcome. And note that I use a German Excel, so you will have to substitute the names of the menu items with the ones of your Dutch version. Also, if you use a newer version of Excel some dialog windows might look a bit different and therefore the script might have to be amended.
Here we go:
Now you can type a name for the .csv file and choose a place to save it.
You can throw the file on an AppleScript in order to get it processed. I don't know how to do that (maybe someone else could help). However, I can provide an AppleScript that should work once you have opened the file in Excel (please don't click on any cell but leave the top left one activated for the script to work).
There are probably more advanced ways to do this with AppleScript. Any suggestions/corrections are welcome. And note that I use a German Excel, so you will have to substitute the names of the menu items with the ones of your Dutch version. Also, if you use a newer version of Excel some dialog windows might look a bit different and therefore the script might have to be amended.
Here we go:
Code: Select all
global window_name
set myApplication to "Microsoft Excel"
tell application myApplication
activate
if the (count of windows) is not 0 then
set window_name to name of front document
end if
end tell
tell application "System Events"
key down command
key code 125 -- Cursor Down
key up command
click menu item "Spalten" of menu "Einfügen" of menu bar 1 of application process myApplication
delay 0.3
keystroke "1"
key down command
key code 126 -- Cursor Up
key up command
key code 125 -- Cursor Down
keystroke "1"
key down command
key down shift
key code 125 -- Cursor Down
key up command
key up shift
click menu item "Datenreihe..." of menu 1 of menu item "Ausfüllen" of menu "Bearbeiten" of menu bar 1 of application process myApplication
delay 0.3
keystroke "1"
click radio button 2 of radio group 1 of group 3 of window 1 of application process myApplication
click UI element "OK" of window 1 of application process myApplication
delay 0.5
key code 126 -- Cursor Up
click menu item "Sortieren..." of menu "Daten" of menu bar 1 of application process myApplication
delay 0.3
click radio button 2 of radio group 1 of window 1 of application process myApplication
click UI element "OK" of window 1 of application process myApplication
delay 0.2
key code 123 -- Cursor Left
click menu item "Zellen löschen..." of menu "Bearbeiten" of menu bar 1 of application process myApplication
delay 0.3
click radio button 4 of radio group 1 of group 1 of window 1 of application process myApplication
click UI element "OK" of window 1 of application process myApplication
delay 0.2
click menu item "Speichern unter..." of menu "Datei" of menu bar 1 of application process myApplication
delay 0.3
click pop up button 1 of group 1 of sheet 1 of window window_name of application process myApplication
click menu item "Kommagetrennte Werte (.csv)" of menu 1 of pop up button 1 of group 1 of sheet 1 of window window_name of application process myApplication
end tell
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Thanks. I will try it when I have had time to translate the menu items.
Which Excel version is this written for? I have Microsoft Excel 14.4.7 from Microsoft Office 2011. It seems your script refers to radio buttons in the Sort dialog. In my Excel that dialog shows lines with menus, not radio buttons.
Which Excel version is this written for? I have Microsoft Excel 14.4.7 from Microsoft Office 2011. It seems your script refers to radio buttons in the Sort dialog. In my Excel that dialog shows lines with menus, not radio buttons.
-
- Granny Smith
- Posts: 120
- Joined: Fri Nov 09, 2007 5:42 pm
Re: Reverse line order in cvs file
"tail -r" will do the job
example:
tail -r bankFile >> appFile
all the files in bankFile will be written in reverse order into a new file called appFile
example:
tail -r bankFile >> appFile
all the files in bankFile will be written in reverse order into a new file called appFile
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Wow! That is simple!
Now I only need to put the originally first line with the headers, that is now the last line, back to its place at the top as first line. Can that be automated as well?
Now I only need to put the originally first line with the headers, that is now the last line, back to its place at the top as first line. Can that be automated as well?
-
- Granny Smith
- Posts: 120
- Joined: Fri Nov 09, 2007 5:42 pm
Re: Reverse line order in cvs file
oh, sure:
head -n1 bankFile >> appFile && tail -n+2 bankFile | tail -r >> appFile
head -n1 bankFile >> appFile && tail -n+2 bankFile | tail -r >> appFile
Re: Reverse line order in cvs file
It is written for Excel 12.3.6 (Office 2008).Ronald P. Regensburg wrote:Which Excel version is this written for?
I found the script does not work correctly with the Excel file I just used for testing (yesterday it did). But it's not worth fixing it as "tail -r" is just way more simple.
When entering
head -n1 1.csv >> 2.csv && tail -n+2 1.csv | tail -r >> 2.csv
in Terminal, 2.csv contains a copy of 1.csv's content, but it is not reversed. Am I missing something?
-
- Granny Smith
- Posts: 120
- Joined: Fri Nov 09, 2007 5:42 pm
Re: Reverse line order in cvs file
That's weird. It works just fine on my machine (OS X 10.10.1).mabam wrote: When entering
head -n1 1.csv >> 2.csv && tail -n+2 1.csv | tail -r >> 2.csv
in Terminal, 2.csv contains a copy of 1.csv's content, but it is not reversed. Am I missing something?
Re: Reverse line order in cvs file
I'm on 10.8.5. That's probably why.
-
- Granny Smith
- Posts: 120
- Joined: Fri Nov 09, 2007 5:42 pm
Re: Reverse line order in cvs file
Does this command work for you?
tail -r 1.csv >> 2.csv
It should reverse the whole file
It's really odd that a command like head or tail change their behavior.
tail -r 1.csv >> 2.csv
It should reverse the whole file
It's really odd that a command like head or tail change their behavior.
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Works perfectly here.MetalSnake wrote:head -n1 bankFile >> appFile && tail -n+2 bankFile | tail -r >> appFile
Edit: I am on OSX 10.10.1
Last edited by Ronald P. Regensburg on Tue Dec 23, 2014 2:09 pm, edited 1 time in total.
Reason:
Reason:
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
With the original file on my desktop, I do now:
cd Desktop
head -n1 XX.csv >> XX_A.csv && tail -n+2 XX.csv | tail -r >> XX_A.csv
where I replace XX with the file name without the csv extension. (I do that in a text editor before pasting the command in Terminal.)
Can this also be automated, by determining somewhere in the script that XX should be read as the name of the original file without its csv extension?
cd Desktop
head -n1 XX.csv >> XX_A.csv && tail -n+2 XX.csv | tail -r >> XX_A.csv
where I replace XX with the file name without the csv extension. (I do that in a text editor before pasting the command in Terminal.)
Can this also be automated, by determining somewhere in the script that XX should be read as the name of the original file without its csv extension?
Re: Reverse line order in cvs file
No, that doesn't work either. The content of 2.csv is identical to the one of 1.csv.MetalSnake wrote: Does this command work for you?
tail -r 1.csv >> 2.csv
It should reverse the whole file
It's really odd that a command like head or tail change their behavior.
- Ronald P. Regensburg
- Expert User
- Posts: 7830
- Joined: Thu Feb 09, 2006 10:24 pm
- Location: Amsterdam, Netherlands
Re: Reverse line order in cvs file
Maybe the option -r was added to tail later and was not yet present in tail in OSX 10.8?
-
- Granny Smith
- Posts: 120
- Joined: Fri Nov 09, 2007 5:42 pm
Re: Reverse line order in cvs file
I tried it on OS X 10.8, works without problems there too.
Must be something else.
Must be something else.
- adespoton
- Forum All-Star
- Posts: 4274
- Joined: Fri Nov 27, 2009 5:11 am
- Location: Emaculation.com
- Contact:
Re: Reverse line order in cvs file
I used to use tail -r on 10.2; it's been standard for a long time. Maybe the user has gnutools installed? What does `which tail` return?
-
- Inquisitive Elf
- Posts: 36
- Joined: Sat Oct 03, 2009 12:51 am
- Location: France
Re: Reverse line order in cvs file
Hi !
Actually, 'which' is not reliable.
What does 'type -a tail' return ?
By the way, I tried the command inside cygwin (My mac's HD is dead right now, reverting to my old backup pc) where tail does not support the r switch, and that's clearly notified : tail: unknown option -- r
So that may not be an error with tail...
Actually, 'which' is not reliable.
What does 'type -a tail' return ?
By the way, I tried the command inside cygwin (My mac's HD is dead right now, reverting to my old backup pc) where tail does not support the r switch, and that's clearly notified : tail: unknown option -- r
So that may not be an error with tail...
Last edited by mathieudel on Wed Jan 07, 2015 11:10 pm, edited 1 time in total.
-
- Inquisitive Elf
- Posts: 36
- Joined: Sat Oct 03, 2009 12:51 am
- Location: France
Re: Reverse line order in cvs file
Too bad I don't have my Mac working right now or I would have prepared an AppleScript application for you...
Anyway, you can copy-paste this to a new export_to_checkbook file :
and then type in the terminal :
That script reverses all but the first line of the files given as an argument, appending '_checkbook' to the file names. Since 'tail -r' is only valid on BSD systems* (like MacOS X) I had to test it using tac. I suspect that tac is available with MacOS X too, which would fix your problem, so I kept the command selection in the script.
So all you have to do from the terminal now is :
And it would output a new 'account_checkbook.csv' file.
Making this script accept it's input from the input stream is simple... but I don't remember how right now ! I'll add this feature tomorrow if you like : I just need to read back some of my old scripts at work to refresh my memories !
Now, if you mix the "Drag 'n' Drop Applet Shell" from here with the answers from here and the script above, you should have a user friendly drag and drop tool to do all your conversions from the Finder.
Since I've never touched AppleScript (well... not for 20 years at least), I can't do it reliably without an actual Mac at hand, maybe someone else could help ?
(I'm quite bad at shell script writing... if anyone here would be glad enough to help me simplify this, fix bad habits and vulnerabilities (I know I did not check for file name conformance to expected *.* format), etc... I would be grateful !)
* According to GNU bash, using tail -r on BSD is not a good idea either :
Anyway, you can copy-paste this to a new export_to_checkbook file :
Code: Select all
function exportToCheckBookFormat() {
REVERSE_COMMAND=$(type tac 1>/dev/null 2>&1 && echo "tac" || echo "tail -r")
head -n1 "$1"
tail -n+2 "$1" | $REVERSE_COMMAND
}
function exportToCheckBookFile() {
DESTINATION_NAME=$(echo "$1" | sed -E 's|^(.*)\.([^.]*)$|\1_checkbook.\2|g')
exportToCheckBookFormat "$1" > "$DESTINATION_NAME"
}
while (( "$#" )); do
[ -f "$1" ] && exportToCheckBookFile "$1"
shift
done
Code: Select all
chmod +x export_to_checkbook
So all you have to do from the terminal now is :
Code: Select all
./export_to_checkbook account.csv
Making this script accept it's input from the input stream is simple... but I don't remember how right now ! I'll add this feature tomorrow if you like : I just need to read back some of my old scripts at work to refresh my memories !
Now, if you mix the "Drag 'n' Drop Applet Shell" from here with the answers from here and the script above, you should have a user friendly drag and drop tool to do all your conversions from the Finder.
Since I've never touched AppleScript (well... not for 20 years at least), I can't do it reliably without an actual Mac at hand, maybe someone else could help ?
(I'm quite bad at shell script writing... if anyone here would be glad enough to help me simplify this, fix bad habits and vulnerabilities (I know I did not check for file name conformance to expected *.* format), etc... I would be grateful !)
* According to GNU bash, using tail -r on BSD is not a good idea either :
GNU 'tail' can output any amount of data (some other versions of
'tail' cannot). It also has no '-r' option (print in reverse), since
reversing a file is really a different job from printing the end of a
file; BSD 'tail' (which is the one with '-r') can only reverse files
that are at most as large as its buffer, which is typically 32 KiB. A
more reliable and versatile way to reverse files is the GNU 'tac'
command.