How do I convert Excel data to comma seperated txt?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Mitchell - 31 Mar 2008 12:06 GMT

I have to convert an excel file into a .txt file, which I can do, however I
also need to enclose the entries in "double quotes".

The data in the original excel file looks as follows:

Column 1 Column 2
AB/LN/UC CE/CRS
AB/LN/UC CVL/MST
AB/LN/UC GW/EMD

I need the data in the .txt file needs to look as follows:

"AB/LN/UC","CE/CRS"
"AB/LN/UC","CVL/MST"
"AB/LN/UC","GW/EMD"

I don't want to have to do a load of concatenation to arrive at this over
4.5K rows.

Is there a standard way of doing this in excel?

If yes how?

Can it be done via access?

If yes how?

TIA.

Reply to this Message

Pete_UK - 31 Mar 2008 12:42 GMT

Why don't you save it as a .csv file (File | Save As and then select
CSV in the file type box), and then rename it from .CSV to .TXT in
Explorer?

Hope this helps.

Pete

On Mar 31, 12:06 pm, "Chris Mitchell"
<chris.a.mitch...@NOSPAMbtinternet.com> wrote:

> I have to convert an excel file into a .txt file, which I can do, however I
> also need to enclose the entries in "double quotes".
[quoted text clipped - 24 lines]
>
> TIA.

Reply to this Message

Chris Mitchell - 31 Mar 2008 13:18 GMT

As I understand it this would return

Column 1 Column 2
AB/LN/UC,CE/CRS
AB/LN/UC,CVL/MST
AB/LN/UC,GW/EMD

As opposed to

Column 1 Column 2
"AB/LN/UC","CE/CRS"
"AB/LN/UC","CVL/MST"
"AB/LN/UC","GW/EMD"

including "double quotes" which is what is required.

Or have I missed something?

Why don't you save it as a .csv file (File | Save As and then select
CSV in the file type box), and then rename it from .CSV to .TXT in
Explorer?

Hope this helps.

Pete

On Mar 31, 12:06 pm, "Chris Mitchell"
<chris.a.mitch...@NOSPAMbtinternet.com> wrote:

> I have to convert an excel file into a .txt file, which I can do, however
> I
[quoted text clipped - 25 lines]
>
> TIA.

Reply to this Message

Pete_UK - 31 Mar 2008 13:50 GMT

Okay,

insert a new column A and fill it with a character that you will not
have in the rest of your data, eg the pipe or underscore character.
Similarly, fill the column next to your last column with this
character. So your Excel display would look like this:

_ AB/LN/UC CE/CRS _
_ AB/LN/UC CVL/MST _
_ AB/LN/UC GW/EMD _

and when you save it as a CSV file it will be like this:

_,AB/LN/UC,CE/CRS,_
_,AB/LN/UC,CVL/MST,_
_,AB/LN/UC,GW/EMD,_

Open this in Notepad and do Find/Replace (CTRL-H) 3 times, as follows:

Find What: _, (underscore comma)
Replace With: "
click Replace All

Find What: ,_ (comma underscore)
Replace With: "
click Replace All

Find What: , (comma)
Replace With: ","
click Replace All

The file should now look like:

"AB/LN/UC","CE/CRS"
"AB/LN/UC","CVL/MST"
"AB/LN/UC","GW/EMD"

Then save your file as .txt

Hope thishelps.

Pete

On Mar 31, 1:18 pm, "Chris Mitchell"
<chris.a.mitch...@NOSPAMbtinternet.com> wrote:

> As I understand it this would return
>
[quoted text clipped - 56 lines]
>
> - Show quoted text -

Reply to this Message

MartinW - 31 Mar 2008 13:48 GMT

Hi Chris,

I know little about this sort of thing, but
does this do what you want.

With your data in cols. A and B put this
in C1 and drag down as far as needed.

=""""&A1&""""&","&""""&B1&""""

Then copy col C to another place with
Paste Special>Values and then copy
that col to Notepad.

HTH
Martin

>I have to convert an excel file into a .txt file, which I can do, however I
>also need to enclose the entries in "double quotes".
[quoted text clipped - 24 lines]
>
> TIA.

Reply to this Message

Earl Kiosterud - 09 Apr 2008 04:01 GMT

Chris,

Try the Text Write Program at www.smokeylake.com/excel. In sheet Setup, set "Bracket all
fields" to yes.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>I have to convert an excel file into a .txt file, which I can do, however I also need to
>enclose the entries in "double quotes".
[quoted text clipped - 23 lines]
>
> TIA.

Reply to this Message