How do I convert Excel data to comma seperated txt?

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. 


0
3/31/2008 11:06:11 AM
excel 39879 articles. 2 followers. Follow

5 Replies
353 Views

Similar Articles

[PageSpeed] 57

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=A0pm, "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".
>
> The data in the original excel file looks as follows:
>
> Column 1 =A0 =A0 =A0Column 2
> AB/LN/UC =A0 CE/CRS
> AB/LN/UC =A0 CVL/MST
> AB/LN/UC =A0 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.

0
pashurst (2576)
3/31/2008 11:42:55 AM
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?


"Pete_UK" <pashurst@auditel.net> wrote in message 
news:159a4345-ac2e-43f2-b682-5668f2eb6ab7@c26g2000prf.googlegroups.com...
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".
>
> 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.


0
3/31/2008 12:18:55 PM
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


"Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:OYkM48xkIHA.5660@TK2MSFTNGP02.phx.gbl...
>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.
> 


0
mtmw (348)
3/31/2008 12:48:59 PM
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=A0pm, "Chris Mitchell"
<chris.a.mitch...@NOSPAMbtinternet.com> wrote:
> 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 =A0 =A0 =A0Column 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?
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:159a4345-ac2e-43f2-b682-5668f2eb6ab7@c26g2000prf.googlegroups.com...
> 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, howeve=
r
> > 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 ove=
r
> > 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.- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
3/31/2008 12:50:28 PM
Chris,

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

-- 
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Chris Mitchell" <chris.a.mitchell@NOSPAMbtinternet.com> wrote in message 
news:OYkM48xkIHA.5660@TK2MSFTNGP02.phx.gbl...
>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.
> 


0
someone798 (944)
4/9/2008 3:01:45 AM
Reply:

Similar Artilces:

can u convert a mac font to pc for me? (reply and i'll send it to ur e mail)
I doubt anyone will respond positively to your request. Fonts are usually copyrighted, and converting them would be a violation of that copyright. This is especially true if the font is a commercial font. Can you not find a Mac equivalent? I believe I've run across freeware converters for just this purpose. You might try versiontracker. I know I found something to convert some fonts I absolutely needed in a bind (but I have since forgotten). "serdar" <serdarsoy@yahoo.com> wrote in message news:elqKvj0cFHA.1356@TK2MSFTNGP10.phx.gbl... > > In addition,...

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Gathering and adding data from different rows
Hi all, I run a report at work every day that tells me how many shares of a company have been sold and from what year these shares were granted to the participant. The year and the amount of shares are on different lines. For instance, the year would be on D5 and the share amount would be on D10. This is a daily function and the data changes every day. I am required to manually scan these reports and add up the shares for each year separately. I have to separate the amounts for 2003, 1997 and 1996. I want to create a spreadsheeet that I can dump this data into and have the spreadsheet find...

excel process in the background
I'm trying to find a way of updating the excel spreadsheet without the excel application coming up on the users PC. We are getting input from the WEB and using that input to run a model and capture the data in an excel spreadsheet. Then manipulate the spreadsheet and create a summary Word document that will be emailed to the user. Then the user will be emailed with the summary data from the excel document. The thing is that this will be running on some server and I don't want the excel spreadsheet or application to actually come up on the PC. How can I suppress the applicati...

Excel 97 #11
How can I change a spreadsheet , so that it opens on sheet 1 every tim someone goes into it , when working on the spreadsheet, it will ope the last page that a save was done on. Thanks -- Message posted from http://www.ExcelForum.com Your 1st question. Save at the sheet desired or use an auto_open macro in a regular module or a workbook_open event in the ThisWorkbook module that uses sheets("yoursheetname").select -- Don Guillett SalesAid Software donaldb@281.com "EADesktop >" <<EADesktop.163m60@excelforum-nospam.com> wrote in message news:EADesktop.163m6...

Adding a column in Excel 2007
Have a column with letters in it. Want to sum up how many of one letter I have in the column. What is a formula to do this please To count the number of cells that equal X =countif(a:a,"X") To count the number of cells that have at least one X in them: =countif(a:a,"*X*") nip wrote: > > Have a column with letters in it. Want to sum up how many of one > letter I have in the column. What is a formula to do this please -- Dave Peterson On Dec 10, 10:44=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote: > To count the number of cells that eq...

Line graphs; excel 2007
I have a simple line graph - and I'd like to shade in the area btwn the two lines only (not the whole background of the graph). Any suggestions? Apply Fill Color Between Lines in a Chart http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=590 - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ MK09 wrote: > I have a simple line graph - and I'd like to shade in the area btwn the two > lines only (not the whole background of the graph). Any suggestions? ...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto another sheet. Any simple macros? Thanks. Amy How about Sub Test() Source_Sheet = "Sheet1" Target_Sheet = "Sheet2" n = 5000 ' your last line of data on Sheet1 Target_Row = 1 Sheets(Target_Sheet).Select For nCount = 1 To n Step 60 Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy Worksheets(Target_Sheet).Cells(Target_Row, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ F...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

Is is me? Or is Excel 2007 Charting Lame?
Maybe I'm missing something. If so, let me know. The process of creating a chart is unintuitive and cumbersome. A big step backwards from 2003. The manual says to just select your data and insert the chart. What I find is that 95% of the time, I have to re-do the data selection. If I highlight two columns and insert the graph, Excel thinks both columns are two different series instead of x and y axis. I create two columns, label one of them "X", one of them "Y". Put some numbers below that. Now insert an XY chart. One would think that it could figure out "...

Shared use of excel work books
Hi, Can anyone tell me "if I have an excel workbook (with multiple worksheets that report to the first worksheet) on a shared drive whether it can be set up for multiple users to access it and use it at the same time?" These users will be accessing their single worksheet to update it periodically with the first worksheet being the project leaders sheet where he gets to see all the other worksheets on his one sheet. -- Ian Hi Ian, You can do this by selcting "shareworkbook" from the tools menu and checking the 'allow changes by more than one use'. Beware th...

EXCEL 2003
hi Im performing Logical boolean operations using MS Excel. The only problem is that its displaying result set as "TRUE/FALSE" and I want to have the result in binary. Is there a way to convert that text "TRUE" into digit "1"? please tell me ASAP. thank "imfaizzi" <imfaizzi@discussions.microsoft.com> wrote: > Is there a way to convert that text "TRUE" into digit "1"? =--(A1>A2) returns 1 for TRUE and 0 for FALSE. Similarly for =1*(A1>A2) "imfaizzi" <imfaizzi@discussions.microsoft.com> wrote ...

Microsoft Excel ASC II Text Files
How do I convert an Excel spreadsheet into this type of file in order to import to another program such as "MaiList & AddressBook" Hi Bill try saving as 'CSV' file in the Save As dialog. This is a comma separated text (ASCII) file HTH Frank Bill Cadwallader wrote: > How do I convert an Excel spreadsheet into this type of > file in order to import to another program such > as "MaiList & AddressBook" ...

Is Monotype Sorts Font available in Excel 2003(Microsoft Office S.
I am using Excel 2003 Small Business Edition. Excel was already loaded on my computer when I purchased it. However, Monotype Sorts font is not an available font selection. Can anyone tell me if it is available and if so how can I add it as an available font? Thanks, Think you'll need to buy it. Search the Internet for various sites offering the font. Regards Trevor "Tim R" <Tim R@discussions.microsoft.com> wrote in message news:04DE3FE9-9CE4-4F6C-8D87-9136C4925BB5@microsoft.com... >I am using Excel 2003 Small Business Edition. Excel was already loaded on ...

Excel Services Publish
I have a 64 bit client (Windows Server 2008) and my colleague has Windows 7 64 bit client. We are trying to publish an Excel 2007 document to MOSS, which is running under https, and is sitting on a 64 bit operating system. We keep getting the "File Not Saved" error when we try to publish from our clients. However, a 3rd colleague has Windows XP 32 bit and can publish the same exact Excel files just fine up to the very same MOSS location we tried on the 64 bit clients. Can someone from MSFT confirm that this is an issue with 64 bit clients? Can you present a solut...

Excel pivot tbl
Brand new laptop w/Windows XP. Has Office 97 installed (first), than complete install of Office XP(second). Than install of PTSfull.exe (pivot table services that come w/ sql server analysis services service pack 2). Go to data manu - PivotTable and Pivot Chart report - External data source/Pivot table - Get Data - OLAP cubes - new data source - select an olap provider for the database you want to access - blank dropdown (no Microsoft OLEDB Provider for OLAP Services or Microsoft OLEDB Provider for OLAP Services 8.0). Tried removing Office 97 & Office XP than installing PTS - sa...

Date formatting in Excel #2
How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I type 10-4, Excel returns Oct 05. Hi When no year is typed, excel would assume it is the current year. You would ahve to type the full date (including 2004) for it to display as Oct 2004. Use Format - cells - date and then select the particular format you want from the options there. >-----Original Message----- >How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I >type 10-4, Excel returns Oct 05. >. > ...

excel #136
I have files in quantrum pro and would like to open them in excel. But they won't popen I'm not familiar with Quantrum Pro. What does it do? "Liz R" wrote: > I have files in quantrum pro and would like to open them in excel. But they > won't popen ...

Excel worksheet merge
Hello all, I have searched google, but to no avail. Here goes. I have one set of excel worksheets (one is an exact link to the other) that I copied four times for a total of five. I moved the 1st worksheet of each to an individual folder. So what I am left with are the 5 link worksheets. Can I merge the links together? I have an Access db that I will use to process the info. I have linked the links to an Access db and it works. Except I have 5 dbs instead of the one that I really need. The fields are the same in all linked dbs. My question is- should or can I merge before access and i...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

Templates Excel XP
Where can I find templates to download to Excel XP. I'm looking for templates for both business and personal use http://office.microsoft.com/en-us/default.aspx -- Regards, Peo Sjoblom (No private emails please) "sfrich1" <sfrich1@discussions.microsoft.com> wrote in message news:B7C932BB-3DE7-440C-B4E0-511EC8527F07@microsoft.com... > Where can I find templates to download to Excel XP. I'm looking for > templates for both business and personal use If you forget the address in the future, go to Excel's menu, and select Help | Microsoft Office Onl...

retaining fonts when placing excel worksheet into pagemaker?
I am currently placing excel worksheets into Pagemaker software. I am having particular trouble with retaining the fonts in the text cells. When it appears in pagemaker the spacing is wierd and the numbers eg.(12/8) are crammed together. I used excel 2003. I am not a pagemaker wizard soooooooooo please help. Cindy I think PageMaker will let you Edit-->Paste special-->as a picture, won't it? Sorry, it's been a long time... ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "parrydise" <parrydise@discussions.microsoft.com> wrote i...