August 25, 2014 at 2:35 pm
I am using the DTS wizard and having problems importing excel into an existing table.
Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Can someone provide a link or an example on how this may be accomplished?
Thanks.
August 25, 2014 at 2:42 pm
cbernardes (8/25/2014)
I am using the DTS wizard and having problems importing excel into an existing table.Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Can someone provide a link or an example on how this may be accomplished?
Thanks.
Quick thought, you can change the destination mapping in the appropriate step in the Wizard, isn't that what you need to do?
😎
August 25, 2014 at 2:47 pm
http://msdn.microsoft.com/en-us/library/ms137643.aspx
Data types. The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
?
Numeric double-precision float (DT_R8)
?
Currency currency (DT_CY)
?
Boolean Boolean (DT_BOOL)
?
Date/time datetime (DT_DATE)
?
String Unicode string, length 255 (DT_WSTR)
?
Memo Unicode text stream (DT_NTEXT)
•
Data type and length conversions. Integration Services does not implicitly convert data types. As a result, you may need to use the Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into a non-Excel destination, or to convert non-Excel data before loading it into an Excel destination. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following:
?
Conversion between Unicode Excel string columns and non-Unicode string columns with specific codepages.
?
Conversion between 255-character Excel string columns and string columns of different lengths.
?
Conversion between double-precision Excel numeric columns and numeric columns of other types
August 25, 2014 at 2:49 pm
Eirikur Eiriksson (8/25/2014)
cbernardes (8/25/2014)
I am using the DTS wizard and having problems importing excel into an existing table.Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Can someone provide a link or an example on how this may be accomplished?
Thanks.
Quick thought, you can change the destination mapping in the appropriate step in the Wizard, isn't that what you need to do?
😎
Thanks for your reply. When I go through the wizard, it does not allow me to chance the Source Type for my Source Column.
e.g.
Source Column "ID", Source Type "Double"
Destination Column "ID", Destination Type "int"
There is not an option to change the Source Type.
August 25, 2014 at 2:53 pm
rxm119528 (8/25/2014)
http://msdn.microsoft.com/en-us/library/ms137643.aspxData types. The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
?
Numeric double-precision float (DT_R8)
?
Currency currency (DT_CY)
?
Boolean Boolean (DT_BOOL)
?
Date/time datetime (DT_DATE)
?
String Unicode string, length 255 (DT_WSTR)
?
Memo Unicode text stream (DT_NTEXT)
•
Data type and length conversions. Integration Services does not implicitly convert data types. As a result, you may need to use the Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into a non-Excel destination, or to convert non-Excel data before loading it into an Excel destination. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following:
?
Conversion between Unicode Excel string columns and non-Unicode string columns with specific codepages.
?
Conversion between 255-character Excel string columns and string columns of different lengths.
?
Conversion between double-precision Excel numeric columns and numeric columns of other types
Yes, that is what I am seeing. But I was hoping I can find a walk-through exercise to understand the process from beginning to end.
I am just failing to get through the steps on conversions.
August 26, 2014 at 4:58 am
cbernardes (8/25/2014)
Eirikur Eiriksson (8/25/2014)
cbernardes (8/25/2014)
I am using the DTS wizard and having problems importing excel into an existing table.Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Can someone provide a link or an example on how this may be accomplished?
Thanks.
Quick thought, you can change the destination mapping in the appropriate step in the Wizard, isn't that what you need to do?
😎
Thanks for your reply. When I go through the wizard, it does not allow me to chance the Source Type for my Source Column.
e.g.
Source Column "ID", Source Type "Double"
Destination Column "ID", Destination Type "int"
There is not an option to change the Source Type.
Ok, so you are using the Import/Export Wizard.
SQL SERVER Import/Export Wizard, On the third step of the Wizard if you do not change anything, wizard will place a your excel Sheet name along with a $ something like this (Sheet$), if you simple click the next button and Finish it, the data will be imported into that table which can be called Staging table in this context. after the import complete successfully, insert data from the newly created table to your actual table with all the conversion you required.
August 26, 2014 at 6:08 am
cbernardes (8/25/2014)
When I go through the wizard, it does not allow me to chance the Source Type for my Source Column.
That's because they're set in excel, you'll need to tell the wizard the type of the target column
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2014 at 4:23 pm
twin.devil (8/26/2014)
cbernardes (8/25/2014)
Eirikur Eiriksson (8/25/2014)
cbernardes (8/25/2014)
I am using the DTS wizard and having problems importing excel into an existing table.Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Can someone provide a link or an example on how this may be accomplished?
Thanks.
Quick thought, you can change the destination mapping in the appropriate step in the Wizard, isn't that what you need to do?
😎
Thanks for your reply. When I go through the wizard, it does not allow me to chance the Source Type for my Source Column.
e.g.
Source Column "ID", Source Type "Double"
Destination Column "ID", Destination Type "int"
There is not an option to change the Source Type.
Ok, so you are using the Import/Export Wizard.
SQL SERVER Import/Export Wizard, On the third step of the Wizard if you do not change anything, wizard will place a your excel Sheet name along with a $ something like this (Sheet$), if you simple click the next button and Finish it, the data will be imported into that table which can be called Staging table in this context. after the import complete successfully, insert data from the newly created table to your actual table with all the conversion you required.
Understood. But in the staging table, I found out it still doesnt convert properly. But that is okay. After more research, it looks like SSIS is the way to go where I can refine my imports and convert the data types where need. Thank you for your reply.
August 26, 2014 at 4:24 pm
Perry Whittle (8/26/2014)
cbernardes (8/25/2014)
When I go through the wizard, it does not allow me to chance the Source Type for my Source Column.That's because they're set in excel, you'll need to tell the wizard the type of the target column
Thanks your for your reply. As stated in my other reply before this, I found out to change my data types properly, the SSIS is the way to go.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply