比较定界文件以进行数据库更新

Question

Any suggestions on packages (or methodologies) that might help with this? I need to take a ~40MB file we receive weekly and determine what changed from the previous to the current file. Whatever those changes are, then need to be made to a single simple database table. In a previous life I've accomplished similar via Linux "diff" with -Hae parameters, resulting in an "ed script". The contents were then handled by a PERL program, using Tie::File to reference the changed record in the previous file. In an effort to strengthen my Go skills I'm trying to utilize it for this current task. https://github.com/sergi/go-diff looks like it might be the ticket, but I'm not sure "patch" output will quite do what I need (easily).

Fixed width and/or delimited text files are still commonly used, does anyone have any samples or pointers or suggestions on packages that might help in dealing with them in this way?

Answer 1

Are you sure you need the intermediate step? 40 MB is not very much, and your database engine is specialized in handling data like that..

For instance with postgresql just load the new data into a temporary table:

create table temptable (
 a varchar,
 b varchar,
 c varchar
);
copy temptable from '/path/to/csv/newdata.txt' delimiter ',' csv;

Then you can use simple SQL query to get the lines that do not have exact match in the old data, for example:

select *
from temptable t
where not exists (
 select 1
 from oldtable o
 where t.a=o.a and t.b=o.b and t.c=o.c
)

If you did not save the data from previous week's batch, then just remember to copy it to an other table for storing. Now the real question is what you want to do with the information, but you should be able to handle most scenarios.