Convert cvs file to excel file

the CVS file has 1000 records seprated by a comma when I 
try to open with excel it only take 256 records and places 
them in a single row I need to have all 1000 records 
placed in a single column is there a way to convert this 
file?
0
jparango (1)
8/25/2003 5:25:28 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
703 Views

Similar Articles

[PageSpeed] 32

Open it in Notepad/Wordpad etc and I'm assuming from your text that you will see something like
the following:-

17,12,20,19,5,14,20,5,11,3

Do an Edit / Replace and replace , with ;  (Replace comma with semi-colon) so that you now have

17;12;20;19;5;14;20;5;11;3

Copy the string and paste into a cell in Excel (Assume cell A1)

Now put ={ at the front and a } at the back, so that you have the following:-

={17;12;20;19;5;14;20;5;11;3} and hit enter

Select A1:A1000 and then click into the data itself in the formula bar, and then hit
CTRL+SHIFT+ENTER which should put all the entries in individual cells in a single column.  Now
just copy the column and then paste special as values.

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Jim" <jparango@yahoo.com> wrote in message news:09c701c36b2d$e10976a0$a001280a@phx.gbl...
> the CVS file has 1000 records seprated by a comma when I
> try to open with excel it only take 256 records and places
> them in a single row I need to have all 1000 records
> placed in a single column is there a way to convert this
> file?


0
ken.wright (2489)
8/25/2003 6:51:57 PM
In a CSV file, each record (row) appears on a line by itself. Within that line, the values
separated by commas go into the columns of that row.

If you have 1000 items, with commas between them, you need to convert all of the commas to
carriage return/line feed pairs. The easiest way to do this would be to open the file in Word,
use search and replace to replace "," with "^p" (both without the quotes) then be sure to save
as a text file, not a Word document, and open it in Excel.

BTW, the above will work ONLY if you have no data fields that are surrounded by double quotes
because they contain embedded commas. i.e. if the file looks like

1,2,3,"John Jones, Sr.",5,6,7

it isn't going to work. The comma that's part of the name will be interpreted as an
end-of-record marker.


On Mon, 25 Aug 2003 10:25:28 -0700, "Jim" <jparango@yahoo.com> wrote:

>the CVS file has 1000 records seprated by a comma when I 
>try to open with excel it only take 256 records and places 
>them in a single row I need to have all 1000 records 
>placed in a single column is there a way to convert this 
>file?

0
myrnalarson (223)
8/25/2003 7:24:20 PM
Reply:

Similar Artilces:

excel won't launch
all other MS Office apps work fine. all of a sudden i can't launch excel. it says it's trying to configure Microsoft Office Professional with Front Page, then it fails and asks for the disk, which my computer did not come with or i can't find. i have Office 2002. then i get error 1706. please help and thanks, richard wheeler "Richard Wheeler" <RichardWheeler@discussions.microsoft.com> wrote in message news:E72266D8-90BF-4372-98A0-8F9E82B5532B@microsoft.com... > all other MS Office apps work fine. all of a sudden i can't launch excel. > it > s...

Can't get money 2002 to open money 98 backup file.
I keep getting an message saying either it isn't a money file or the file is corrupted. I did this over and over on diff. floppies. Still the same. It is on our biz laptop we backup money 98 to a floppy, then to our pc in the office we try to restore and no go. Any clues? Pretty much tried everything possible here. Thanx Ben In microsoft.public.money, Ben wrote: >I keep getting an message saying either it isn't a money >file or the file is corrupted. I did this over and over on >diff. floppies. Still the same. It is on our biz laptop we >backup money 98 to a flo...

Error in Excel 03: "Cannot cut part of a publisher." Help
I keep getting the following error message when I cut and try to paste anything in an Excel 2003 Spreadsheet- "Cannot cut part of a publisher." What is this message and what do I need to do to cut and paste in this document. ...

how to down load tv ads and play them in office excel
How do I down load T.V. ads, and then replay them etc....Tony Why do you want to play a video in Excel? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "TLC-FCUK" <TLC-FCUK@discussions.microsoft.com> wrote in message news:C15A9213-F702-4AA4-95CA-4C682528F9B6@microsoft.com... > How do I down load T.V. ads, and then replay them etc....Tony use a VCR -- Gary''s Student gsnu200705 "TLC-FCUK" wrote: > How do I down load T.V. ads, and then replay them etc....Tony ...

Reading Publisher file after burning to CD
After a Publisher file is burned to a CD, error message says Publisher cannot find that file. ...

How do I keep from printing blank pages in excel?
I'm trying to print only rows containing nonzero values in a specific column. I can filter the data to show only rows with nonzero values but I am then forced to reset my print area or I get blank pages. Is there an easier way? Thanks. You could press Ctrl-* to select just the current region, then use Print Selection, rather than simply Print. You can probably even record a macro incorporating those steps to speed it up. "Davbwalker" wrote: > I'm trying to print only rows containing nonzero values in a specific column. > I can filter the data to show only r...

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

"Functions Displaying", & Old Excel 97 ?
Hello, Really embarased to ask, but: Using an old copy of Excel "97" Where do I find the list of Functions that are available (on what pull-down menu), and how do I display them, please ? Just can't seem to find them on any of the items that displays. How can I get an icon to allow easy access to them on one of the toolbars ? Thank you, B. Excel's help is nice(?). Look for Functions. and... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's si...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

Pocket Excel files (.pxl) do not open in Excel 2003 on XP desktop
I just tried to open a Pocket Excel file on my Windows XP desktop and the file could not be opened. Has anyone had any luck opening Pocket excel files on a desktop PC without converting them? Pocket Excel I believe is the .pxl format that the Handheld PC and the PocketPC use. I may want to password protect files and send them to a party that does not have a PocketPC. Can this be done, or do I need to convert the files to MS excel and password protect a seperate .zip file using resco File explorer? I cannot send any files to this party that are not password protected for the risk is to...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

CHm file format
Hello, I have seen your's reference on code project. I am new to CHM format and i need to create a CHM viewer. I have some queries regarding the same a) what is binary TOC and binary index ( I have been able to parse the HHc and HHk fie). How i should parse the same b) What are merged files. How i can read them c) How how CHI, CHw and CHQ files can be created. How reading of these files will be different form the reading CHM file. Please help me out by answering my queries. Thanks, Ritu ...

How do I bring up a file that I saved as an Excel file?
I am taking classes and have to do work on Excel and was wondering why when I save them I can't retrieve them? I can see the file names but am not able to retrieve them I am inexperienced on Office. Hi open excel, choose file & open, locate your files, click on the one you want to open and press the Open button. however, if the files that you want to open are on a floppy disk then it is a VERY good idea to copy them onto your hard drive before opening them. This is best achieved using windows explorer. to do this: right mouse click on the start button choose explore ensure the flo...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Attach file vs insert file
I have a user who is trying to attach a file to an email (Exchange 2003 SP2, Outlook 2003) using the paperclip icon. Instead of attaching to the email (adding an Attach: line under the Subject line) Outlook is inserting the file into the body of the email. Any suggestions? TOP wrote: > I have a user who is trying to attach a file to an email (Exchange 2003 SP2, > Outlook 2003) using the paperclip icon. Instead of attaching to the email > (adding an Attach: line under the Subject line) Outlook is inserting the > file into the body of the email. > > Any suggestions?...

Macros in excel
how to find first empty cell in a selected column ie A12:A20 with a macro This should do it. Hope this helps! If so, let me know, click "YES" below. Sub FindFirstEmptyCell() MsgBox Range("A12:A20").End(xlDown).Offset(1) End Sub -- Cheers, Ryan "plugger" wrote: > how to find first empty cell in a selected column ie A12:A20 with a macro You can use the End property of a range- this is the same as holding down Ctrl + [Down/Up Arrow]. If you are sure that there is one continuous range of non-blank cells in the column then it is more r...

Excel file save macro.
I need help with a macro. I want to be able to save a file as a listed name, that is referenced in a cell, in a directory that is listed in a different cell. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Post your efforts. Homework? "darrenlshipley" <darrenlshipley.vecxb@excelforum-nospam.com> wrote in message news:darrenlshipley.vecxb@excelforum-nospam.com... > I need help with a macro. I want to be able to save a file as a listed > name, tha...

Sharing a excel document over the internet
Hey everyone, I am looking to improve a few things with a client of mine. I have an excel worksheet(office 2000, but we coudl also purchase office xp if needed) which contains work that i have to do for my client. What i would like to do is setup an excel doc that would inform us(depending who modifies the document) that there were changes made (like in outlook or something). Example: he writes that he wants me to install 2 new computers in section d(whatever) of the office. HE then saves the excel file, and outlook automatically sends me a notice that it has been modified. I then double ...

Cannot double click an excel file and open it.
Hi When I double click an excel file, the excel application opens with a grey screen. If I drag the file onto the grey screen it opens fine. If I double click an excel attachement the excel screen opens grey and I get a message box saying The system cannot find the file specified. If I drag an excel file into the outlook inbox and then double click on it, it launches as it would normaly. I have un associated and re associated the file with Excel. I have checked the selections under the window menu in Excel, but unhide is greyed out, indicating that excel has opened, but not loaded the fil...

how to convert excel's .cvf file to .csv file
...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

How to open excel with in IE
hi, i want to open excel with in IE and shud be able to control the object using script. If i use createobject, it opens up a MS-Excel separately. is there a way to embed excel with in IE. balaji ...

Files Publisher cannot open.
After creating files in Publisher 2003, most will not open. I get the message"Publisher cannot open this file". ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=cbbcbdd8-ef0a-412a-9a74-65ca4bb16...