Arash-ET Posted January 17, 2022 Share Posted January 17, 2022 Hi. I have a folder in specific direction and it contains .xls or .xlsx files for example the folder include 20 .xls or .xlsx files. I would be grateful if you help me how to create a LISP to covert all these files to .csv files. I know .csv file only have on sheet and it is no problem for what i want to do so it will be no problem for me if the other sheets will be deleted. Best regards Quote Link to comment Share on other sites More sharing options...
mhupp Posted January 17, 2022 Share Posted January 17, 2022 (edited) Maybe you could read the spreadsheets and write them to a new csv. Autocad cant convert a file structer its not desinged for. Look for an excel VBA macro to convert to csv. Edited January 17, 2022 by mhupp Quote Link to comment Share on other sites More sharing options...
BIGAL Posted January 18, 2022 Share Posted January 18, 2022 Like mhupp saveas csv is a feature of excel. Yes could open then read a excel direct writing a csv from Autocad, but is it worth all the effort compared to save csv. If you want it will cost you. 1 Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted January 18, 2022 Share Posted January 18, 2022 19 hours ago, Arash-ET said: Hi. I have a folder in specific direction and it contains .xls or .xlsx files for example the folder include 20 .xls or .xlsx files. I would be grateful if you help me how to create a LISP to covert all these files to .csv files. I know .csv file only have on sheet and it is no problem for what i want to do so it will be no problem for me if the other sheets will be deleted. Best regards hi please Attach example file Quote Link to comment Share on other sites More sharing options...
Arash-ET Posted January 18, 2022 Author Share Posted January 18, 2022 8 hours ago, mhupp said: Maybe you could read the spreadsheets and write them to a new csv. Autocad cant convert a file structer its not desinged for. Look for an excel VBA macro to convert to csv. Yes i can read the xls and xlsx file with GetExcel.lsp but it takes a long time but my data are huge it maybe takes a while actually i found VBA to do this but i want every thing done with LISP Quote Link to comment Share on other sites More sharing options...
Arash-ET Posted January 18, 2022 Author Share Posted January 18, 2022 2 hours ago, hosneyalaa said: hi please Attach example file Sheet1.xlsx i have folder and it is include excel file like this attachment. Important thing is I do not know how many rows will be exist in excel file its variable. Quote Link to comment Share on other sites More sharing options...
Steven P Posted January 18, 2022 Share Posted January 18, 2022 Assuming you have Excel, open office or similar spreadsheet program it seams weird to me to use a program that is not designed to handle tables of data to do what the native program is designed to do. If the scale of the issue is about 20 files I reckon you would be quicker to just open then all with the spreadsheet and 'save as' rather than spending the same amount of time researching, asking about and writing a LISP to do that, and then there is testing afterwards. When I am working my goal of automation is to make more time in the day to get a cup of tea, not spend more time and get fewer cups. However if you are likely to do this regularly then it might be worth writing something to do this... but is could be a slow running LISP. Unless someone has something really clever, my instinct is that AutoCAD won't be able to save an XLS file directly as CSV, you'll have to read the data to memory, create a CSV file and write the data to that. Which like you say takes time with GetExcel Lisp. Can I ask why you want to do all this with LISP rather than with a spreadsheet? 1 Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted January 18, 2022 Share Posted January 18, 2022 4 hours ago, Arash-ET said: Sheet1.xlsx 94.69 kB · 2 downloads i have folder and it is include excel file like this attachment. Important thing is I do not know how many rows will be exist in excel file its variable. Hi Can you attach a result file? I don't know if I can help you, it may take time Quote Link to comment Share on other sites More sharing options...
BIGAL Posted January 18, 2022 Share Posted January 18, 2022 (edited) If the csv is to be used as a part 2 of the lisp then why make a csv just read the excel directly, I have started doing my own version of getexcel as it has a couple of things I re coded to use a different method like get cells. There is a variable in a excel its the used range, not to be confused with selected range. Maybe this is a clue. (setq UR (vlax-get-property (vlax-get-property myxl "ActiveSheet") "UsedRange")) (setq CR (vlax-get-property UR "CurrentRegion")) (setq RADD (vlax-get-property CR "Address")) (setq cnt (vlax-get-property CR "Count")) Look at property Address (RO) = "$A$1:$P$65" in UR my test excel had a graph so RADD was more useful "$A$1:$b$65" Edited January 18, 2022 by BIGAL Quote Link to comment Share on other sites More sharing options...
Arash-ET Posted January 19, 2022 Author Share Posted January 19, 2022 16 hours ago, Steven P said: Assuming you have Excel, open office or similar spreadsheet program it seams weird to me to use a program that is not designed to handle tables of data to do what the native program is designed to do. If the scale of the issue is about 20 files I reckon you would be quicker to just open then all with the spreadsheet and 'save as' rather than spending the same amount of time researching, asking about and writing a LISP to do that, and then there is testing afterwards. When I am working my goal of automation is to make more time in the day to get a cup of tea, not spend more time and get fewer cups. However if you are likely to do this regularly then it might be worth writing something to do this... but is could be a slow running LISP. Unless someone has something really clever, my instinct is that AutoCAD won't be able to save an XLS file directly as CSV, you'll have to read the data to memory, create a CSV file and write the data to that. Which like you say takes time with GetExcel Lisp. Can I ask why you want to do all this with LISP rather than with a spreadsheet? first of all thank you for your explanation. I do not have 20 file actually it could be 100 or more. and about your question i should say my database are excel which contain load on structure and coordinate of point load i want to work with coordinates and draw them on DWG and sorry for my bad english Quote Link to comment Share on other sites More sharing options...
Arash-ET Posted January 19, 2022 Author Share Posted January 19, 2022 16 hours ago, hosneyalaa said: Hi Can you attach a result file? I don't know if I can help you, it may take time First of all, thank you for your time, and this is a result file in attachment but I did a process to create this file actually ma main problem is the user should convert all .xls or .xlsx file to .csv and as i have 100 of them it maybe take some time. Book1.xlsx Quote Link to comment Share on other sites More sharing options...
Arash-ET Posted January 19, 2022 Author Share Posted January 19, 2022 5 hours ago, BIGAL said: If the csv is to be used as a part 2 of the lisp then why make a csv just read the excel directly, I have started doing my own version of getexcel as it has a couple of things I re coded to use a different method like get cells. There is a variable in a excel its the used range, not to be confused with selected range. Maybe this is a clue. (setq UR (vlax-get-property (vlax-get-property myxl "ActiveSheet") "UsedRange")) (setq CR (vlax-get-property UR "CurrentRegion")) (setq RADD (vlax-get-property CR "Address")) (setq cnt (vlax-get-property CR "Count")) Look at property Address (RO) = "$A$1:$P$65" in UR my test excel had a graph so RADD was more useful "$A$1:$b$65" oo it maybe helpful thank you. Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted January 19, 2022 Share Posted January 19, 2022 Hi I couldn't understand how to connect the first picture with the result in the second picture There is no connection between them, did you explain that? And if you want to draw it in AutoCAD There is no need to convert, take the data directly from Excel If your ultimate goal becomes clear, it will be better From the second file, these look like results from SAP2000 And you want to turn it into a drawing, I think Quote Link to comment Share on other sites More sharing options...
BIGAL Posted January 19, 2022 Share Posted January 19, 2022 (edited) Like hosneyalaa I am confused what it is your trying to do, as a Civil engineer have played with points and stringing for years, a PXYZD file is very common. Post a xls and a csv that match, need then a dwg to see what joins to what. Only need a few lines etc. Is this what you want ? Edited January 19, 2022 by BIGAL Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.