SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import data from Excel 2007 to SQL Server Management Studio 2008


Import data from Excel 2007 to SQL Server Management Studio 2008

Author
Message
prashantotageri
prashantotageri
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3487 Visits: 1865
Can u tell us exactly what issues/errors you are facing?

"Keep Trying"
Jerry Hung
Jerry Hung
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1208
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
prashantotageri
prashantotageri
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
Alistair Potts
Alistair Potts
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 38
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.
prashantotageri
prashantotageri
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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)
Alistair Potts
Alistair Potts
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 38
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.
prashantotageri
prashantotageri
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
Alistair Potts
Alistair Potts
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 38
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.
Gosta Munktell
Gosta Munktell
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 2161
"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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search