How to merge csv and excel?

Question

I am trying to build simple program that does my weekly job.
Everytime I receive csv file, I maintain excel file.

My csv is like below:

key_code,eng_name,...so on
000001,some name,...so on

My excel is like below:

Some text are written on A1-G4
No column hearders written
Data is from 5th row
Each row has data from B-G(1st row B5-G5, 2nd row B6-G6)

If key_code in csv does not exist in excel, I add.
If key_code in csv does exist in excel, I update the rest columns.
If key_code in excel does not exist in csv, I delete the row.

Can anyone tell me any easy way or steps to get this done?
I am very confusing about what to use to update excel file among OleDb, Interop.Excel, epplus, spire.xls, etc.
And in which class do I have to store csv data and excel data to compare.

score 0 · Answer 1 · answered Jun 24 '20 at 04:42

If key_code in csv does not exist in excel, I add.

If key_code in csv does exist in excel, I update the rest columns.

If key_code in excel does not exist in csv, I delete the row.

As my understanding of the rules above, you simply delete the old excel file and create a new file from the data in the CSV file. You can use R to do this very easily:

#Install package 'writexl' if you didn't, by install.packages("writexl")
library(writexl) 
#File excel
fn <- "file.xlsx"
#Check its existence
if (file.exists(fn)) 
  #Delete file if it exists
  file.remove(fn)
#Read the csv file to a data frame
df <- read.csv("C:/newfile.csv")
#Write the data frame to excel file. Change col_names = TRUE if you want the headers.
write_xlsx(
      df,
      path = "file.xlsx",
      col_names = FALSE
)

Thank you for your help but I don't know R. And I can't make new excel file, since I need the texts in A1-G4, also there are some macros inside excel. — グジョンミン, Jun 24 '20 at 04:53
If you know VBA, there is a thread on how to open csv file in VBA: https://stackoverflow.com/questions/9564908/open-csv-file-via-vba-performance I didn't try but looks doable to me. — xwhitelight, Jun 24 '20 at 05:18

score 0 · Accepted Answer · answered Jun 24 '20 at 06:01

For reading CSV you can use ChoETL reader, this is one of the best CSV readers I have ever used.

The tricky part is to how to write Excel file and choosing the right tool, amongst the tools you have mentioned EPPlus is best because

Excel.Interop needs Excel(MS Office) to be installed on production machine which can create licencing issues
To use OleDB you need some nitty gritty to use it a better way

EPPlus provides some abstraction which makes it easy to manipulate the excel files

             using (var p = new ExcelPackage())
         {
            //A workbook must have at least on cell, so lets add one... 
            var ws=p.Workbook.Worksheets.Add("MySheet");
            //To set values in the spreadsheet use the Cells indexer.
            ws.Cells["A1"].Value = "This is cell A1";
            //Save the new workbook. We haven't specified the filename so use the Save as method.
            p.SaveAs(new FileInfo(@"c:\workbooks\myworkbook.xlsx"));
         }

This is very simple example given on the github page to write, please use it and post any specific issues

Thank you for your help. I will try using epplus. – グジョンミン Jun 24 '20 at 06:10 — グジョンミン, Jun 24 '20 at 06:10

How to merge csv and excel?

2 Answers2