0

I was formatting data in an Excel sheet and saved it as an xlsx and was able to import it to SQL Server Management Studio. When I go to SQL though, not all of the data I imported was there, it stops at row 255, but I have more data than that. Also, in two of the columns it puts Null all the way down.

I've double checked and made sure that I didn't edit the data, and everything in my Excel sheet is fine, it's just when I import that things go wrong. When I import using the Server Import and Export Wizard, my data source is obviously the Microsoft Excel, file path is the xlsx file, version I have to choose is Microsoft Excel 5.0. Then I choose the destination: Microsoft OLE DB Driver for SQL Server because I can't use Native Client. I put in the server name and my database name and everything pulls over. When I look at the size, it has 255 next to each one... I'm not sure if that has something to do with it limiting what can be accessed in SQL, but I'm thinking it is. I'll keep plugging away, but I hope someone may have an answer.

Jessica May
  • 1
  • SQL Server tries to guess the column width using a sample of rows in your spreadsheet. If the guess is wrong, some suggest filling each column in the first row of your spreadsheet with placeholder strings wider than the widest strings in your data. Another solution I've seen is to use an option in the [Provider string/ExtendedProperties string](https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/) called IMEX (*IMportEXport* mode). I couldn't find an authoritative reference for its settings, but you could experiment with values IMEX=0, IMEX=1 or IMEX=2 to see if one works. – kofeigen Apr 16 '23 at 05:08
  • change your excel to `Microsoft excel 97-2003 `(`xls`) not (`xlsx`). and choose `Microsoft excel 97-2003` for version.it should work. both excel file and version should be same. – RF1991 Apr 16 '23 at 05:09
  • Excel 5.0 predates even Office 95. What versions of Excel are listed when you're choosing a data source in the SQL Server Import and Export Wizard? – AlwaysLearning Apr 16 '23 at 05:10

1 Answers1

0

TL;DR: Make sure the majority of the first eight rows of your spreadsheet have character lengths that exceed the maximum expected character length of your data for columns at risk of being truncated.

To understand what is happening, these references should help:

  1. Microsoft Excel Data Types
  2. The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database
  3. Data Type Limitations

Microsoft Excel

The main point is from the article Microsoft Excel Data Types where it says:

Creating a TEXT column of zero or unspecified length actually returns a 255-byte column.

Microsoft Access drivers

Microsoft Access database drivers are often used to link Excel spreadsheets to SQL Server. These drivers may be acting as described in article #2 above where it says:

When you link an Excel spreadsheet to an Access database, and the columns of the Excel spreadsheet contain more than 255 characters, the columns are mapped to Memo data types, and the Format property of the Memo field is set to @. Therefore, Access treats the Memo field as a text field, and you can view only 255 characters of data.

Solutions

Approaches to solving the problem have been discussed in other Stack Overflow questions (example). While I have not seen a definitive, authoritative solution, the only approach that I have tried and found worked was suggested in a Stack Overflow answer by bastos.sergio. For a column with truncation problems, it is suggested to:

garantee that the majority of the first eight rows in that column exceed 255 characters in length.

In one of my tests I used a spreadsheet cell having over 1024 characters in the first row (below the header). All characters were successfully retrieved with a SELECT statement from the Linked Server in SQL Server.

kofeigen
  • 1,331
  • 4
  • 8