Read a .txt file and export to Excel using vb.net

[NewGuy1234]

hello all,

I need to read a .txt file and then put that into fields in an excel spreadsheet. for example i get print spool files that have invoices on them i need to put the customer names into a column of customer names and their addresses into a column of addresses. here is what i wrote to read with:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim reader As New System.IO.StreamReader(TextBox1.Text)
        TextBox2.Text = reader.ReadLine()
        reader.Close()
    End Sub

as you can see i just have it outputting to a second textbox so i could see that it was reading properly.
I am very green with programming in general so please be exhaustive with your suggestions. thank you in advance.

[billboy]

Must you create an "XLS" file?
If not you might want to jus simply create a ".CSV" file which can be read by excel. This is a simpler process

I do something similiar to what you need by reading my text file into a DataGridView(DGV) and then creating my excel file from the DGV

[NewGuy1234]

no i don't have to put it directly into an xls however that is the ultimate goal. I'm not familiar with csv what is that? and please elaborate on how you would output to it. thanks!

[billboy]

a .csv file is a comma seperated value file that excel can read
Not sure what your program is doing but i would read the text file into a datatable then from the table into a DGV this will aloow you to import the fields you want a bit easier, and allow you to view the data someone else might have a better idea

So step
1 read file to datatable
2 populate DGV with the DataTable
3 export the DGV to a CSV file or directly to an excel .xls or xlsx file

[NewGuy1234]

i will try this

[billboy]

Ok if you need help on anyone of those steps its helpful if you post the code you have and pointout where you are stuck.

FYI, for whats its worth excel will open a .txt file directly

[Techno]

directing away from the original intention just because its "easier" is not a solution at all.
thats like saying to a financial institute not to use databases to store data but text files as its easier.

to use Excel, you need to familiarize yourself with the PIA - Programmable Interop Assemblies. in other words, directly access and using excel using the libraries.

good starting point:

http://support.microsoft.com/kb/301982

[Ashaelon]

Also to add, you can still take the approach of using a datatable and DGV while outputting to Excel, which, IMO, would still be simple since the data is already laid out in rows and columns with headings. Excel is not hard to write to by any means.

With CSVs, you have to be careful because if any of your data has a "," in it (ie Last Name, First Name), Excel will split the comma in between and put the data in seperate columns.

Just my 2 cents.

[Shaggy Hiker]

directing away from the original intention just because its "easier" is not a solution at all.
thats like saying to a financial institute not to use databases to store data but text files as its easier.

That isn't valid in this case. The interops and coding against them are a pain for anyone. Meanwhile, Excel opens CSV files as easily as it opens XLS files, and they are faster and easier to write. I would certainly prefer to create CSV over XLS, and do so whenever it is possible.

On the other hand, if you want to write to Excel directly, you will want to eventually end up with a late bound solution. I would agree that going from a datatable or datareader to Excel would be particularly easy, especially since I have a class over in the .NET CodeBank that exports either of those to Excel using late binding, so if you have the data in either a datatable or a datareader, you can use the class to do the rest. It is also late bound, so you won't have to deal with the PIA stuff unless you need to change the output methods.

[Techno]

true however if the business requirement, especially from a customer, is to export against a certain file type then because it maybe be "hard" to do so doesnt mean nor is it acceptable to say "its too hard, sorry, we are going to do it against a different file format".
just because its hard doesnt mean it should not be done nor investigated. it just doesnt work like that
furthermore, reading more and understanding more about using the PIA's against Word/Excel is what is required due to, as you have pointed out, the difficulty and given the fact that its quite complex.

there are 3rd party tools which do make it easier to export into Excel and should also be considered.

[kareninstructor]

If you are going to write for multiple versions of Excel (Office 2000, Office 2003, Office 2007) with no cell formatting CSV makes sense. If there is formatting involved for multiple versions of Excel then late binding makes sense.

An alternative for multiple versions with cell formatting using VS2010 (new to VS2010) is embedding multiple versions of Excel Interop into your application, which gives you the benefit of early binding which means tighter code.

My preference is working with a third party library called Aspose Cells which does not need Excel installed on the client machine and has the capabilities of Excel and then some. If interested Google Aspose Cells, download and try it out. The evaluation version has no restrictions other than it places footer at the bottom of each sheet indicating it is an evaluation. Once purchase a license file makes it a non-evaluation product. My preference for “simple”, no formatting would be either having Excel read a CSV file or using OleDb methods (see below).

You should also consider Shaggy Hiker’s code bank class also.

Working just with Office 2007 with cell formatting then there is OpemXML SDK from Microsoft, which as with Aspose library does not require Excel to be installed. The downside to OpenXML SDK is it does virtually nothing for you, instead you have full control with no limits in what you can do but comes with the price of you need to code everything. There is a free tool, which takes a workbook and writes code in C Sharp, which you can study then reproduce what you want. I have a code bank submission, which shows the basics. Just understand you cannot simply jump in and code but need to take time to understand the SDK.

OleDb data provider can write unformatted data to Excel using SQL insert statements to sheets and named ranges. If you need say column headers then create the column headers in an existing Excel file, place this file in a template folder. When ready to use it copy the template file to the location to write data too, write the data using OleDbConnection and OleDbCommand.

In regards to reading your text file there is TextFieldParser (Google this class) class in the .NET Framework which makes reading common delimited text files easy.

No matter which direction you decide on it would be best to work with this method outside of your project, get a feel for it with a small amount of data then a large data set so that if you run into roadblocks you are not dealing with surrounding code to solve the roadblock then move back to your project and code as needed.