Manipulating CSV data

ld13

Honorary Master
Joined
Oct 28, 2005
Messages
12,370
Our business software kicks out .CSV data that we (read: I) then manually manipulate and prime in the format that can be imported into our accounting software. This is repetitive work and I would like to find out if there is a better way of doing this, short of making an autohotkey/macro script?

First I compare the previous data set with the latest one to only work with the new data lines. Will probably look for a diff tool of sorts.
Then I manipulate the remaining data into the order required, e.g. column B,A,Z,E and dump the remaining data.

Any tips?
 

IOPS

Senior Member
Joined
Oct 2, 2016
Messages
500
R Studio.

Edit: After reading your post properly, macros might be the easiest.
 

prOd

Expert Member
Joined
Nov 11, 2009
Messages
3,978
Any programming experience? I'd probably go the Python route, should be easy enough even for a total beginner.
 

gfmalan

Expert Member
Joined
Nov 11, 2013
Messages
2,676
Python,

Followed this and bought the training (Udemy) when I started.
 

DrJohnZoidberg

Honorary Master
Joined
Jul 24, 2006
Messages
22,284
+1 to Python. I even used to do basic csv manipulation in bash scripts using Linux cli tools like grep.
 

ld13

Honorary Master
Joined
Oct 28, 2005
Messages
12,370
Any programming experience? I'd probably go the Python route, should be easy enough even for a total beginner.

Minor experience. Started off with Java in school etc. and that is where my formal training ended. Dabble with a bit of php and javascript here and there these days. Most of my active code base (ha, my base is not as big as the word make it sound) is copied and pasted from other people's questions and answers before being tweaked to my will:

eRrtM0O.png
 

alphabyte

Well-Known Member
Joined
Jun 17, 2008
Messages
487
Should not be too difficult to set up macros, especially if you are not an experienced programmer.
Also keep in mind that someone else may have to take over the job from you in the future.
 

ld13

Honorary Master
Joined
Oct 28, 2005
Messages
12,370
Also keep in mind that someone else may have to take over the job from you in the future.

Whomever takes over will have no programming experience, so I am not worried about making anything everlasting or even remotely user friendly. This will be purely for my own personal use in the end.
 

HeftyCrab

Expert Member
Joined
Mar 26, 2009
Messages
2,292
Minor experience. Started off with Java in school etc. and that is where my formal training ended. Dabble with a bit of php and javascript here and there these days. Most of my active code base (ha, my base is not as big as the word make it sound) is copied and pasted from other people's questions and answers before being tweaked to my will:

eRrtM0O.png

If you know some JAVA you can check out the OpenCSV lib.

https://www.baeldung.com/opencsv
 

mh348

Expert Member
Joined
Jun 2, 2006
Messages
4,189
For this, I would get someone on upwork.com to either write a python script or small java app.

I've had a few scripts/apps made for similar type of work with excel/word files etc, pay about $20 to $30 once off and once it's done, all you do is run a script and your work is done.
 

ld13

Honorary Master
Joined
Oct 28, 2005
Messages
12,370
Python, KNIME and OpenCSV 4 .... installed, installed and downloaded. Thank you guys, will be playing around with these. KNIME looks extremely interesting.

For this, I would get someone on upwork.com to either write a python script or small java app.

I have never thought of this. Great idea actually. At this stage I would prefer to code/figure out something for myself in order to learn something but will opt for something like this if that takes too long in the end.
 

initroot

Senior Member
Joined
Jul 30, 2011
Messages
865
Python, KNIME and OpenCSV 4 .... installed, installed and downloaded. Thank you guys, will be playing around with these. KNIME looks extremely interesting.



I have never thought of this. Great idea actually. At this stage I would prefer to code/figure out something for myself in order to learn something but will opt for something like this if that takes too long in the end.
You'll love KNIME, no need for code, only if you need to create your own nodes.
 
Top