CODEWITHSUNDEEP
c#csvexport
muhammetsahin
muhammetsahin

Reputation: 191

C# Export CSV file with semicolon

I want to convert an Excel file to csv and I wrote this C# code:

Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbooks workBooks = excelApp.Workbooks;
excelApp.DisplayAlerts = false;
Excel.Workbook workBook = workBooks.Open(sourceFile,
                                    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);
workBook.SaveAs(csvFilePath, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(false, sourceFile, null);
excelApp.DisplayAlerts = true;
Excel.Application app = excelApp.Application;
app.Quit(); 
Marshal.ReleaseComObject(workBooks);
Marshal.ReleaseComObject(workBook);
Marshal.ReleaseComObject(excelApp);
Marshal.ReleaseComObject(app);
sourceFile = csvFilePath;

But the csv file is seperated with commas. I have searched on the internet. I tried Region Setting and other things, but none of them solved my problem.

Example output: Hesap,391,TL,,,

I want this output: Hesap;391;TL;;;

Upvotes: 2

Views: 5875

Answers (2)

datalohr
datalohr

Reputation: 21

For German System Settings:

xlVorlBook.SaveAs(sDatei, FileFormat: Excel.XlFileFormat.xlCSV, Local: true);

Just use the Local: true to change the Separator to ;

Upvotes: 2

Rustam
Rustam

Reputation: 21

I had the same problem, - I had to migrate one project from Windows Server 2003 to Windows Server 2012, and this one

workBook.SaveAs(csvFilePath, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

and csv file was separated with commas instead of semicolons. I found one solution that helped me to avoid this problem

Formatting:

 oBook.SaveAs(newFileName, XlFileFormat.xlUnicodeText,
            missing, missing, false, false,
            XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing, missing, missing);

And after I rewrite file this way:

 string text = File.ReadAllText(newFileName);
            text = text.Replace("\t", ";").Replace("{tab}", "\t");
            File.WriteAllText(newFileName, text, Encoding.UTF8);

So I have csv file separated by semicolons. But another problem left - fileds with date and float values have another format, - date is 10/5/2015 18:54 instead of 5.10.2015 18:54 and float values 5.55 instead of 5,55.

I think the last two problems i will decide by converting vaules in my oracle procedures, but the main problem is passed - we have the same csv file we need.

Some of developers may suppose that comma is right and semicolon not or it is not difference between these delemiters, but if you have a big project there all procedures on the server side work in one way it is difficult to set and correct all of them.

And I don't understand one thing - if i have a comma in the field in excel file and i use this method workbook.SaveAs, how we will take correct value of the field further? - there will be two values instead of one! With semicolon situation is the same, but we never use semicolons in our Excel files.

Upvotes: 2

Related Questions