Export list to excel

[msarqui]

Hi guys,

In the list: (setq lst '("ITEM" "QUANTITY"))

How can I export "lst" to an Excel file, or csv, in a way that the "ITEM" will be put in the first column and "QUANTITY" in the second column?

Thanks,

[paullimapa · Accepted solution]

If you want to create comma separated csv file, you can try structuring a lisp routine like this:

(defun c:Export-CSV (/ count csv lst txt )

 (setq lst '("ITEM" "QUANTITY")) ; given list 

 (setq count 1)

 (setq csv (open(strcat(getvar"dwgprefix")(getvar"dwgname")".csv")"w")) ; create a csv file using drawing prefix & name with .csv file type

 (setq txt (nth 0 lst)) ; place into a text string the first element of the list

 (repeat (1- (length lst)) (setq txt (strcat txt "," (nth count lst))) (setq count (1+ count)))

; loop through the length of the list and place into a long text string separated by a comma

 (write-line txt csv) ; write the long text string to the csv file

 (close csv) ; close the csv file

) ; defun command Export-CSV

Area Object Link | Attribute Modifier | Dwg Setup | Feet-Inch Calculator
Layer Apps | List on Steroids | VP Zoom Scales | Exchange App Store

[msarqui]

Hi pli,
Thanks for your answer but I do not want comma separated. I want column separated. I mean, one row with each string in a different cell.

[paullimapa]

Like with any CSV file, when you have a string of text separated by a comma and you open this file using Excel, each item will be in a separate cell.

I'm not sure what you're trying to get at here...

Area Object Link | Attribute Modifier | Dwg Setup | Feet-Inch Calculator
Layer Apps | List on Steroids | VP Zoom Scales | Exchange App Store

[leeminardi]

What @paullimapa has given you is a program that creates a comma delimited file.  You can open it in Excel and then specify that it is a delimited file and that commas are the separator between columns.

Alternatively, you can modify the following line in pli's program:

 (repeat (1- (length lst)) (setq txt (strcat txt "," (nth count lst))) (setq count (1+ count)))

and replace the "," with (chr 9)  yielding:

 (repeat (1- (length lst)) (setq txt (strcat txt (chr 9) (nth count lst))) (setq count (1+ count)))

which will create a tab delimited file which will open directly as you want in Excel with the tab character used to make the new columns.

[msarqui]

Hi Paul,

You are absolutely right. Your code works perfectly well. The problem was that when I opened the csv file on the office computer, the result was separated by comma in one cell. But when I got home and opened it on my computer, the result was separated by columns, in two cells. I'm not an Excel expert, but I imagine there must be some differing setup between the two computers. Later I'll do some research to try and figure this out.

Thanks a lot for the help.

Marcelo

[msarqui]

Hi lminardi,

I tried your suggestion but it did not work. The result was ITEMQUANTITY in the same cell.

By the way, I have Excel 2016 in French at work and in English at home. I read here that this can be an issue and it can explain why it works with comma separated at home and not at work.

"Excel ships with different configurations and hotkeys depending on the language. This makes importing a CSV file a puzzle"

Thanks

[paullimapa]

Read up the procedures as outlined on this web page on how to import csv files into Excel:

https://www.ablebits.com/office-addins-blog/2014/05/01/convert-csv-excel/ 

I would especially investigate on your work computer item 5. Change separators in Region and Language settings. 

Cheers!!!!

Area Object Link | Attribute Modifier | Dwg Setup | Feet-Inch Calculator
Layer Apps | List on Steroids | VP Zoom Scales | Exchange App Store

[msarqui]

Hi Paul,

I find the solution here.

The second post explain that American Windows versions, the comma is set as default for the "List Separator", but on European Windows versions this character is reserved as the Decimal Symbol and the "List Separator" is set by default to the semicolon. So, I will just edit the code to have ";" rather than "," because it will be used only at work.

Cheers!

[mbrown2N7T5]

I know it is five years since your post, but I wanted to thank you for the code. It worked perfectly for what I needed it to do. I am pulling Fuse Part numbers and Tagnames from ACADE and putting them in a spreadsheet formatted for an Access Database I created to make Fuse lists for our panels we build. Lisp is a little clunky when it comes to XLS and CSV files, so anything New I pick up is great.

[Sea-Haven]

Have a look at getexcel.lsp its very handy when it comes to writing direct to excel. It does require a understanding of lisp.

[mbrown2N7T5]

99% of our projects are automated using Autolisp. My first big adventure was automating our revisions. We type in "Arev", then just insert the rev triangle, type in a desription and show it where the revisioncloud goes. everything else is automated. It knows which rev we are on and inserts it into the rev triangle, whose initials to insert in the revision block, and the date to insert. We modified the same program to do our as-built drawings for a project, which probably saves over a half hour on a twenty sheet project.

 Thanks again for the help.

[Sea-Haven]

You do know you can write to a Access database directly ? But dont ask me how, just know you can do it.

[mbrown2N7T5]

We write directly to the Database on the bigger problems. We have a lisp that creates a new project for us, inserting all of our sheets, most of the parts, including all of the PLC , on their proper pages. It took a lot of programming. For most of it, we are just inserting saved circuits, based on the selection the user makes when the program starts. We use DCL to build selection windows for all of the components.(power supplies, fans, heaters, Surge suppressors. etc.) , We use the parametric builder for the PLC stuff, but communicate directly with the Database to do that. We use a spreadsheet to populate the PLC data, which we can use repeatedly as the project gets modified.  We can build a project of 20 sheets, including an 8 card PLC, in about 2 minutes, when it would have taken over a full day without the Programming.

 autolisp is awesome, and when combined with all of the other tools, and a lot of time, It can save more hours than you can count, and reduce errors.