Import Data from spreadsheet not importing data

[Anonymous]

Hi All,

I posted this problem back in October of 2015.  I'm semi-reposting it again because the issue still persists.  In a nutshell, the Import Data feature is not importing/updating to the drawings all the data I edit in the exported spreadsheet.  When I say "all the data", I mean all relevant data in the following tabs: COMP, TERM, WIRENO, PNLCOMP, & PNLTERM (more on that below).  The results are a mixed bag leading to one of the following outcomes:

• The entire sheet updates according to the changes made to the spreadsheet, or
• Some sheets have certain schematic child symbols updated, but their schematic parent symbol remains unchanged, or
• Schematic parent symbols are updated, some of their child symbols change, but their peer on the wiring diagram/panel layout remains unchanged, or
• The entire sheet is left unchanged.

NOTE: The same mixed results outlined above apply to wire numbers and terminals throughout the project.

The unpredictable results make it difficult to troubleshoot the problem.  Before I import the data 'From Spreadsheet', all parent symbols, child symbols, and their peers share their respective INST, LOC, & TAG attributes and fully link to one another in the project.  They can be surfed from one sheet to another, so the relationships are verified prior to running the update. 

I'll use the same example I used in my previous post to outline the steps I take:

J1012 is an old panel designation and J1008 is a newly added panel designation that is an exact copy of panel J1012, except for the obvious name changes.  Steps: 

• I copy project J1012 and rename the copy J1008.
• I click 'Import/Export Data > To spreadsheet', select "General", select "Project", and the spreadsheet opens.
• I proceed to change ALL instances of J1012 in the spreadsheet to J1008 in the COMP, TERM, WIRENO, PNLCOMP, & PNLTERM tabs (INST, LOC, TAGx, etc, etc.).  
• I change ALL wire numbers and terminals to match the new panel data.
• I save and close the spreadsheet. 
• I click 'Import/Export Data > from spreadsheet, select Project, and let it go about its business. 

That's it.  I can't think of why AutoCAD is selectively changing certain fields and not others, when all fields in question were changed in the spreadsheet.  I'm not messing with the format of the individual cells or the column headers in any way.  All of the canned "demos" of this feature show an automated, flawless updating process that really sells the product.  The nature of the work I'm doing required 100% customization of all symbols, blocks, etc. so a standard demonstration of the tool doesn't help me much.  

Any helpful info will get enthusiastic kudos. 🙂

Thanks,

Tim T.

P&C Designer

Substation Relay

Ampirical Solutions, LLC 

[dougmcalexander]

Be sure that all data in the spreadsheet remains as ASCII text.  If you manipulate numerical values Excel might convert the cells to actual numbers.  There is a Macro that pushes out with the spreadsheet export called Convert to Text.  Run this macro for all spreadsheet pages that you edit.

[Anonymous]

Hi Doug,

Thanks for the reply. 

I ran it again and, this time, I highlighted all of the cells in each tab and changed the cell format to 'text'.  Is that what you meant by the macro?  I ran the 'from spreadsheet' command and, unfortunately, got the same results as before.  I thought this would at least solve the issue I was having with wire numbers not updating.  

The example below is a microcosm of a problem that applies to everything from schematic terminals to wire numbers, etc.

Some notes:

• The two symbols that are circled are the same exact symbol (VCR21_NO_CONTACT_FLEXITEST).
• Both symbols share the same info, minus DESC info.
• Both symbols are on the same sheet.
• The symbol circled in GREEN changed when I imported the spreadsheet data.  The symbol circled in RED remains unchanged.

If this issue is solved, I have a feeling it will apply to every other issue I'm having with this command.

[dougmcalexander]

There is a macro that exports from AutoCAD Electrical when you export the spreadsheet. It will appear in the Macro list inside Excel, provided you enabled the import of macros in the security settings of Excel. By default it is disabled.

But as long as you make sure all cells are text before you import back into AutoCAD Electrical it should work. It has never failed me. The only issue I had ia that versions prior to 2017 require that you update the project scratch database before you export from AutoCAD Electrical. The command was never added to the ribbon but you can type AEREBUILDDB at the command line and press enter. Always choose a full rebuild. I added the command to my Project Tools ribbon menu.

