SQL 2012 Import from Excel fails when blank
I was given a spreadsheet with item numbers and alternate item numbers. There are about 800 rows in the spreadsheet and it looks like this:
ITEMNMBR,ALTITEM1,ALTITEM2
100842,100942,10142,10242,
100843,100943,10143,,10344
100844,100944,10144,10244,
100845,100945,10145,10245,
ETC
I am just clicking Tasks->Import inside SSMS. ALTITEM1 and ALTITEM2 import fine because no rows are missing any values and they correctly came over as nvarchar(255) as all columns are defined as text in Excel. However, ALTITEM3 and ALTITEM4 for same strange reason get converted to float even if I tell SSMS to treat them as nvarchar. How do I prevent this conversion? If I do tell it to use nvarchar the import does not work at all because it tells me invalid conversion. If I do not tell it to convert them all the values come over as NULL.
ITEMNMBR,ALTITEM1,ALTITEM2
100842,100942,10142,10242,
100843,100943,10143,,10344
100844,100944,10144,10244,
100845,100945,10145,10245,
ETC
I am just clicking Tasks->Import inside SSMS. ALTITEM1 and ALTITEM2 import fine because no rows are missing any values and they correctly came over as nvarchar(255) as all columns are defined as text in Excel. However, ALTITEM3 and ALTITEM4 for same strange reason get converted to float even if I tell SSMS to treat them as nvarchar. How do I prevent this conversion? If I do tell it to use nvarchar the import does not work at all because it tells me invalid conversion. If I do not tell it to convert them all the values come over as NULL.
ASKER
That is what I tried and that failed. I even tried inserting a bunch of rows with X's. Isn't there a setting where you can tell it to read some many records as it tries to determine the column type? The only fix I had for now was to create 2 additional spreadsheets and in each column eliminated all blanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thnaks
Or you try a quick work-around. Add a dummy first row to the spreadsheet and put a string of 50 x's (xxxxxxx...) in the columns that are now being treated as float. That should force Excel to treat them as (n)varchar. Then, after you load the spreadsheet into SQL, delete that row.