Convert cvs file to excel file

J

Jim

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?
 
K

Ken Wright

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.
 
M

Myrna Larson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert CVS files on Excel 1
convert to cvs(,) from excel 4
Importing CVS files into Excel 2
Excel and CVS 2
CVS files 1
converting txt and cvs files 1
changing word files to excel files 2
Formula for deleting " 1

Top