Eng-Tips is the largest engineering community on the Internet
Intelligent Work Forums for Engineering Professionals
-
Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to tranfer a table from CAD to excel
- Thread starter cad123
- Start date
- Thread starter
- #3
Sure, it is easy for tranfer from Excel to CAD, but I have problem when tranfer from CAD to excel. Because this object in CAD is text and can not copy and paste to excel , it will be a bitmap. I mean I have a CAD file but do not have the excel data file.
cad123,
which AC-version are you running? Since 2006 you can export the text to Excel
In earlier versions I would search for a lisp. Maybe you can export the text to a *.csv file.
Lothar
ADT 2004
ACAD 2002
which AC-version are you running? Since 2006 you can export the text to Excel
In earlier versions I would search for a lisp. Maybe you can export the text to a *.csv file.
Lothar
ADT 2004
ACAD 2002
SeanSweeney
Civil/Environmental
Have you tried the export command from a selected table - to a csv file type, which can in turn be opened in excel. Theres no link which is the problem. You can I guess then do as IFRs said and copy that table back to autocad and link it.
I have had far too many link problems from excel so I have given up (didnt always update / strange lines on the printout to PDF etc).
Alternatively you can select the area in excel and copy, go to autocad and paste special, and slect autocad entities. It works but takes some work to make it look right - I've not found a match properties method for tables yet.
Cheers. Sean.
I have had far too many link problems from excel so I have given up (didnt always update / strange lines on the printout to PDF etc).
Alternatively you can select the area in excel and copy, go to autocad and paste special, and slect autocad entities. It works but takes some work to make it look right - I've not found a match properties method for tables yet.
Cheers. Sean.
I picked this up as a result of looking for some seismic design information... I don't have the URL, but a google search of Dennis Wish and 97UBC might locate it. The file starts here:
The following instructions help to automate the process of extracting points from an Autocad R14 or later drawing in CDF (Comma Delimited Format) and inserting it into an Excel File. For here it is an easy matter of cutting and pasting the cell values into the 97 UBC Design Spreadsheet to eliminate hours of manual work.
Please report any problems you have with this to me at SEConsultant@earthlink.net. I will see what I can do to correct the problem quickly.
NOTE: THE FOLLOWING WAS TESTED ON AUTOCAD R2000. I ASSUME THE INSTRUCTIONS WILL BE THE SAME FOR RELEASE 14 BUT HAVE NOT TESTED IT. I SAVED THE BLOCKS PLAN_PT AND WALL_PT IN A FORMAT THAT R14 WILL READ.
PLACE ALL FILES IN YOUR AUTOCAD / SUPPORT DIRECTORY
The file contains two blocks which can be inserted on a template once and forgotten about. These blocks are inserted on their own layers to allow you to turn them on and off easily.
Wall Coordinate and Plan Coordinate text files are templates which Autocad needs to extract the coordinates and Tag name to a Comma Delimited Format text file (you can choose whatever name for the output file you want). Once this is done, the file can be opened and formated in Excel 97 or later (possibly earlier as well but this has not been tested).
Once the Excel file is created the data must be sorted so that the points are continuous.
NOTE: IT IS IMPORTANT TO SORT THE DATA BEFORE COPYING AND PASTING THE VALUES TO YOUR 97 UBC SPREADSHEET OR THE RESULTS WILL BE INACCURATE. ALSO, AUTOCAD MUST BE SETUP SO THAT ALL DATA IS OUTPUT IN EITHER DECIMAL INCHES OR DECIMAL FEET. PLEASE REFER TO YOUR USERS MANUAL .
Usage Directions
1. Insert Plan_Pt.dwg at all points around the perimeter of your roof (you can define this as a polyline or simply pick off points for insertion. The limits of your roof are based on your interpretation of the code requirments for diaphragm area.
1.a Enter an attribute value that represents each point in consecutive numbers 1,2,3,4.....50 for every point chosen. Insert the block sequentially so that Excel will be able to calcualte the area, center of mass, center or rigidity and application of loads. If you forget what number you have entered last simply start at 100 and continue. If you forget again, start at 200 and so forth. You don't need the tag value, however, the purpose is only to sort the coordinates for insertion into the UBC spreadsheet
2. If you are locating Wall data, I suggest you use a polyline to define the true length of the all and insert it on the wall at all locations which are to be designated as shearwalls. Insert Wall_Pt at each end of each wall.
2a. For walls enter an attribute value in the format #.1 and #.2 where # is the wall number and .1 or .2 represents the left and right edge of the wall. Follw the tip in 1.a should you forget what wall number was entered last. It is important that the left and right side of the wall have the same integer.
3. Isolate the layer of the block you want to extract data from.
4. Use Autocad's ATTEXT to extract the data.
4.a Choose all blocks within the drawing that you want to pull data from.
4.b Choose the Wall Coordinate.txt or Plan Coordinate.txt files as a template when prompted. Be sure to designate where this file is located (use Browse when possible to manually locate the file).
4.c Designate whether you want the values extracted in Comma or Space delimited format - either should work within Excel, however, I have only tried with Comma Delimited
4.d Enter the Name of the output file. It will automatically have the extention TXT. It is important that you use an ASCII text editor like Windows Notepad to insure that the text is not formated.
5. Open Excel 97 or later
6. File/Open the file you created above. Make sure that the file type is set to text or all files and retrieve the file from the folder / directory that you had Autocad save it in. For simplicity, I generally save these files to my desktop so that I can easily locate it whenI need it. When finished, I move it to a project file.
7. You may now need to refer to your Excel manual for the rest, but Excel will recognize the format and lead you through a series of dialog boxes to format the data into columns. Once you have completed this step, the data will be in spreadsheet format without column headings. You don't need these for now.
8. Using DATA/ SORT, sort the values on the column containing the attribute numbers you assigned. Sort from lowest to highest. Save the data to any name you like, but make sure you are saving it in Excel 97 or later XLS format.
9. Open the 97 UBC workbook (spreadsheet). If you saved it to a project name (since it is a template) open the project file you created.
10. You can either arrange two vertical windows side by side or highlight data by switching workbooks. Copy the data from the extraction file and Paste the values only into the appropriate columns on the Rigid Diaphragm Geometry-Walls Worksheet.
If you need to change a wall, follow the proceedure from the start. You can move inserted blocks rather than deleting them and re-inserting. This allows you to fine tune the diaphragm area or relocate shearwalls to a different location or oriented in a different direction.
Follow the same steps (it becomes very easy after the first or second time) and the UBC spreadsheet will update accordingly.
The above information is given in great detail. The steps are really very easy and straight foreward. After you have done it once or twice all future attempts will be second nature.
HINTS:
I have not done this as yet, but I intend to try (and will provide a copy to the users of the 97 UBC spreadsheet when ready). If you generally create a limited number of shearwall types, you can create each wall as a block and attach attirbutes to the walls. You can then create a pull-down menu add-in for Autocad which helps you automate the insertion of these blocks onto your shearwall plan. Use the Wall_Pt.dwg file. First explode the block and then place it on each end of the line or polyline you will use to define the shearwall block. Define Attributes for the wall line object such as Wall length, sheathing thickness, nail spacing, shear value, etc and save all objects as a block with a descriptive name (ie, 280_plf_SW.dwg
When you insert the block it will ask you for the wall starting point and ending point attribute names 1.1 and 1.2 etc. All other data will be embedded and can later be extracted as a schedule or table on your Autocad drawing (an easily created shearwall schedule).
If you improve upon any of the spreadsheets or add on features that I provide, I would appreciate it if you would turn around and share it with the rest of the users.
Enjoy,
Dennis S. Wish PE
seconsultant@earthlink.net
3. Use the format #.1 and #.2 to represent right and left end of wall.
4. Save Attext data to a Comma Delimited Value in the output and use Wall.txt as the template file.
5. Move the column with the Wall_Datum values (#.1 or #.2) and sort on this column.
6. Copy and past values only to the 97 UBC Spreadsheet.
The following instructions help to automate the process of extracting points from an Autocad R14 or later drawing in CDF (Comma Delimited Format) and inserting it into an Excel File. For here it is an easy matter of cutting and pasting the cell values into the 97 UBC Design Spreadsheet to eliminate hours of manual work.
Please report any problems you have with this to me at SEConsultant@earthlink.net. I will see what I can do to correct the problem quickly.
NOTE: THE FOLLOWING WAS TESTED ON AUTOCAD R2000. I ASSUME THE INSTRUCTIONS WILL BE THE SAME FOR RELEASE 14 BUT HAVE NOT TESTED IT. I SAVED THE BLOCKS PLAN_PT AND WALL_PT IN A FORMAT THAT R14 WILL READ.
PLACE ALL FILES IN YOUR AUTOCAD / SUPPORT DIRECTORY
The file contains two blocks which can be inserted on a template once and forgotten about. These blocks are inserted on their own layers to allow you to turn them on and off easily.
Wall Coordinate and Plan Coordinate text files are templates which Autocad needs to extract the coordinates and Tag name to a Comma Delimited Format text file (you can choose whatever name for the output file you want). Once this is done, the file can be opened and formated in Excel 97 or later (possibly earlier as well but this has not been tested).
Once the Excel file is created the data must be sorted so that the points are continuous.
NOTE: IT IS IMPORTANT TO SORT THE DATA BEFORE COPYING AND PASTING THE VALUES TO YOUR 97 UBC SPREADSHEET OR THE RESULTS WILL BE INACCURATE. ALSO, AUTOCAD MUST BE SETUP SO THAT ALL DATA IS OUTPUT IN EITHER DECIMAL INCHES OR DECIMAL FEET. PLEASE REFER TO YOUR USERS MANUAL .
Usage Directions
1. Insert Plan_Pt.dwg at all points around the perimeter of your roof (you can define this as a polyline or simply pick off points for insertion. The limits of your roof are based on your interpretation of the code requirments for diaphragm area.
1.a Enter an attribute value that represents each point in consecutive numbers 1,2,3,4.....50 for every point chosen. Insert the block sequentially so that Excel will be able to calcualte the area, center of mass, center or rigidity and application of loads. If you forget what number you have entered last simply start at 100 and continue. If you forget again, start at 200 and so forth. You don't need the tag value, however, the purpose is only to sort the coordinates for insertion into the UBC spreadsheet
2. If you are locating Wall data, I suggest you use a polyline to define the true length of the all and insert it on the wall at all locations which are to be designated as shearwalls. Insert Wall_Pt at each end of each wall.
2a. For walls enter an attribute value in the format #.1 and #.2 where # is the wall number and .1 or .2 represents the left and right edge of the wall. Follw the tip in 1.a should you forget what wall number was entered last. It is important that the left and right side of the wall have the same integer.
3. Isolate the layer of the block you want to extract data from.
4. Use Autocad's ATTEXT to extract the data.
4.a Choose all blocks within the drawing that you want to pull data from.
4.b Choose the Wall Coordinate.txt or Plan Coordinate.txt files as a template when prompted. Be sure to designate where this file is located (use Browse when possible to manually locate the file).
4.c Designate whether you want the values extracted in Comma or Space delimited format - either should work within Excel, however, I have only tried with Comma Delimited
4.d Enter the Name of the output file. It will automatically have the extention TXT. It is important that you use an ASCII text editor like Windows Notepad to insure that the text is not formated.
5. Open Excel 97 or later
6. File/Open the file you created above. Make sure that the file type is set to text or all files and retrieve the file from the folder / directory that you had Autocad save it in. For simplicity, I generally save these files to my desktop so that I can easily locate it whenI need it. When finished, I move it to a project file.
7. You may now need to refer to your Excel manual for the rest, but Excel will recognize the format and lead you through a series of dialog boxes to format the data into columns. Once you have completed this step, the data will be in spreadsheet format without column headings. You don't need these for now.
8. Using DATA/ SORT, sort the values on the column containing the attribute numbers you assigned. Sort from lowest to highest. Save the data to any name you like, but make sure you are saving it in Excel 97 or later XLS format.
9. Open the 97 UBC workbook (spreadsheet). If you saved it to a project name (since it is a template) open the project file you created.
10. You can either arrange two vertical windows side by side or highlight data by switching workbooks. Copy the data from the extraction file and Paste the values only into the appropriate columns on the Rigid Diaphragm Geometry-Walls Worksheet.
If you need to change a wall, follow the proceedure from the start. You can move inserted blocks rather than deleting them and re-inserting. This allows you to fine tune the diaphragm area or relocate shearwalls to a different location or oriented in a different direction.
Follow the same steps (it becomes very easy after the first or second time) and the UBC spreadsheet will update accordingly.
The above information is given in great detail. The steps are really very easy and straight foreward. After you have done it once or twice all future attempts will be second nature.
HINTS:
I have not done this as yet, but I intend to try (and will provide a copy to the users of the 97 UBC spreadsheet when ready). If you generally create a limited number of shearwall types, you can create each wall as a block and attach attirbutes to the walls. You can then create a pull-down menu add-in for Autocad which helps you automate the insertion of these blocks onto your shearwall plan. Use the Wall_Pt.dwg file. First explode the block and then place it on each end of the line or polyline you will use to define the shearwall block. Define Attributes for the wall line object such as Wall length, sheathing thickness, nail spacing, shear value, etc and save all objects as a block with a descriptive name (ie, 280_plf_SW.dwg
When you insert the block it will ask you for the wall starting point and ending point attribute names 1.1 and 1.2 etc. All other data will be embedded and can later be extracted as a schedule or table on your Autocad drawing (an easily created shearwall schedule).
If you improve upon any of the spreadsheets or add on features that I provide, I would appreciate it if you would turn around and share it with the rest of the users.
Enjoy,
Dennis S. Wish PE
seconsultant@earthlink.net
3. Use the format #.1 and #.2 to represent right and left end of wall.
4. Save Attext data to a Comma Delimited Value in the output and use Wall.txt as the template file.
5. Move the column with the Wall_Datum values (#.1 or #.2) and sort on this column.
6. Copy and past values only to the 97 UBC Spreadsheet.
- Thread starter
- #8
Thanks Exxit I will try 2006 version, now I use CAD2004 so I did not know the new one have this function.
- dik : sorry I read through your reply but understand a little, it is complicated . I used to write a lisp for export table to excel but it not work well the data is enough but mess up
Thanks
- dik : sorry I read through your reply but understand a little, it is complicated . I used to write a lisp for export table to excel but it not work well the data is enough but mess up
Thanks
I also wrote a lisp to export a table of text in excel, to a csv file. By "messed up" I assume you mean not in correct order of columns/rows? That was the more difficult part of the routine. If you post your email address (disguised), or have a place to post it I'll send you a copy to try out.
- Thread starter
- #10
"Everybody is ignorant, only on different subjects." — Will Rogers
There is a program made and supported by dotsoft called XL2CAD. I believe the most current version is 4.0 which is the one that I'm currently using. It has a lot of great features including auto-update when reopening a drawing with that object in the file.
- Status
Similar threads
- Locked
- Question
- Question