Help with Comma Delimited Files

[Charles May]

I have a file that I had to import into excel and separate the City and
State so that I can re-import it into another package.

The original file had each field enclosed in quotes

I had "Albany NY" and I used excel to create Albany in one column and NY in
another

When I save it the fields are comma delimeted as needed however, the
original file had all fields
enclosed in quotes. The CSV file had Albany,NY but the software will not
accept these files unless each field is enclosed in quotes. Is there any way
to save the CSV with each field in the text files enclosed in quotes.

After saving the file to a .CSV file, I rename the extension to .txt (the
only extension that is supported)

When I double click on the file my columns are listed as
Albany,NY

I need them to be:
"Albany","NY"

Is this possible during saving the file or will I need to go in and re-do
this by parsing out the fields and enclosing them in quotes?

Any help is appreciated

Thanks
Charlie

 

[Dave Hawley]

Hi Charlie

Coud you parse them from the original with

First Word:
=TRIM(LEFT(A1,FIND(",",A1)-1)&"""")
and
Second Word:
="""" &TRIM(MID(A1,FIND(",",A1)+1,256))

Copy down, then copy and Paste Special as values to remove the formulas?


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Peterson]

maybe you could use a couple of helper cells:

I'd build a nice string in the first:

="@"&A1&"@,@"&B1&"@,@"&C1&"@"
(concatenates a1, b1, c1)

then use another formula that changes the @ to "'s.
=SUBSTITUTE(D1,"@","""")

Then I'd copy that column and paste into notepad and save from there.

You could write your own code if you wanted to:

I'd try to steal as much as possible from one of these samples:

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(You may want to try Earl's. It may be sufficient as is.)

 

[Charles May]

Thanks Guys,
I thought maybe Excel could automate this in the saving of the CSV.
I'm in the process of converting in VB to rebuild the data enclosed in
quotes.

Thanks for the quick reply though

Charlie

 

[Charles May]

Actually,
I decided to go your route and on mcgimpsey site, I found exactly what I was
looking for. In less than 5 minutes I had everything converted.

Thanks Guys, especially for the links

Charlie

 

[Dave Peterson]

JE does nice work.

Actually,
I decided to go your route and on mcgimpsey site, I found exactly what I was
looking for. In less than 5 minutes I had everything converted.

Thanks Guys, especially for the links

Charlie