Convert .xls or xlsx file to .csv file

[Arash-ET]

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

[mhupp]

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

[BIGAL]

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.

[hosneyalaa]

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

[Arash-ET]

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

[Arash-ET]

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.

[Steven P]

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?

[hosneyalaa]

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

 

 

[BIGAL]

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

[Arash-ET]

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

[Arash-ET]

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

[Arash-ET]

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.

[hosneyalaa]

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

[BIGAL]

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