[Anonymous]

Hi Doug,

I tried it again this afternoon.  I enabled macros in Microsoft Excel 2010, executed an AEREBUILDDB prior to exporting from AutoCAD, and again made sure that all cells were 'text' format.  Unfortunately, I still get the same results - or lack thereof.

I experienced some success with wire numbers, but only if I exported just the wire numbers from a single active drawing, edit them, and import them back into the active drawing.  Trying to use the "General" tab for the same active drawing gave the following return:

AutoCAD Electrical Export --> Spreadsheet
 COMP: 48
 TERM: 77
 PLCMOD: 0
 PLCIODESC: 0
 SIG: 6
 WNUM: 20
 PNLCOMP: 0
 PNLTERM: 0
 Exported file: \\ampsrv3\ahome\trt01\My Documents\0_AutoCAD Electrical Data\SSTESTD05_01.XLS
 Data lines extracted: 151
Command:
Command:
Command:
Command:
Drawing Update from spreadsheet
Command:
Command:
Command:
Command:
Drawing Update from spreadsheet
Updating COMP
Error: bad argument type: stringp nil

I converted a client's current "dumb" CAD drawings into AutoCAD Electrical for a POC project.  For that client, and company I'm working with, one of the requirements for adopting the AutoCAD Electrical platform is the ability to mass-edit project data very quickly and accurately.  Unfortunately, if the project-wide update option doesn't function properly, it renders the entire Export/Import Data feature all but a complete waste of time for us, and may eliminate AutoCAD Electrical from consideration.  This seems to work for most people out there, so I can only think there is something wrong with my project file(s) or blocks, or both.  Back to the drawing board 🙂

Thanks for all the help, Doug.  Kudos for trying.

[dougmcalexander]

I don't know what else to tell you. I make extensive use of spreadsheet editing and have been since the earliest versions. It always works. I do not recommend using the spreadsheet editing to change part numbers or blocks. Spreadsheet import only changes attribute values. It doesn't trigger further automation, such as changing a footprint when a part number changed that should require a new footprint image, and it doesn't trigger automated functions related to a block change. Just think of the spreadsheet update as a way to update attribute values, nothing further.

Also be very careful not to chsnge the drawing number assignments or data in the HDL column. The software needs that data in order to locate the components.

[rhesusminus]

Did you ever resolve this issue?

[pvpatel]

I have noticed that if I import data from spreadsheet to parent symbols in one drawing, AutoCAD electrical doesn't automatically update the child/peer references that are located in other drawings. I was expecting AE to automatically update the related symbols. Is there a command for updating parent/child/peer symbols? The update/retag doesn't work because it automatically reassigns TAG info.

[dougmcalexander]

Like I said in the previous post, the spreadsheet export, creates a spreadsheet with the values of the block attributes in ASCII format. You can use the power of Excel to make changes but when you Import from the spreadsheet all you are doing is overwriting attribute values. There is no downstream automation. For example, if you change the part number assignment of a schematic symbol for a circuit breaker and run the Import/Update, there will be no additional command trigger to perform a bi-directional update to change the associated footprint part number, and the possible subsequent swap-out of the footprint symbol itself. Such command sequences are only called up when you assign or change the circuit breaker with the AutoCAD Electrical Component Edit utility.

[Anonymous]

I know this is a really old thread now, but it is relevant.  (Using ACADE 2021)

When importing "From Spreadsheet", the information is taken correctly if "Active Drawing" is chosen.

If instead "Project" is selected, ACADE appears to be going through the process, opening & closing each drawing selected from the list, but the information is not pulled in.  (In this case, MFG & ITEM from the COMP tab).

Strange that it'd work for one & not both the same. 

So, thanks to dougmcalexander, I realized that my Excel Macro changed the cell format.

The solution for me was to select all the whole sheet & change the cells to "General".  (And I'm not sure if this next step is necessary, but it worked).  I then selected just the header & changed its format to "Text."  After that, importing to more than one drawing at a time was successful.