Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Import data from Excel 2007 to SQL Server Management Studio 2008 Expand / Collapse
Author
Message
Posted Monday, October 6, 2008 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:39 PM
Points: 4, Visits: 13
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.
Post #581509
Posted Monday, October 6, 2008 11:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Can u tell us exactly what issues/errors you are facing?

"Keep Trying"
Post #581515
Posted Tuesday, October 7, 2008 9:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:07 AM
Points: 774, Visits: 1,191
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


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #581914
Posted Wednesday, October 8, 2008 3:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:39 PM
Points: 4, Visits: 13
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.
Post #582420
Posted Wednesday, October 8, 2008 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 6:01 AM
Points: 6, Visits: 35
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.

Post #582441
Posted Wednesday, October 8, 2008 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:39 PM
Points: 4, Visits: 13
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)
Post #582455
Posted Wednesday, October 8, 2008 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 6:01 AM
Points: 6, Visits: 35
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.
Post #582469
Posted Wednesday, October 8, 2008 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:39 PM
Points: 4, Visits: 13
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.
Post #582477
Posted Wednesday, October 8, 2008 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 6:01 AM
Points: 6, Visits: 35
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.

Post #582488
Posted Thursday, October 9, 2008 3:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:26 AM
Points: 205, Visits: 1,336
"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



Post #583611
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse