Importing an Excel file to a list - how to control the column types

Question

I am attempting to import an Excel file into SharePoint using Create List > Import Spreadsheet.

Several columns are just short text fields < 100 chars and with no formatting/line breaks etc.

Some of these fields get imported into "Single lines of text" fields (which is fine), however some get imported as "Multiple lines of text" fields.

I can't see any reason why they would, or any way to influence the column type created.

Is there a way to influence or change the column type chosen during an import?

Answer 1

I have encountered the same question with you. I don't want imported text columns tobe multi-line, since multi-line columns cannot be added into a calculation column. There seems have no official solution for this, but you can try the work around by this post: http://groups.google.com/group/microsoft.public.sharepoint.windowsservices/browse_thread/thread/9e8872b80dee9c44/20ec2d9e10224eaf?show_docid=20ec2d9e10224eaf&pli=1 ,at least, it works for me.

Answer 2

There's a much easier way that I just discovered - edit the multi-line column and save it as plain text only. Go back in to edit it a second time, and you can change it to a single line of text.

Answer 3

I can confirm that, depending on some words in the cells of the Excel column, the Excel column will be converted in the Sharepoint list to either single line of text or multiple lines of text.

In my case, the word "Supervisor" in a cell of a the Excel column made it multiple lines of text in the Sharepoint list. I changed "Supervisor" to something else and the Sharepoint list column became single line of text !

Actually in order to create a Sharepoint list by importing an Excel sheet, there is no need to import the whole Excel content at import time. That increases the probability of presence of these magic words.

In the Excel sheet, one can create a kind of "template" row just below the header row and verify that when importing just the header row and template row in Sharepoint, one get the right single / multiple line(s) of text columns in the Sharepoint list. When the Sharepoint list is correctly created, it is possible to Actions/Edit in Datasheet this list and copy the whole Excel content and paste it into the edited list.

At the opposite, to force an Excel column to be converted into a Sharepoint list multiple lines of text, a simple tip is to create, still in this Excel template line, a cell content with 2 lines (e.g. "ABC" [Alt]+Enter "DEF").

In the Sharepoint list it is of course easily possible to convert a single line of text column to multiple lines of text with Settings/List Settings and clic on the column name. The opposite is not possible and the only workaround I know about is to create a new column as single line of text, Actions/Edit in Datasheet, and copy the multiple lines of text column data into this new colum, then delete the source column.

Answer 4

In my case I wanted to export Excel cells in a single line text format so that it could be sorted and filtered in Sharepoint list.

But I found the same problem : some cells were misteriously considered as multiple lines text whereas there was only few caracters in them. After some experiments I've found that the problem happens when the text is exactly 10 caracters ... I don't know why ... Add or remove some caracters and it works.

So for each cell I added an extra text 'xxx', made the export to Sharepoint, then edit the list and filtered with this extra text and removed it. Not really enjoyable but this worked for me.

Answer 5

The best work around I've found (with help) is to 1) Let the import complete with the multiple lines 2) Go in to Sharepoint > List Settings 3) Change the column properties from text to plain text, and number of lines to 1, 4) Save, exit and then reopen the Column properties 5) YOu should now have the option to change the multi-line to single line

You can see the original answer posted by Victor Chataboon here: http://social.technet.microsoft.com/Forums/office/en-US/6b49fb45-d9b4-40ec-baeb-acdc988c1aa6/convert-multiple-lines-of-text-to-single-line-of-text?forum=sharepointgeneralprevious&prof=required

Answer 6

We ran into this extremly weird bug today.

Steps to reproduce:

(0.) Have a sharepoint site available where you can export things from excel. Any site will do.

1. Write Some stuff in Excel, like

    klas von klas 1
    Jan 2
    ta fram af 3
    Ulf 4

(this would be 8 cells spanning A1 to b4)

1. Select the entered values and choose Insert (I think, It is called infoga in swedish) and then Table in the ribbon.

2. Mark any cell in the table and choose Design (Table tools) and Export to a Sharepoint List. (You don't actually have to click the final ok button, so nothing will be exported in this test)

3. Write the url of a sharepoint site and a name for the table. Click next. Now, Excel may or may not tell you that one of the columns are of type Text(Multiple rows). And if so, the column "Key cell(?)" (Nyckelcell) will tell you the first cell in A1 ... An that excel thinks is a multiple line text.

Here are some cell values that my project manager used that triggered the bug:

• SQL Backup
• Ta fram af

We have tried these words on multiple client computers and multiple sharepoint sites, and we can reproduce the error everywhere. We are using a swedish language version of Excel 2010 if it matters.

"ta fram af" seems pretty innocent, but SQL Backup made me wonder if it was some kind of internal encoding to prevent injection attacks. It appears "Drop Table" is also considered multiple lines. But that may have been just a coincidence because I tried lots of other injection smelling lines, and they went through.

To give you some background on why this multiple line business is important: When a table is exported to sharepoint like this, the first column consisting only of single line text becomes the "Title" field and gets the ECB menu in sharepoint. If one of the lines is marked as multiple lines, Excel will move on to the next column and the resulting list in SharePoint will have the wrong title column, or no title column at all.

edit: Yes, I now realise that this should have been an "improve the question" post and not an answer post.

edit 2011-12-29: Kit Menke (below), I believe this is the exact same problem as the original poster had. When exporting excel data to SharePoint, Excel treats some cells as multiple lines of text in an arbitrary, but deterministic way. Most sentances are treated as single line of text, but some magic words are treated as multiple lines of text. My post includes a step by step guide to recreate the bug. The bug is so random and weird that I think you have to see it to believe it. It is not a user error, but an actual bug in Excel.