topic Re: Ascii File to Excel Data Import in Operating System - Linux

Ascii File to Excel Data Import

Anurag_7 — Tue, 09 May 2006 12:26:22 GMT

Hi all,

I have a file which is of the form (Where all the alphabets denote decimal numbers)

A,B,C,D
E,F,F,F
.
.
.
A,D,D,F

Is there a way to import this files contents to a Excel file such that each comma separated number occupies a cell in a row and each line in the file takes a new row in the excel file?

Thanks a lot for your help on this.

Regards,

Anurag

Re: Ascii File to Excel Data Import

Victor BERRIDGE — Tue, 09 May 2006 12:34:57 GMT

Hi,
Edit your file or use sed to substitute , with ; e.g. under vi :%s/,/;g

save under another name.csv
Try
(I just did, works no problem...)

All the best
Victor

Re: Ascii File to Excel Data Import

Rodney Hills — Tue, 09 May 2006 12:47:43 GMT

Just name the file with a ".csv" extension. When opened by Excel, it will automatically be parsed out with each comma seperated item in its own cell.

HTH

-- Rod Hills

Re: Ascii File to Excel Data Import

Anurag_7 — Tue, 09 May 2006 12:51:30 GMT

Thanks Rodney and Victor.

Both the approach are pretty good.

Thanks again

Anurag

Re: Ascii File to Excel Data Import

Anurag_7 — Tue, 09 May 2006 12:52:22 GMT

All the solution were damn good......

Re: Ascii File to Excel Data Import

H.Merijn Brand (procura — Tue, 09 May 2006 12:54:55 GMT

Excel and CSV are two formats that are both well-defined and portable
The worse is the fact that we have to conclude that M$ has never been able to make a reliable CSV import. They just fuck up all the time.

An example

make a file like:

1,"a",12-11-2004,11/12/2004,12112004,20041112,2004-11-12,"20041112","12112004"

Then take the three approaches of loading that file in Excel:

1. name it test.csv and double click
2. name it test.csv and use Alt-F Open
3. name it test.csv and import from data

Note the differences? Yes, all different
Now save it as CSV. Note that the C in CSV stands for Comma. Now look at the saved file.
Still like M$ software? I don't. They've used SEMI-colons as field seperators.

The only reliable solution is to do it yourself, using perl and Text::CSV_XS and SpreadSheet::WriteExcel

I wrote the script 'csv2xls', which I now attached. Use and modify it to your liking.

Enjoy, Have FUN! H.Merijn [ not enjoying CSV in Excel ]

Re: Ascii File to Excel Data Import

H.Merijn Brand (procura — Tue, 09 May 2006 12:56:50 GMT

IMHO that was too fast a close for the thread. I was not able to type any faster, and when I started to post, there were no answers yet.

Enjoy, Have FUN! H.Merijn

Re: Ascii File to Excel Data Import

Anurag_7 — Tue, 09 May 2006 13:52:53 GMT

Hi Procura,

I am sorry for closing the thread so fast :-)

However, i would definitely like to try out your script.

However, when i download the script, i get lots of junk characters inside the file.

I was wondering if it's possible for you to send the script file as a text attachment to me at achourasia@manh.com

Waiting eagerly for ur mail.

Thanks a lot for taking out time for me.

Anurag