convert text file to excel ?

[pakerly@gmail.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]

Re: convert text file to excel ?

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@gmail. comwrote in message
news:f5602786-6822-44c3-95a7-6137f31872f5@a2 6g2000prf.googl egroups.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@gmail.com]

Re: convert text file 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.

On Nov 17, 6:06 pm, "James Hahn" <jh...@yahoo.co mwrote:

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
>
<pake...@gmail. comwrote in message
>
news:f5602786-6822-44c3-95a7-6137f31872f5@a2 6g2000prf.googl egroups.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]

Re: convert text file to excel ?

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.
<pakerly@gmail. comwrote in message
news:f4d5efd3-4a31-4709-9e15-317207540415@1g 2000prd.googleg roups.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" <jh...@yahoo.co mwrote:

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
>
<pake...@gmail. comwrote in message
>
news:f5602786-6822-44c3-95a7-6137f31872f5@a2 6g2000prf.googl egroups.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]

Re: convert text file to excel ? (continued)

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" <fakename@total lybogus.comwrot e in message
news:eGk0zkRSJH A.1164@TK2MSFTN GP03.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.
<pakerly@gmail. comwrote in message
news:f4d5efd3-4a31-4709-9e15-317207540415@1g 2000prd.googleg roups.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" <jh...@yahoo.co mwrote:

>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
>>
><pake...@gmail .comwrote in message
>>
>news:f560278 6-6822-44c3-95a7-6137f31872f5@a2 6g2000prf.googl egroups.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]

Re: convert text file to excel ?

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

<pakerly@gmail. comwrote in message
news:f4d5efd3-4a31-4709-9e15-317207540415@1g 2000prd.googleg roups.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@gmail.com]

Re: convert text file to excel ? (continued)

On Nov 17, 8:08 pm, "eBob.com" <faken...@total lybogus.comwrot e:

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" <faken...@total lybogus.comwrot e in message
>
news:eGk0zkRSJH A.1164@TK2MSFTN GP03.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.
<pake...@gmail. comwrote in message
news:f4d5efd3-4a31-4709-9e15-317207540415@1g 2000prd.googleg roups.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" <jh...@yahoo.co mwrote:

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

>

<pake...@gmail. comwrote in message

>

>news:f560278 6-6822-44c3-95a7-6137f31872f5@a2 6g2000prf.googl egroups.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]

Re: convert text file to excel ? (continued)

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?

<pakerly@gmail. comwrote in message
news:36a001f9-3af8-4cc3-9995-1dc181da11e8@w2 4g2000prd.googl egroups.com...
On Nov 17, 8:08 pm, "eBob.com" <faken...@total lybogus.comwrot e:

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]

Re: convert text file to excel ? (continued)

<pakerly@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@gmail.com]

Re: convert text file to excel ? (continued)

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" <az.nos...@free net.dewrote:

<pake...@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/libr.../ws92aysc.aspx
>
Armin

[pakerly@gmail.com]

Re: convert text file to excel ? (continued)

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.WebC lient
txtbox1.Text = myclient.Downlo adString("C:\Do cuments and
Settings\user\D esktop\mytestfi le.txt")

On Nov 18, 9:04 am, pake...@gmail.c om 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" <az.nos...@free net.dewrote:
>

<pake...@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/libr...tp://msdn.micr...

>

Armin

[Armin Zingler]

Re: convert text file to excel ? (continued)

<pakerly@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]

Re: convert text file to excel ? (continued)

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]

Re: convert text file to excel ? (continued)

NOT tested or checked.

Dim sw as New StreamWriter ("C:\Documen ts and
Settings\user\D esktop\myEXCELf ile.txt")
Dim a() as string
a = Split(txtbox1.t ext, vbCRLF)

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

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

<pakerly@gmail. comwrote in message
news:8023508d-ef5f-4cdb-b1ce-6dad15bcf58a@35 g2000pry.google groups.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" <az.nos...@free net.dewrote:

<pake...@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/libr.../ws92aysc.aspx
>
Armin

[pakerly@gmail.com]

Re: convert text file to excel ? (continued)

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 " <ge0193...@otc. eduwrote:

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.