3

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?

2
  • 4
    If this is a one shot task, try copy/paste! =)
    – Jens
    Jun 24, 2010 at 13:16
  • 1
    Better suited for superuser.com
    – Tom Gullen
    Jun 24, 2010 at 13:18

7 Answers 7

6

You can do File | Open the txt file and you should see a prompt to import the file. It should ask you how the file is delimited (such as ',', or '|' or \t etc.) and you can import into an excel sheet.

1

Assuming it's one link per line, add a line on top containing the column name and then rename it to .csv and try opening it with Excel.

1
  • The steps 'add a line on top containing the column name' and 'then rename it to .csv' are both optional. If you open a plain text file, Excel just asks what the delimiter is. Jun 24, 2010 at 13:24
0

Are they one link per line? You should be able to copy/paste into Excel. If not, use a search and replace function in a decent text editor to add a carriage return before each "http://" Then paste.

@jiraz you got there first!

0

Another option would be to copy from the txt file and paste into an empty worksheet. Similarly, you could use the excel import feature (Data > Get External Data).

0

If you want to do this manually, then just open it up using Excel, and the Excel text file import wizard will pop up and guide you the rest of the way. This approach is the best way to deal with fixed length text files, and also works great with delimited files as well (not just csv).

0

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.
2. using (OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + templatefilename + "; Extended Properties=Excel 8.0"))
3. OleDbCommand cmd = new OleDbCommand("insert into...", conn); cmd.ExecuteNonQuery;

0

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.

Good luck.

You must log in to answer this question.