October 16, 2009 at 8:18 am
Hi,
I am trying to import a series of excel spreadsheets. The first column is a "pick list" of predefined values. However when they are imported into the SQL table each one is null. Is there a way of importing these columns or do I have to save them all as csv files (which does import the values)?
Thanks
Stuart
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
October 16, 2009 at 3:09 pm
If your question is "how to import the excel to a sql table" then
You can chose oledb source for excel and destination as oledb sql server destination and map it.
Then it should work.
If your question is something different i am sorry that i couldnt understand it properly. So can you please elabrate it.
Thanks
October 16, 2009 at 3:59 pm
OK - I'll try again.
In excel you can set cell(s) to be a value from a drop down box within that cell. The problem is, when I import the spreadsheet all of the cells with the drop down values are blank, the remainder of the table is populated as expected.
Hope this makes it clearer- in the mean time I'll try to find the correct term for this type of cell
It is a cell where data validation has been applied (I'll be googling that in the mean while)
Regards
Stuart
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
October 21, 2009 at 5:01 am
Step 1: copy the entire data from your spreadsheet
Step 2: Use the option of Edit Top 200 rows by right-clicking the table in SSMS
Step 3: Paste the data, and ensure that sequence of columns in spreadsheet and the sequence of columns in the table are same.
Hopefully this would be much easier to import your data.
October 21, 2009 at 5:10 am
Siddharth Mehta (10/21/2009)
Step 1: copy the entire data from your spreadsheetStep 2: Use the option of Edit Top 200 rows by right-clicking the table in SSMS
Step 3: Paste the data, and ensure that sequence of columns in spreadsheet and the sequence of columns in the table are same.
Hopefully this would be much easier to import your data.
??? sounds like a manual process & I don't really understand what you're getting at anyway.
I was thinking that this must be a driver problem, but thought someone more knowledgeable would step in.
My first step here would be to create a new calculated column in the spreadsheet and enter the simple formula (=A1, =B1 etc etc) to copy the drop-down column to the new column. Then try importing the new column - a hack, but might work.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2009 at 5:14 am
Isn't importing using a wizard a manual process ?
October 21, 2009 at 5:17 am
Except for your post, the word 'wizard' appears nowhere in this thread.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2009 at 9:21 am
1. Check those cells to see if they contain a formula
If they do then
2. Check Excel help for "Replace a formula with its result"
Unfortunately the only instructions for doing the replacement are manual activities.
Now this is not much help, but it might get your thinking turned on, and if you do develop an automated solution, please post it here to help others who may have the same problem.
October 21, 2009 at 9:38 am
bitbucket-25253 (10/21/2009)
1. Check those cells to see if they contain a formulaIf they do then
2. Check Excel help for "Replace a formula with its result"
Unfortunately the only instructions for doing the replacement are manual activities.
Now this is not much help, but it might get your thinking turned on, and if you do develop an automated solution, please post it here to help others who may have the same problem.
You are wrong in thinking that SSIS cannot import values from Excel which are the result of calculations. I just tried this and it works fine.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2009 at 9:51 am
I just expanded my testing to include cells populated via drop-down and these worked fine too. So there must be something more to this ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2009 at 9:55 am
Phil Parkin
You are wrong in thinking that SSIS cannot import values from Excel which are the result of calculations. I just tried this and it works fine.
Phil did the formula refer to another cell(s) on the same work sheet or did it refer to cell(s) on another work sheet in the same workbook?
October 21, 2009 at 10:05 am
bitbucket-25253 (10/21/2009)
Phil Parkin
You are wrong in thinking that SSIS cannot import values from Excel which are the result of calculations. I just tried this and it works fine.
Phil did the formula refer to another cell(s) on the same work sheet or did it refer to cell(s) on another work sheet in the same workbook?
Just the same worksheet. So I expanded the testing further to include in the formulas references to other worksheets in the same workbook and that worked OK too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2009 at 12:23 pm
Phil Parkin
Thanks for doing that additional testing.
As you said in a previous post on this forum. The OP may have another problem.
Again thanks for doing that additional testing.
November 12, 2009 at 7:19 am
Many thanks for your work and input to this.
Most confusingly, I have since tried this on various spreadsheets I created my self and didn't have any problems, it didn't matter how / where the "dropdown" was populated, it was imported correctly.
I have also done a sanity check and rechecked (from scratch) my method and I still get the null. The sheet is protected but, from my trials, I have found that this makes no difference.
A colleague of mine wrote a small access database to convert them to delimited text files. Not elegant but it works.
That still leaves me with something I don't like - a workround rather than a solution.
If I find out any more I will post it for completeness.
Thanks again for the help
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
April 28, 2010 at 11:50 am
I suspect the nulls are to due to the data in the dropdowns being of "mixed" types. The Excel connection manager looks at a sample of the first few rows of data and if the data in a field looks to be say numeric, assigns a numeric data type to the field. If data for that field beyond the sample size is text, the Excel connection manager doesn't know to with it and makes the value null.
The solution is to add the value of IMEX=1 to the ConnectionString of the Excel connection manager.
The connection string should look something like the below:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MonthlyInput.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
The IMEX=1 value basically tells the Excel connection manager to take the data as it is and not try to interpret the data type.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply