convert text file to excel ?

[pakerly]

How would i do this, convert a test file to excel?
Lets say my text file has fields like this:
NUMBER NAME ADDRESS PHONE
11002 Test1 TestAddress1 111-2222

lets say the number field is 10 characters long, there is a space and
Name field is 15 characters long, there is a space, address can be 25
characters long, there is a space and phone is 10 characters long

I want to put the Data in Excel as follows:
A B C D
1 11002 Test1 TestAddress1 111-2222


So I need to read a text file, put the contents into an excel file and
save it as .xls
And can this be done as a service?...or does it have to be an .exe in
vb 2008? thanks

 

[James Hahn]

If you don't need to specify formatting for the Excel items, you could save
the file in CSV format. That will import into Excel as a simple list of
fields. The name and address items will need to be wrapped in quotes, as
they could include commas. Or, use TAB instead of comma and accept the
Excel default during import..
http://www.cknow.com/ckinfo/c/CSV-Comma-SeparatedValue.html

 

[pakerly]

Its not TAB delimited though. Its 10 characters, a space, 10more, etc.

I cant put quotes around a field because this is the way I get a file,
no quotes. And there will never be a coma in that field the way this
file comes out.

 

[eBob.com]

I think that James Hahn was suggesting, and if he wasn't I am, that you
could write a simple text manipulation program which places a comma or tab
character between the values. Such a file can be read by Excel.
Its not TAB delimited though. Its 10 characters, a space, 10more, etc.

I cant put quotes around a field because this is the way I get a file,
no quotes. And there will never be a coma in that field the way this
file comes out.

 

[eBob.com]

Sorry, my previous reply, below, was sent before I had finished it. The
complete reply which I had intended was ...

I think that James Hahn was suggesting, and if he wasn't I am, that you
could simply write a simple text manipulation program which places a comma
or tab character between the values. Such a file can be read by Excel. You
CAN interface with Excel to create an Excel spreadsheet and it's a lot of
fun. But given your requirements, as you've explained them, you could just
write a text file and save considerable effort. (Although you will have
less fun!)

Bob

 

[James Hahn]

What I was suggesting was that you process the file by reading your input
text file, separating out the fields according to your fixed-width field
structure and write out a new file that is CSV format (with text fields
quoted for preference) or as a tab-delimited file (quoting not required.

This is not an XLS file, but it can be opened in Excel. The drawback is that
you cannot specify formatting for the columns, but you haven't indicated
whether that's an issue.

The advantage of tab delimited is that the file will import into Excel using
default values for the process. Since your existing file will also import
into Excel, but requires user intervention or a macro) to specify the column
widths, I assume that unattended import is important for your project.

Creating an XLS file is a much larger task that you probably wouldn't tackle
without assistance, but it does give you control over the resulting
worksheet. Here's some examples:
http://www.bytescout.com/bytescoutxls.html
http://www.codeplex.com/ExportToExcel

An alternative is to process the source file into a database that Excel can
understand. Look in this NG from a few days ago for "Exporting to Excel".

Its not TAB delimited though. Its 10 characters, a space, 10more, etc.

I cant put quotes around a field because this is the way I get a file,
no quotes. And there will never be a coma in that field the way this
file comes out.

 

[pakerly]

Sorry, my previous reply, below, was sent before I had finished it.  The
complete reply which I had intended was ...

I think that James Hahn was suggesting, and if he wasn't I am, that you
could simply write a simple text manipulation program which places a comma
or tab character between the values.  Such a file can be read by Excel. You
CAN interface with Excel to create an Excel spreadsheet and it's a lot of
fun.  But given your requirements, as you've explained them, you could just
write a text file and save considerable effort.  (Although you will have
less fun!)

Bob

Well my initial reaction to this was just that. Write a little program
that opens a file, Removes the first two lines, because I really don't
need the headings, moves over 10 spaces and inserts a tab, moves over
15 and inserts a tab and then saves the file as .xls

My problem is I only have figured out how to read in a file so far

 

[James Hahn]

What part of the process are you having a problem with? Parsing the input,
creating the output, creating the output file, writing to the output file,
etc?

snip <

Well my initial reaction to this was just that. Write a little program
that opens a file, Removes the first two lines, because I really don't
need the headings, moves over 10 spaces and inserts a tab, moves over
15 and inserts a tab and then saves the file as .xls

My problem is I only have figured out how to read in a file so far

 

[Armin Zingler]

My problem is I only have figured out how to read in a file so far

Help helps:
http://msdn.microsoft.com/en-us/library/k3352a4t.aspx
http://msdn.microsoft.com/en-us/library/ws92aysc.aspx



Armin

 

[pakerly]

I can read in a file.
How would I read over 10 lines, insert a TAB, read over 15 more,
Insert a TAB?

And how can this than automatically be saved out as excel?

 

[pakerly]

Here is what I am doing to read in the file....And I put the contents
in a textbox just to know that it is being read.

Dim myclient As New System.Net.WebClient
txtbox1.Text = myclient.DownloadString("C:\Documents and
Settings\user\Desktop\mytestfile.txt")

 

[Armin Zingler]

I can read in a file.

Sorry, my mistake! I read "I haven't figured out..."


Armin

 

[James Hahn]

NOT tested or checked.

Dim sw as New StreamWriter ("C:\Documents and
Settings\user\Desktop\myEXCELfile.txt")
Dim a() as string
a = Split(txtbox1.text, vbCRLF)

dim e(10) as string
for each s as string in a
e(0) = s.Substring(0,10)
e(1) = s.Substring(10,15)
< etc >

dim b as String = Join(e, Ctype(Chr(8),String))
sw.WriteLine(b)
Next
sw.Close()

I can read in a file.
How would I read over 10 lines, insert a TAB, read over 15 more,
Insert a TAB?

And how can this than automatically be saved out as excel?

 

[pakerly]

I'm totally lost....Can anyone show me how to do this?
Basically read in a file
Loop through it and at 10 spaces insert a TAB or coma
at 15 spaces insert another TAB or coma

Save the file out as an excel .xls or csv whichever will put these
records into A1, B1, C1, A2, B2, C2 etc ?

And if anyone is willing to do this could you document where the TABS
or comas are being put in and where the file is beting re-written out?

thanks, in the mean time i'll keep playing with this.

 

[Armin Zingler]

I'm totally lost....Can anyone show me how to do this?
Basically read in a file
Loop through it and at 10 spaces insert a TAB or coma
at 15 spaces insert another TAB or coma

Save the file out as an excel .xls or csv whichever will put these
records into A1, B1, C1, A2, B2, C2 etc ?

And if anyone is willing to do this could you document where the
TABS or comas are being put in and where the file is beting
re-written out?

thanks, in the mean time i'll keep playing with this.

Maybe the probelm is we don't know what exactly is the problem you are
having.

First, use System.IO.File.ReadAllLines to read all lines into an Array of
Strings.

I think how to write a loop is known. With every line, you can do what you
described above. Use the String's SubString method to get a part of a
String. Use the & operator to concatenate two strings. You can store the
result back in the array. After the loop is done, write the file back (I
suggest to a new file). This can be done with the
System.IO.File.WriteAllLines method.


Armin