Import worksheet command - problem with cells that start with "E-"

[Anthony Reed]

Hello all,

 

I'm trying to import a CSV into my VW 2018 file via File>Import>Worsheet. Up until this point my workflow has been working fine, but I'm now trying to import the latest version of this CSV generated by Excel that now has cells that start with "E" and have a digit or two after, sometimes with a hyphen.  Unfortunately, it appears that VW tries to do math on those cells and replaces them with 0e+000 and displays them as 0 if there's a hyphen (see "Worksheet View of Imported CSV.PNG) or some negative number if there isn't a hyphen

 

What I've tried so far:

- Restarting Vectorworks and my machine. Doesn't seem like the kind of problem to be resolved by this but it was worth a shot.

- Using a Tab delimited worksheet instead. Same problem.

- Checking that excel exported the data properly (see "Excel view before exporting CSV.PNG" and "Notepad++ view of imported CSV.PNG"). The exported file opens in other programs just fine.

- Changing the cell format in the VW worksheet via Format>Cells... Doesn't help, and changing them to text just makes them display "0e+000" instead of 0.

- Manually typing the data into the cells (due to the volume of cells that will eventually be involved in this, it's not a long term option but just as a test). It did work though if I first changed the cell format, but otherwise didn't work. 

 

Is there a setting somewhere for reverting all cell formats to text when importing CSVs? Do you know of another setting somewhere that might cause this or some workaround I could consider?

 

I appreciate any help you can provide, thank you for your time!

 

 

EDIT - If it helps, a friend suggested I try to recreate the problem from scratch, so I made a new csv and vwx, imported it as I had been and got the same problem.

 

Edited October 17, 2019 by Anthony Reed
adding more information

[Anthony Reed]

To anyone who happens to stumble upon this post looking for the same answer, I haven't found a setting to disable the automatic-math that's happening, but I have found a workaround:

 

File > Import > Worksheet

Select all cells (or at least the cells that exhibit the problem)

Format > Cells... > Number tab (default tab) > Select the "Text" radio button > OK

Select cell A1 only, then reimport the same worksheet the same way

 

I'm not certain exactly how it works, but it appears that importing a worksheet while a worksheet is open overwrites the worksheet from the selected cell. Unfortunately, you can't overwrite a database worksheet this way, as that would completely solve another problem I'm having.