Converting .csv file to .xls file

Hi. I am working on Autolisp in which I have generated one csv file (excel). Now, my task is to convert .csv file into .xls file using Auotlisp itself. But, I don't know what to do. Please help me to get over this. Thank you in advance.

Why wouldn't you just open Excel and import the .cvs file?

Version 2 rather than trying to create a xls why not open a excel session and write directly to it like Remark then save as xls. Plenty of examples here at Cadtutor.

Thank you all for your reply. Sir, please guide me in how can I import values of csv into xls. The file I am generating (csv) contains coordinates which are saved in different cells. So please help me some more regarding importing those values to xls file to move further in my work.

Just open CSv in excel if you want elsewhere in another excel cut and paste to correct location. I guess 10 seconds to do.

Sir, but I have to do it, with autolisp and without opening the csv file.

Ok, sir I have got one logic. Please tell me that if I want to read the value of a cell in B2 of csv file. Then, I want to write in a cell C3 of xls file. So, please help in terms of code, that what I can do regarding it. Please help me. If I simply get this idea then my whole problem will get solved.

Import or export text (.txt or .cvs) files.

https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-e8ab9ff3-be8d-43f1-9d52-b5e8a008ba5c?CorrelationId=75f8088b-73bf-49c9-85ef-a14bb0a436aa&ui=en-US&rs=en-US&ad=US

Sir, it is not working.

Please tell me that if I want to read the value of a cell B2 of csv file. Then, I want to write in a cell C3 of xls file. So, please help in terms of code, that what I can do regarding it. Please help me. If I simply get this idea then my whole problem will get solved. Please please please.

The Autocad to excel is not one of the simplest things to do, but it is achieveable, if you have no programming skills in say lisp then it will be hard for you. There are so many different ways and combinations of things that people want that there is not just 1 answer. Have a search here and there is a vast amount of examples read the post comments and find one that sounds like it does what you want post its page link here and then we can probably help you.

If you have some lisp skill then find "getexcel.lsp" it has a library of functions to and from Autocad.

Thank you for your response.

I request all the giants of autolisp to come forward and help me. It really matters for me.

My need is just simple:

1. To read the value from cell B3 of the csv file, and

2. To write that value to cell C3 of xls file.

If anyone can do this, then please help me. I badly need this.

1. To read the value from cell B3 of the csv file, and

2. To write that value to cell C3 of xls file.

sorry for interrupting..

hi sonali, do you mean 2 different files

read csv write to xls?

if your out-put is just acsii text with delimiter, i prefer csv.

it's easier & faster than using xls format which need import type-library.

i suggest you post an example data in .csv or output

Thank you sir, for coming forward to help me.

Yes sir, two different file.

Read csv and write it xls.

Now I will explain initially what problem I am facing in csv file:

1.The data I am getting in csv file, I have to process them by mathematical formulas.

For example, for summation of data availbale in the cell B1 to B4.

i.e. ( =SUM (B1:B4) ).

Now, these cannot be done in csv files. These operations can only be performed in xls file.

So, after getting the data from particular program, the csv file is generated.

Now, what I want to do is to get the data transferred from csv file to xls file. So, that I can process the data available in xls file.

So, if whole file can simultaneously be transferred from csv to xls then it will be awesome.

Or at least, I am in search of code by which, I can exactly read the value of a perticular cell in csv file. And write that value exactly to the destination cell of the xls file.

Please help me something about it.

Here I am attaching my csv and xls file. So, that you can visualize them.

Thank you again.

csv file.csv

xls file.xls

Your both csv & xls same data?

why not just open csv with Excel, edit or formulas etc..

then Save As xls?

cannot be done in csv files. These operations can only be performed in xls file...

 

summation can be done in list:

example: from your data.

(setq data

'(

"3,5,5"

"5,7,1"

"84,7,7454"

"5454,7,4"

"4,1,48"

"44,7,4"

"5,54,444"

))

 

 

split list as rows

'(("3" "5" "5") ("5" "7" "1") ("84" "7" "7454") ("5454" "7" "4") ("4" "1" "48") ("44" "7" "4") ("5" "54" "444"))

 

Transposed list as columns

'(("3" "5" "84" "5454" "4" "44" "5") ("5" "7" "7" "7" "1" "7" "54") ("5" "1" "7454" "4" "48" "4" "444"))

 

then can use list manipulating functions: nth , car , assoc , cdr , mapcar etc..

 

Example:

;split list as rows

(setq rows

(mapcar ''((x)(csv$ x)) data))

;Transposed list as columns

(setq columns (trp row))

;sum for each column (A,B,C)

(_sum columns)

;(5599.0 88.0 7960.0)

;sump for each row (1~7)

(_sum rows)

;(13.0 13.0 7545.0 5465.0 53.0 55.0 503.0)

sub-functions

;transpose : Doug Wilson
(defun trp (x)
(apply 'mapcar (cons 'list x)))

(defun csv$ (str / i l) ;split csv
	(while (setq i (vl-string-search "," str))
	  (setq	l   (cons (substr str 1 i) l)
		str (substr str (+ 2 i) (- (strlen str) i))
		) ;_ end of setq
	  ) ;_ end of while
	(reverse(vl-remove "" (cons str l)))
	)

(defun _sum (lst)
(mapcar ''((x)(apply '+ (mapcar 'atof x))) lst))

Thnak you sir, for your reply.

But, these summation is just an example, I have to go for multiple mathematical operations. And I have to do it with autolisp without opening csv file.

So, what I need is simple:

How can I read the value specific cell B4 of csv file available ? If I get this answer then I can move forward for writing Xls. Please help me sir.

Thnak you sir, for your reply.

 

But, these summation is just an example, I have to go for multiple mathematical operations. And I have to do it with autolisp without opening csv file.

 

So, what I need is simple:

 

How can I read the value specific cell B4 of csv file available ? If I get this answer then I can move forward for writing Xls. Please help me sir.

Maybe you overlooked the advise by BIGAL at post#10

what he mentioned was getexcel.lsp

read the comments with usage, putcell is one of the functions.

perhaps this is what you are looking for?

;;example function:

;PUTCELL

 

; PutCell - Put values into Excel cells

; Syntax: (PutCell StartCell$ Data$) or (PutCell StartCell$ DataList@)

; Example: (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""))

ok a solution inside excel you have VBA so could do all the reading of the csv file and maths involved and the "put" the answer into cell b23. Same with lisp. I think the problem here is not us writing something but you learning how to program Using Google you can find the sample coding again you can cut and paste code into excel macros. As a reverse you could probably use excel to drive Autocad. hanphuc has provided some examples as start You need to just get on top of taking a variable from Autocad and putting into a cell the maths is easy.

there is a CSV to list lisp I use it on iPad now not available but example

(3 4 6) (* (nth 0 lst)(nth 1 lst)) = 12

Thank you very much, to hanhphuc, BIGAL and ReMark for your help. It is finally done. Without you guys, it would never have been possible. Thank you again, god bless you.

If its done for you, can you post your code here.

Thanks.

Yes, of course. Codes are taken from the GetExcel.lsp. Exactly suggested by BIGAL anf hanhphuc in post #16.

http://web2.airmail.net/terrycad/LISP/GetExcel.lsp