I am trying to upload the following table from an Excel worksheet into SQL Server (keep in mind this is an Excel workbook and NOT a csv file). When I upload the below data, SQL Server cannot show rows 2 and 4 as varchar. The data gets converted to NULL.
| color | F2 |
|---|---|
| red | 1,4,6 |
| blue | NULL |
| green | 3,5 |
| orange | NULL |
I expect SQL to show the table exactly as is but am instead getting the following output:
| color | F2 |
|---|---|
| red | 1 |
| blue | NULL |
| green | 3 |
| orange | NULL |
How do I get the data to convert properly? I have already tried explicitly changing the data types to varchar(255), varchar(max), float, and bigint, but the values still get converted to NULL.
I also tried converting the commas to another type of delimiter (e.g. hyphens and slashes) but still the same issue.
ANSWER-- Martin Smith gave the correct answer as follows:
You need to convert the data to data type 'text' in Excel. BUT you have to convert to 'text' and THEN paste the data, otherwise the value still shows as NULL. Originally, I tried converting the already pasted values to 'text' and still got NULL.
If re-pasting the data AFTER converting the Excel columns to text does not work, copy the data from Excel into Notepad or Notepad++, then recopy the data, and then repaste back into Excel. This will force the data to paste as 'text'. Repasting the data as 'values' did not work.