0

I'm exporting alphanumeric data from a SharePoint list (single line of text datatype), but when my Excel desktop app opens the file, it changes "7-3" to "07/03/2021" and displays it as "07-March". I know that the export is working correctly because if I open the file in Notepad++ instead of Excel I can see that the value is still "7-3".

This is what I see in SharePoint before I export: 7-3 from SharePoint

This is what I see when I view the exported CSV in Notepad++ : "7-3" in Notepad++

But this is how it appears in Excel when I first open the file: 07-Mar in Excel

And this is what I see if I edit the cell value in Excel: 07/03/2021 in Excel

I tried changing my regional settings to use a slash as a date separator instead of a dash, but that doesn't prevent Excel from seeing "7-3" as a date... it just makes Excel display that data as "07/03/2021" instead of "07-03-2021" in edit mode, as shown above. My point is not that I want it to be changed to a date with slashes, but that I want to retain the value I exported ("7-3").

I need to find a way to provide my users with a simple, predictable export/import process that keeps the data intact for a process that will be repeated many times, so it's not feasible to ask them to manually force the format for every column that may have this type of information every time they do an import. Is there a one-time setting/solution that will make Excel keep the existing alphanumeric data when opening a CSV file?

Improve this question
6
  • 3
    This is an age old complaint about Excel. If you just open the text file, excel will always try to convert any text that looks like a date into a date. It will convert the string 10E3 to 10000. The only reliable way is to use the import wizard where you can set the column type as text during the import. If the column is not set to Text BEFORE the data is inserted it will always try to convert.
    – Scott Craner
    Sep 9, 2021 at 20:48
  • One way is to edit the text file so that the character separating fields is followed by a single quote. For example, if , is the separator, search and replace it to ,'. Of course, all such columns will be text only, so you would need to not add the quote for numerical fields.
    – DrMoishe Pippik
    Sep 10, 2021 at 1:07
  • What version of Office did you use? If you can use Power Query in Excel, you can use from text feature in Power Query editor and change the column cell format: docs.microsoft.com/en-us/power-query/connectors/textcsv
    – Lee
    Sep 10, 2021 at 9:23
  • So defining datatype in target worksheet will work if it's always the same worksheet, but the user can't just open the file, they'll need to go through the extra steps for the import wizard every time - extra training and time required. Search & Replace for the delimiter requires an extra step each time but would mess up fields which are actually dates, so likely wouldn't be feasible for me (the extra steps each time would be an issue even if it didn't mess up other date fields). I haven't used PowerQuery but will look into it. It's unfortunate there's not a setting to address this behavior!
    – Darryl
    Sep 10, 2021 at 16:40
  • What about distributing the data as Excel files (.xlsx or .xlsm)?
    – Ron Rosenfeld
    Sep 11, 2021 at 1:54

1 Answer 1

Reset to default
0

Say we have gary.csv like:

enter image description here

Here 5-11 is a height in feet and inches and 174 is weight in pounds.

Rather than double-clicking the file to open it, use Import Data in the Data Tab. Tell the Import Wizard the the field is text. This results in:

enter image description here

Improve this answer
1
  • Unfortunately this isn't feasible in a large organization where users with limited technical knowledge expect to double-click to open a file. There's a high cost to training thousands of users to use the import wizard every time, provide support when they do it incorrectly, and find and fix errors when they forget. It also costs them time (compared to just opening directly) every time they open the file. It results in a huge amount of extra work for what "should" be simple in Excel - just show me the data I provided, the way I provided it. MS dropped the ball on this one.
    – Darryl
    Sep 28, 2021 at 16:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .