Manipulating CSV data

L

ld13

Honorary Master
Joined
Oct 28, 2005
Messages
13,925
Reaction score
2,474
Location
Stellenbosch
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?
 
R Studio.

Edit: After reading your post properly, macros might be the easiest.
 
Any programming experience? I'd probably go the Python route, should be easy enough even for a total beginner.
 
+1 to Python. I even used to do basic csv manipulation in bash scripts using Linux cli tools like grep.
 
prOd said:
Any programming experience? I'd probably go the Python route, should be easy enough even for a total beginner.
Click to expand...

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
 
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.
 
alphabyte said:
Also keep in mind that someone else may have to take over the job from you in the future.
Click to expand...

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.
 
ld13 said:
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
Click to expand...

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

https://www.baeldung.com/opencsv
 
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.
 
Python, KNIME and OpenCSV 4 .... installed, installed and downloaded. Thank you guys, will be playing around with these. KNIME looks extremely interesting.

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

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.
 
ld13 said:
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.
Click to expand...
You'll love KNIME, no need for code, only if you need to create your own nodes.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X