I have a .txt file which holds some links for a website. It's about 560 links. I'm trying to convert my .txt file into an Excel file with a column name.
How can I do this?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
Using OleDb. This is way overkill for a oneshot task.
1. Create template
.xls file. Use the top row for column names. You should programmatically make a copy of it and load the copy during step 2.
using (OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + templatefilename + "; Extended Properties=Excel 8.0"))
OleDbCommand cmd = new OleDbCommand("insert into...", conn); cmd.ExecuteNonQuery;
Assuming you want to do this programatically (this is Stack Overflow after all), there are three basic techniques:
(i) convert the text file to CSV format and open it in Excel.
(ii) use the Office's built in automation COM library and call this from code (here is an example in C# http://www.c-sharpcorner.com/UploadFile/ggaganesh/CreateExcelSheet12012005015333AM/CreateExcelSheet.aspx, in VB.NET http://www.vbdotnetheaven.com/UploadFile/ggaganesh/ExcelSpreadsheet04182005093012AM/ExcelSpreadsheet.aspx).
(iii) use a third party library to create the excel file (eg. http://www.gemboxsoftware.com/GBSpreadsheet.htm).
Each has its advantages and disadvantages:
(i) Definitely the simplest and cheapest option, though the least flexible in terms of layout, formatting, etc. Further, Excel has problems opening CSVs. For instance, it will try to be clever by interpreting any string of number characters as a number, and therefore format it as such. This is annoying because if your CSV contains, say, a phone number starting with zero (eg. 0292630678), Excel will remove the leading zero. If you do not have the requirement for leading zeros, and all you need to display is a basic table in Excel, then this technique is probably the simplest and easiest.
(ii) The most flexible approach, though possibly also the most complex. Should give you maximum control over your output. I believe you need to have Office installed on the machine that is producing your XLS file though. Calling the Office COM library from a version of C# before 4.0 is a pain due to its lack of support for optional parameters, of which the COM interface makes extensive use (so VB.NET is usually the better choice in this case).
(iii) Depending on the component you choose, this could be simpler, and would probably work out cheaper if you don't have Office installed on the machine producing the file.