How do I convert Excel data to comma seperated txt?

[Chris Mitchell]

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.

 

[Pete_UK]

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

 

[Chris Mitchell]

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

 

[MartinW]

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

 

[Pete_UK]

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

 

[Earl Kiosterud]

Chris,

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