Export all text content from multiple drawings ( more than 10 000) to excel

[Raph Cot]

Hi,

I am stuck to a problem.. I need to find specific text on a + - 15 000 cad library drawing on a daily base.

So I managed to create a +2.7 million lines multi table excel to list every text line of every drawing with the drawing of the specific occurence.

 

First problem, I used a program called comsCountIt! found on autodesk appstore and stretched its functions to the limit with moderate result

(the program fail If I load more than 200 drawing so I need almost 2 weeks of work to compile everything!! )

and secondly it is not every text that list on the excel files. (chose between text or text nested in block)

 

So what I need is :

Export text, mtext, nestedtext, (all text contend) from a drawing or multiple drawing to xlsx (or csv) with a column for : the text found and the files that the text is

 

I attached an exemple of the excel that I work with (for privacy I only included a fraction)

 

Thanks in advance !

 

 

Excel exemple.xlsx

[zwonko]

Maybe workaround od Lee mac bfind (batch find and replace). Used it to change text and replace it. It is generating report with changed text and filename. Propably it Has also option to only find (without replace)

 

http://www.lee-mac.com/bfind.html

Edited October 20, 2021 by zwonko

[Steven P]

As an alternative, there are examples out there to copy all the text from a sigle drawing and save it nto a text file, i file per drawing. If you go this way then I suspect it will be a bit less resource hungry than a 2.7 million line excel file... and it might not crash.

 

So my thought might be find a LISP to save texr as a text file - you can specify where it is saved in the routine - and then use a batch script editor (Lee Mac has one) to run this on all the drawings. Save the dtext file as the drawing name, You could even leave this running overnight couldn't you? The beauty of this is that if it all crashes the last text file saved will tell you how far along it got to and you can probably work out how to restart it from that point. To seach for text use the search facility in wndows explorer - help is onlne how to search for text in a file

 

You could append the text to a single text file but I would go 1 file for each drawing, named the same as the drawing. If you want them all in excel, there might be an Excel macro to do that

 

One other benefit of this method is that you only now need to update the files modified since you last ran the routine rather than the excel sheet.

 

Evenong here and my CAD machine is off but hoping this might give you another method

[BIGAL]

Using accoreconsole will solve 1 part the searching dwg's, I would do in batches a directory etc at a time. You can get all text with ssget *text,  nested text ? 

 

There is a remove doubles and a count by Gile which I have used works well and is fast. 

 

I can see from the excel patterns but what is search criteria for which text ? To get every text seems massive task and does not make sense to me. 

 

Lastly rather than Excel I would look at Access can handle way bigger database and supports indexes. 

 

 

[maratovich]

What is the ultimate goal?
Is it for translating drawings into another language?

[Raph Cot]

6 hours ago, maratovich said:

What is the ultimate goal?
Is it for translating drawings into another language?

The goal is to quickly search text throught + - 15 000 drawing.

This is the perfect tool to cross reference a PLC input/output, follow a path of a cable with a specific name, find in wich drawing there is a reference to an equipment, etc.

 

So the only thing I need is to list every text in every drawing

almost like a function find "all text" with a export csv of the result.. on every drawing.

 

[Raph Cot]

13 hours ago, Steven P said:

As an alternative, there are examples out there to copy all the text from a sigle drawing and save it nto a text file, i file per drawing. If you go this way then I suspect it will be a bit less resource hungry than a 2.7 million line excel file... and it might not crash.

 

So my thought might be find a LISP to save texr as a text file - you can specify where it is saved in the routine - and then use a batch script editor (Lee Mac has one) to run this on all the drawings. Save the dtext file as the drawing name, You could even leave this running overnight couldn't you? The beauty of this is that if it all crashes the last text file saved will tell you how far along it got to and you can probably work out how to restart it from that point. To seach for text use the search facility in wndows explorer - help is onlne how to search for text in a file

 

You could append the text to a single text file but I would go 1 file for each drawing, named the same as the drawing. If you want them all in excel, there might be an Excel macro to do that

 

One other benefit of this method is that you only now need to update the files modified since you last ran the routine rather than the excel sheet.

 

Evenong here and my CAD machine is off but hoping this might give you another method

This is a great way to do it and I ike the fact that I could run over night ( not have two autocad instances open and manage the amount of ram used as I work..)

Only thing, do not trust window search, long story short.. windows search do not work as fast as a find in excel and sometime do not show every files.

Even worst, I have windows 7 and only a I5-4590 with 16 go RAM 

[Raph Cot]

12 hours ago, BIGAL said:

Using accoreconsole will solve 1 part the searching dwg's, I would do in batches a directory etc at a time. You can get all text with ssget *text,  nested text ? 

 

There is a remove doubles and a count by Gile which I have used works well and is fast. 

 

I can see from the excel patterns but what is search criteria for which text ? To get every text seems massive task and does not make sense to me. 

 

Lastly rather than Excel I would look at Access can handle way bigger database and supports indexes. 

 

 

Yes it is a massive task for a computer..

For Access I tought about it you are right. I needed more time to understant how access work but I am sure that this can be the solution as excel is not ment to be a data center.

 

For the Gile research I will try it on my personnal computer as at work, I need to verify windows installer.

[Raph Cot]

16 hours ago, zwonko said:

Maybe workaround od Lee mac bfind (batch find and replace). Used it to change text and replace it. It is generating report with changed text and filename. Propably it Has also option to only find (without replace)

 

http://www.lee-mac.com/bfind.html

I love what Lee-mac offer but in my case,it does not meet what I need. It work too slow for the immense drawing library that I have.

[BIGAL]

To dump say *text to a csv is only a few lines of code, again look at Accoreconsole it will scream through the dwgs, what you do with what is created is up to you, oh yeah the other hint is use the "A" option for a file open this is append so your csv will just grow as each dwg is processed. 

 

https://through-the-interface.typepad.com/through_the_interface/2012/02/the-autocad-2013-core-console.html

 

 

[Steven P]

Just wondering if that would work well if you updated say 100 of the 15000 files and wanted to update the texts listing, would you have to start from the beginning again, or modify the file?

[Raph Cot]

3 hours ago, Steven P said:

Just wondering if that would work well if you updated say 100 of the 15000 files and wanted to update the texts listing, would you have to start from the beginning again, or modify the file?

I search .dwg extension on all drawing folder on windows, I filter by date and select all drawing from a specific date.

I put the result on a new tab on my excel file

[BIGAL]

This is dump text will work with TEXT & Mtext. just save it as textdump.scr and use it in conjunction with Accoreconsole. 

 

(defun wow ( / ss fo)
(setq fo (open "d:\\acadtemp\\textout.txt" "A")) 
(setq ss (ssget "X" '((0 . "*TEXT"))))
(repeat (setq x (sslength ss))
(write-line (strcat (getvar 'dwgprefix) (getvar 'dwgname) ","(cdr (assoc 1 (entget (ssname ss (setq x (1- x))))))) fo)
)
(close fo)
)
(wow)

 

Re update you would have to look for a particular dwg name in your master list and update those records with current text values. Could be a big job. Maybe remove from database and add new ones.