topic Re: some column are not imported form excel file in Discussions

some column are not imported form excel file

matthias_bruchh — Sat, 10 Jun 2023 20:43:20 GMT

Hi,

I have downloaded an excel file from https://www.nrel.gov/transportation/assets/docs/battery-failure-databank-revision1-oct30.xlsx

I want to import the third sheet in JMP, but some of the columns are not imported (e.g. the column Test-Series).

(Removing the data filters in the excel file didn't help.)

I guess I could copy+paste the missing columns into JMP, but maybe there is a better way?

Re: some column are not imported form excel file

txnelson — Fri, 19 Feb 2021 13:35:04 GMT

I can replicate your findings, using the Excel Wizard. I can not see why the issue is happening. Maybe someone with stronger Excel experience can see the issue.

However, the data do read in properly if you use the Excel JMP Addin.

Re: some column are not imported form excel file

dale_lehman — Fri, 19 Feb 2021 13:38:54 GMT

This is very strange and I'll be interested to hear if anyone can resolve this. I can say that the formatting for most (perhaps all) of the columns in Excel is a number format. When I change them to text formatting, a few of the columns now import correctly - but most do not. I have no idea why the columns are showing up as blank. For example, changing the formatting to text brings in the S-FTRC-Generation column correctly, but the Test-Series column is still missing.

Re: some column are not imported form excel file

briancorcoran — Fri, 19 Feb 2021 14:20:32 GMT

Hello Matthias,

The problem, and this is almost always the case, is that the columns are explicitly typed as numbers with the Excel worksheet. The engine that we use on Windows to read the files actually does a good job of showing us the types, which in this case is to the detriment of the desired outcome. When the type is not General, we strictly honor the typing. When we convert the numeric, of course it comes out missing. This behavior has been requested by users over the years (“just give me what I asked for if I went to the trouble of typing it”), but it has its downsides.

The solution for you in this case is to type the columns as Text or General and then import it. I’ve attached an example where I typed it as Text, but again General will work.

I hope this helps,

Brian Corcoran

JMP Development

Re: some column are not imported form excel file

matthias_bruchh — Fri, 19 Feb 2021 15:39:01 GMT

Thanks Brian,

The "general" works fine as you said it would. (And I see the point for keeping the number format if it's specified. I don't see why they formatted text input as numbers...)

Another question: The first column of the excel file contains links. I there a way to keep these during the import?

Best regards,

Matthias

Re: some column are not imported form excel file

dale_lehman — Fri, 19 Feb 2021 17:20:56 GMT

I don't understand why this does not work for me. I had tried changing the format - as you did in your attached file. But when I open it in JMP, the columns are still missing. So, why is it not working?