Reverse line order in cvs file

[Ronald P. Regensburg]

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?

[Cat_7]

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

[Ronald P. Regensburg]

No timestamp besides the date.

Here is an example: http://ronaldpr.home.xs4all.nl/example.csv.zip

[Cat_7]

sent you a message through gmail.

Best,
Cat_7

[Ronald P. Regensburg]

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.

[Cat_7]

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

[Ronald P. Regensburg]

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.

[Ronald P. Regensburg]

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?

[mabam]

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:

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

Now you can type a name for the .csv file and choose a place to save it.

[Ronald P. Regensburg]

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.

[MetalSnake]

"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

[Ronald P. Regensburg]

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?

[MetalSnake]

oh, sure:

head -n1 bankFile >> appFile && tail -n+2 bankFile | tail -r >> appFile

[mabam]

Which Excel version is this written for?

It is written for Excel 12.3.6 (Office 2008).
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?

[MetalSnake]

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?

That's weird. It works just fine on my machine (OS X 10.10.1).

[mabam]

I'm on 10.8.5. That's probably why.

[MetalSnake]

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]

head -n1 bankFile >> appFile && tail -n+2 bankFile | tail -r >> appFile

Works perfectly here.

Edit: I am on OSX 10.10.1

[Ronald P. Regensburg]

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?

[mabam]

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.

No, that doesn't work either. The content of 2.csv is identical to the one of 1.csv.

[Ronald P. Regensburg]

Maybe the option -r was added to tail later and was not yet present in tail in OSX 10.8?

[MetalSnake]

I tried it on OS X 10.8, works without problems there too.
Must be something else.

[adespoton]

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?

[mathieudel]

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

[mathieudel]

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 :

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

and then type in the terminal :

Code: Select all

chmod +x export_to_checkbook

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 :

Code: Select all

./export_to_checkbook account.csv

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 :

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.