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-Co...atedValue.html

<pa*****@gmail.comwrote in message
news:f5**********************************@a26g2000 prf.googlegroups.com...

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

[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.

On Nov 17, 6:06*pm, "James Hahn" <[e-mail]:

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-Co...atedValue.html

<[e-mail] in message

news:f5**********************************@a26g2000 prf.googlegroups.com...

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

[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.
<pa*****@gmail.comwrote in message
news:f4**********************************@1g2000pr d.googlegroups.com...
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.

On Nov 17, 6:06 pm, "James Hahn" <[e-mail]:

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-Co...atedValue.html

<[e-mail] in message

news:f5**********************************@a26g2000 prf.googlegroups.com...

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

[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

"eBob.com" <fa******@totallybogus.comwrote in message
news:eG**************@TK2MSFTNGP03.phx.gbl...

>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.
<pa*****@gmail.comwrote in message
news:f4**********************************@1g2000pr d.googlegroups.com...
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.

On Nov 17, 6:06 pm, "James Hahn" <[e-mail]:

>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-Co...atedValue.html

<[e-mail] in message

news:f5**********************************@a26g200 0prf.googlegroups.com...

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]

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".

<pa*****@gmail.comwrote in message
news:f4**********************************@1g2000pr d.googlegroups.com...
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]

On Nov 17, 8:08*pm, "eBob.com" <[e-mail]:

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

"eBob.com" <[e-mail] in message

news:eG**************@TK2MSFTNGP03.phx.gbl...

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.
<[e-mail] in message
news:f4**********************************@1g2000pr d.googlegroups.com...
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.

On Nov 17, 6:06 pm, "James Hahn" <[e-mail]:

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-Co...atedValue.html

<[e-mail] in message

>news:f5**********************************@a26g200 0prf.googlegroups.com....

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

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 :(

>

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]

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?

<pa*****@gmail.comwrote in message
news:36**********************************@w24g2000 prd.googlegroups.com...
On Nov 17, 8:08 pm, "eBob.com" <[e-mail]:

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]

<pa*****@gmail.comschrieb

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?

On Nov 18, 3:06*am, "Armin Zingler" <[e-mail]:

<[e-mail]

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

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

Armin

[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")

On Nov 18, 9:04*am, [e-mail] wrote:

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?

On Nov 18, 3:06*am, "Armin Zingler" <[e-mail]:

<[e-mail]

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

Help helps:http://msdn.microsoft.com/en-us/libr...tp://msdn.micr...

Armin

[Armin Zingler]

<pa*****@gmail.comschrieb

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

I can read in a file.

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

[cfps.Christian]

Start with reading in the file. Once you have your data in some kind
of easily manipulated format (i.e. User created objects, string
arrays, etc.) you can loop over the collection and put them into the
Excel spreadsheet.

As I commented on another thread there is a way to use ODBC to query
an existing spreadsheet and so also possible to use an Insert/Update/
Delete statement. At that point just treat the spreadsheet as a
database. Same drawback is that you can't format the columns
automatically.

Another option is to grab the Excel object model and attempt to use
that. Of all the Office interops its probably the best since Excel at
least somewhat behaves like a database table. With this you can do
formatting on cells/rows/columns, but the documentation is somewhat
spotty when you get into that.

[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()

<pa*****@gmail.comwrote in message
news:80**********************************@35g2000p ry.googlegroups.com...
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?

On Nov 18, 3:06 am, "Armin Zingler" <[e-mail]:

<[e-mail]

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

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

Armin

[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.
On Nov 18, 11:08*am, "cfps.Christian" <[e-mail]:

Start with reading in the file. *Once you have your data in some kind
of easily manipulated format (i.e. User created objects, string
arrays, etc.) you can loop over the collection and put them into the
Excel spreadsheet.

As I commented on another thread there is a way to use ODBC to query
an existing spreadsheet and so also possible to use an Insert/Update/
Delete statement. *At that point just treat the spreadsheet as a
database. *Same drawback is that you can't format the columns
automatically.

Another option is to grab the Excel object model and attempt to use
that. *Of all the Office interops its probably the best since Excel at
least somewhat behaves like a database table. *With this you can do
formatting on cells/rows/columns, but the documentation is somewhat
spotty when you get into that.

[Armin Zingler]

<pa*****@gmail.comschrieb

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