Hi all,
I'm trying to make a program that automatically converts csv files to xls files. I'm using Visual Studio 2005 (Visual Basic).
My csv files are separated with ; (so, not with a comma)
I want my program to open an existing csv file, convert it and then save it as an xls file without me seeing it.
Is this possible? I hope i explained it well.
Many thanks in advance!
Greets Hans
Hi!
It's possible. You can start Excel, load CSV there and save as XLS. Add COM reference to your project on Excel and browse interop assembly (Workbook & Application interfaces). If you want invisible Excel use "Application.Visible = false"
Hi,
Do you maybe have an example code that i can use for my project?
Many thanks in advance!
I'm sorry, but I haven't. I work with C# and last time do Excel stuff few months ago. It's something like this (not really working sample, but for better understanding):
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook doc = app.Workbooks.Open(fileName);
doc.SaveAs(newFileName);
app.Quit();
Check Excel docs for parameters in Open and SaveAs.
Is it possible to just change the extension of a file?
I've looked in Help and i saw something, but i can't find out how to do it.
It's just nog working. I want to select a file with OpenFileDialog, then push a button that changes the name of the file from test.csv to test.xls
Can anybody let me know how to do this?
Thnx!!
You can't rename file and expect that internal format changed automatically (still, this is great idea to embed into OS).
CSV is a text based format, XLS is binary Excel format. You need to open it in Excel.
Here is sample to convert any file (that Excel can open) to the XLS format:
using
System;using
System.Reflection;using
Microsoft.Office.Interop.Excel;namespace
ConsoleApplication2{
class Program
{
static void Main(string[] args)
{
ApplicationClass app = new ApplicationClass(); Workbook doc = app.Workbooks._Open(
@"I:\file.csv", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, false, // Editable Missing.Value, Missing.Value, false);// AddToMRU
doc.SaveAs(
@"I:\file.xls", XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, false, // AddToMRU Missing.Value, Missing.Value, Missing.Value);
doc.Saved =
true;app.Quit();
}
}
}
Ok...
Does anybody then know a code to open a file with visual basic?
The code of Sergey didn't work.
But this is not a Visual Basic code is it?
So it wouldn't work in my project i think.
No, it's C# code. You can translate it to VB (I don't use VB and I haven't install it, so I can't do it with 100% gurantee
).
All you need is to get Excel application object, call app.Workbooks.Open() to get document and call doc.SaveAs() to save it. Just translate to VB params (all where you see Missing.Value just think as that parameter not passed to function). I may make syntax mistakes, but I think it will be:
dim app as ApplicationClass = new Excel.ApplicationClass()
dim doc as Workbook = app.Open(csvFileName)
doc.SaveAs(xlsFileName, XlFileFormat.xlWorkbookNormal);
im having a similar problem with my .csv file however i am able to open it using excel, but when i do it displays an error message saying "file not loaded completly"
how do i open this .csv file in excel without losing any data?
Hi!
I think you have a very large CSV file - Excel can handle up to 65536 lines, but after that it will scream something about "file not loaded completely".
In this case you can't do much because of Excel limitations. Still you can do something (if this acceptable) - read file by yourself and paginate it. But this will need a little more work to be done. First you have to open empty Excel doc, then you manually open your CSV, start reading it and pass to Excel pages of 1000 or 65000 lines (also you have to add new pages into document).
Or there is another way (a little bit simplier) - you can write application that split large CSV into multiple CSV and convert them one by one. You don't need to deal with CSV details - just read text file and save text files with no more than 65536 lines.
Hi Steven,
Could you let me know how you open the .csv files?
My files are about 200-300 lines max, so for me it shouldn't be a problem.
Thnx!
