September 14, 2007 at 6:19 am
I am trying to solve a problem for a client where he wants to upload an Excel spreadsheet that will do updlates to a table (table B). The problem is that the user has no concept of formatting and the spreadsheet cell and column definitions are all over the map. My attempted solution is to just bulk upload the spreadsheet into an SQL file (table A) and then inspect, edit, and process the resulting table with VB code to do the appropriate updates to the main table (table B).
I am having a very unsual problem with importing the Excel file into an SQL table. When I run the upload via an SQL Import wizard all rows and column contents are uploaded to the file properly (then I save the package). However, when I run the package one of the columns seems to ignore text in any cells of that column, for example
Excel Source / Table Result
12345 12345
abcde NULL
67890 67890
The receiving table's column is defined as nvarchar(20). This one really has me stumped. Any ideas or suggestions would be appreciated. I hope this wasn't TMI but I wanted to be as clear as possible.
Thanks, John
September 27, 2007 at 10:47 pm
May I know that excel column's first 10 rows are numerical?
September 27, 2007 at 11:05 pm
Hi John,
I am currently building a data warehouse as a consultant in conjunction with a Microsoft Consultant and have asked this question. Using a data conversion process I have found no quick solution. If it is possible when the spreadsheet is filled in from where ever the source to prefix the number columns with a single '.
I know this is sometimes not a desired solution but we always go back to the client and ask them to do this. If they are unable to do this for any reason, we then convince them to use a flat file system (CSV) to get around this issue.
I have tried to do this with Katmai (spreadsheet column with mixed text and numbers) and the same error occurs. Have not tried ensuring the first 10 rows are test values though like suggested by indumon.das
I know this may not answer your question, but may provide some insight into possible workarounds.
September 28, 2007 at 5:45 am
Thanks to both of your for some valuable points in the right direction.
The incoming spreadsheet may or may not have rows with numberic values in the preceeding rows; however, I have edited it to have alphas in the first row at the very least with no luck.
Also it was helpful to know that this problem is being experienced by others (it helps me to not to feel so all alone ;>).
I had to jump on some other projects but I am going to convert the file to a CSV and then try this again. I'll let you know what happens.
Thanks.
John
September 28, 2007 at 5:46 am
Ignore the data type in the table for a minute and look at the data type of the INPUT buffer on the data reader or OLEDB source in your data flow task (I hope that described where to look for you).
Implicit conversions in SSIS make it possible that you have a numeric column on the input side of the control with NVARCHAR on the output side of the control. This will hide your problem from plain view and often just convert text data to NULL.
When you put the control into your data flow and choose the Excel data source, it immediately samples a small group of rows to try to get data types. If you do not have any character data in the first few rows of the spreadsheet, it can select a numeric data type. When figuring out the output for the control, it sometimes gets a different sample data set which would cascade down to where you created your table.
I make it a point when creating an Excel file import to make the first row have all of the appropriate data types in it - even if that means adding a dummy row and removing it later in the data flow. Another option is to save the spreadsheet as XML - SSIS likes XML.
September 28, 2007 at 8:10 am
Got it. Thanks a million. I'll give it a try and let you know.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply