October 6, 2008 at 10:55 pm
Hello All,
SQL Server Management Studio 2008 is provided with import/export wizard. I am trying to import data from excel 2007 but, i am finding many difficulties and not able to achieve successful import.
Has anyone tried this? Please suggest.
Thanks
Prashant.
October 6, 2008 at 11:07 pm
Can u tell us exactly what issues/errors you are facing?
"Keep Trying"
October 7, 2008 at 9:18 am
What are the errors?
Have you tried importing it as Excel 97/2003 .xls format (instead of 2007)?
Also this may provide some insights (to use MS Office 12 OLE engine instead of Microsoft Excel)
http://www.sql-server-performance.com/articles/biz/How_to_Export_Data_to_Excel_2007_p1.aspx
October 8, 2008 at 3:50 am
I tried importing it as Excel 97/2003 .xls format (instead of 2007) and it is failing.
Also I tried to follow as per the below link for SQL 2008 and found the below error after pressing the "Test Connection" button
"Test Connection failed because of an error in initializing provider. Unrecognized database format 'c:\XXX\test.xlsx'
Below problem what I am facing with my normal strategy
1. In the first wizard ("Choose a Data Source") selected data source as "Microsoft Excel"
2. Selected the .xlsx path which has many sheets that needs to be imported into respective tables
3. Excel version selected as 2007
4. In the second wizard ("Choose a Destination") selected destination as "Microsoft OLE DB Provider for SQL Server"
5. Server name selected
6. Authentication - Selected SQL server authentication
7. Selected the database where i want to import data
8. In the third wizard ("Specify table copy or query), I have selected Copy data.....
9. In the fourth wizard ("Select source tables and views") I have selected list of all the tables and mapped them to the corresponding tables
10. In the fifth wizard ("Review Data Type Mapping") I am finding below error message
"Found X unknown column type conversion(s). The package will not not be run.
October 8, 2008 at 4:49 am
Hello - I've done a lot of Excel importing into SQL and it's a real pain. Sometimes values simply don't get imported, and other times it makes a guess at the column type which is just wrong, but can't be fixed.
The most painless solution I have found is to save the sheet as a flat file (tab-delimited or whatever) and use the Flat File provider to import. The only thing to watch out for is to ensure your Output Column Widths are big enough on the for the data in the table. It's under 'Advanced' when you choose the data source. If you don't make them big enough the import will fail.
Really, doing it this way has saved a lot of wasted time.
Hope that helps.
October 8, 2008 at 5:17 am
Alistair Potts, Thanks for the info.. but, it will not work. I need to import 10 sheets from workbook to SQL server management studio 2008.
I tried with one sheet. I converted that into flat file and tried to import the same into the database and it gave me below error message.
- Executing (Error)
Messages
Information 0x402090de: Data Flow Task 1: The total number of data rows processed for file "XXX\Test.txt" is 5.
(SQL Server Import and Export Wizard)
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "X" (22) to column "XX" (78). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "X" (78)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "XX" (78)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (58) failed with error code 0xC0209029 while processing input "Data Conversion Input" (59). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
--- Please NOTE: As I stated earlier, I want to use Excel 2007 only to import to database. This excel workbook has many sheets. (Please refer my previous post messages for more information)
October 8, 2008 at 5:47 am
OK, well it looks like you have a mismatch between what you think you have in your Excel columns and what the engine thinks you have.
This is sometimes caused, for instance, by trying to import an int column, and it having an empty cell in Excel. That will cause it to fall over.
You haven't said whether you're using the wizard to create a new table, or import into an existing one. To get it working, I suggest you "Create destination table" (you'll need to drop the existing one first). You'll find that under 'Edit Mappings'. For the Types, I strongly recommend you set them ALL to nvarchar and nullable, and make sure they're big enough to take the data. You can always CAST them later, or send them to a 'proper' SQL table with the correct data types, but the first thing is to get the data in.
Start with just one sheet. If that works, run it again adding the sheets one by one, and doing the same thin regarding the mapping.
That should get it working.
October 8, 2008 at 5:54 am
I just have to insert data to the existing tables. Let's say i have 10 tables and each table is having one-one record. Now, I want to import ten-ten records in each table. Its like appending record. Till now, I am using Import/Export Wizard to do so.... and somewhere it is failing. One failure i have mentioned it in my previous post message.
October 8, 2008 at 6:20 am
OK, well the clue is here: "The value could not be converted because of a potential loss of data." You're trying to push in a value that's not convertible to your SQL table's data-type.
The only thing I can suggest now is that you start with the first worksheet and see if that works, and then move on to the next and so on until it falls over. Then, when you know which worksheet is causing the problem (it might be the first) go through just importing column by column. Sooner or later you'll find the column which is causing the problem, and then take a look at it.
I would think it's likely that your problem is either string -> int, or string -> bool.
October 9, 2008 at 3:17 pm
"Hello - I've done a lot of Excel importing into SQL and it's a real pain. Sometimes values simply don't get imported, and other times it makes a guess at the column type which is just wrong, but can't be fixed."
I agree on that. The situation described has been on the agenda many times. I woudn't use any
wizard but for a flat tabseparated texfile. I would either split the 10 worksheets to 10 textfiles and
import them (by a macro) or write plain VBA SQL code (insert) which loops the sheets.
You can take a look at :
http://support.microsoft.com/kb/321686 (an old one)
/Gosta
October 21, 2008 at 10:59 pm
prashantotageri (10/8/2008)
I tried importing it as Excel 97/2003 .xls format (instead of 2007) and it is failing.Also I tried to follow as per the below link for SQL 2000 and found the below error after pressing the "Test Connection" button
"Test Connection failed because of an error in initializing provider. Unrecognized database format 'c:\XXX\test.xlsx'
Below problem what I am facing with my normal strategy
1. In the first wizard ("Choose a Data Source") selected data source as "Microsoft Excel"
2. Selected the .xlsx path which has many sheets that needs to be imported into respective tables
3. Excel version selected as 2007
4. In the second wizard ("Choose a Destination") selected destination as "Microsoft OLE DB Provider for SQL Server"
5. Server name selected
6. Authentication - Selected SQL server authentication
7. Selected the database where i want to import data
8. In the third wizard ("Specify table copy or query), I have selected Copy data.....
9. In the fourth wizard ("Select source tables and views") I have selected list of all the tables and mapped them to the corresponding tables
10. In the fifth wizard ("Review Data Type Mapping") I am finding below error message
"Found type mismatch.
May 12, 2009 at 12:01 pm
Thanks for the suggestion on the flat file import.
I've run into the same problems noted by others here on this forum (that's why I got here - from a Google Search), and after a few trials using the flat file method got everything to work fine.
Greatly Appreciated 🙂
Hank
February 25, 2010 at 7:12 pm
I am also getting same problem
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "VALID_RANGE_MAX_OLD" (66) to column "VALID_RANGE_MAX_OLD" (247). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
December 6, 2011 at 10:59 am
Thanks Alistair! Converting to Flat File approach worked for me.
March 30, 2012 at 11:24 pm
Hi Prashanth,
Impost data from excel 2007 to sql server.
1. source - excel
2. Data Conversion must
Because Excel default data type for every column page "UNICODE STRING" OF length 255.
For that we need to provide required data types for columns in the excel sheet.
3. Destination- OLEDB
Thanks & Regards,
Subbu.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply