2013/10/02

[C#] Customize the delimiter of CSV when loading it by using Excel.Workbook.Open() (VBA)

I got an old program which can load a csv file and do something. The customer wants to change the delimiter from comma (,) to others like ";", "|", or "-"... etc. Sounds like an easy job. But no, it's not. The approach that uses on opening the csv file is
using Microsoft.Office;
....
xlsApp = new Excel.ApplicationClass();
xlsWBs = xlsApp.Workbooks;
xlsWB = xlsWBs.Open(TempFileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

I tried to use the following parameters (according to a MSDN document at here) but no luck.
xlsWB = xlsWBs.Open(TempFileName,Type.Missing,Type.Missing,6,Type.Missing,Type.Missing,Type.Missing,Type.Missing,"|",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

Then I found a trick: change the file ext from ".csv" to ".txt" then it will works. Why? Read the MSDN document that I mentioned above about the Format parameter carefully: "If Microsoft Excel opens a text file ...". See? A text file! :)

1 comment: