Excel, Get & Transform Data, From Text/CSV File, Mixed Data in a Column

Question

Monday morning and Excel has changed.

Appears to be an Office 365 update.

Ver 1704 Build 8067.2115

Now uses Get & Transform Data, From Text/CSV File

My csv file has a text header describing the contents of the file

The data below the header:

Column 1 is the Data and Time

Columns 2, 4, 6, etc is text listing the channel number

Columns 3, 5, 7, etc is numerical (temperature in this case)

If I let "From Text/CSV" choose the column data types, it selects Text for column 1.

When I chart the data with column 1 as the horizontal axis, it displays numbers from 0 to 12,000.

If I change the chart horiz axis type to date, it starts with 1/1/1900 to 11/7/1932, which is 0 to 12,000.

If I highlight only the temperature data in column 1 and try to change the data type, it has no effect.

If during the import, I change column 1 to Date, it doesn't import the text header data (which I need).

It will chart with the correct Date Time on the horiz axis.

I enable the Legacy import and that still works.

I'm open to learning how to use the new Get & Transform Data, but am not finding much for instructions.

Answer 1

Hi Craig,

Firstly, sorry for the incorrect information mentioned above. You are correct. If we use the default way to import CSV, don't change column 1 type, we cannot get the correct chart with X/Y (Scatter) even when we manually change column 1 format. The only method to achieve your requirement is to use the legacy importing wizard.

We understand the experience changing is not convenient for you. I will suggest you submit this experience feedback via Uservoice mentioned above. So, the related team can hear the voice from you.

We appreciate your understanding.

Tim

Answer 2

Hi Craig,

Thanks for sharing with us these files.

Could you help us record the following three videos? We want to know the detailed steps about how you use the legacy wizard to import the file and how you set up those charts. You may just use a mobile phone to record videos and upload it via replying to my private message.

Video 1. Disable legacy Wizard. Import the csv file. Create the first chart. Change column format. Create the second chart with format changed.

Video 2. Enable legacy Wizard, Import the csv file. Create the Chart.

Video 3. Import the csv file with column changed to Date format. Create the Chart.

We understand it will take you some time to record videos. However, these videos will let us know more about the situation. They will help us reproduce the importing and chart creating behaviors from our side.

Best regards,

Tim

Answer 3

Tim, 

I sent the excel files to your email.

Also, I did not initiate an update to Office.

The first thing I needed to do this morning was chart 3 days of data and Excel importing was different.

I assumed it was an update.

Craig

Answer 4

1.  Here's the version info:\

2. Enabling Legacy Import per Unified Get & Transform writeup under Whats New in Ver 1704 (8067.2115)

I checked all of the boxes under Show Legacy data import wizards.

3.  I'll send you a stripped down csv file.  Actually, I'll send you several with notes.

     I don't know how to upload to the cloud.  I'm old school.  Clouds are for rain.

4.  I used Snipping Tool to capture the screen shots, but it won't let me capture the drop down boxes.

Answer 5

Hi Craig,

To better understand the situation, I’d like to confirm the following things:

1. Do you mean this change comes after updating to 1704(8067.2115)? Please open Excel>File>Account>capture a screenshot of all information under Product Information.
2. How do you enable the “legacy import” function?
3. If it’s convenient, please attach that CSV file via replying to my private message. You may also upload it to a cloud storage and share a guest link here. So, other community members can share their experience here.
4. Capture a screenshot about the importing result with “legacy import”. We appreciate it if you can share with us some screenshot about your settings during the importing process.

Best regards,

Tim