August 6, 2013 at 9:45 am
I have a spreadsheet with 88 columns and about 5000 rows which I need to import into Sql Server 2008.
The import wizard doesn't work - it has a stab at it and imports a few rows and the rest of the data is null.
I have tried creating a table with 88 nvarchar(255) columns and pasted a couple of hundred rows from the spreadsheet.
But, while most of the data pastes okay, some just seems to be missing.
In the first row, 80 odd columns get the data in, but the 81st has data that starts with a pair of inverted commas but the data is not complete. The rest of the row is empty.
The next row is okay.
Anyone any tips on how do to this please? Anyone got a list of hidden characters in Excel that I can find and remove? (The pair of inverted commas mentioned above do not appear in the spreadsheet)
August 6, 2013 at 12:04 pm
Is this a one time import or are you trying to setup a repeatable process?
If one time I would save the Excel as a delimited file and do the import.
August 6, 2013 at 2:18 pm
sku370870 (8/6/2013)
I have a spreadsheet with 88 columns and about 5000 rows which I need to import into Sql Server 2008.The import wizard doesn't work - it has a stab at it and imports a few rows and the rest of the data is null.
This is most likely caused because the data types change inside a column. The provider guesses the data type by looking at the first 8 rows. If they are all integers, it will assume the column contains only integers. But if you have a string at a later position (which cannot be converted to an int), it will be imported as NULL.
This is not something you can fix in the import wizard. You would need to save the SSIS package and edit it.
But if this is a one time import, just go with the CSV method Chrissy mentioned.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 5:13 pm
This is a one time import.
I have saved the Excel file as a csv file and tried to import it.
I have set all the fields in the table that is going to be created to be varchar(1000) (which ought to be way too big) but endless 'truncation error' messages occur.
The spreadsheet has data in the first 88 columns. The csv file has about 200 comma separated values in each row. How can I export just the columns with data?
August 6, 2013 at 5:15 pm
Are their commas in your data? If so try a tab or pipe delimited file.
August 6, 2013 at 5:32 pm
Also if you know your dataset in Excel has a certain number of rows and columns select and paste just the data into a new sheet and then save you delimited file.
Sometimes there is invisible junk in Excel.
As Koen said sometimes the wizard can't handle the data and you need to save you package and fix it in BIDS.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply