Error Importing from .xlsx file to MSQL 2008

[TheHobbit1981]

Hi All

This is my first post as a new member of this site.

I am trying to import a large .xlsx file into MSQL 2008.

It ran fine up until almost the end of the process then fails. I am completely new at this and would really appreciate some help.

I have included links to the file I am trying to import "AffiliateFutureSoccer - Condenced - Full.xlsx" BUT as this is a fairly large file with a lot of records I have also included a "...Test.xls" version with only 100 lines. Additionally I have also included ".csv" formats of the same files in case they are needed.

Finally, I have included the error report that was generated whilst trying to perform the import. "Report1.txt"

I am thinking that there is data that SQL does not like. Can SQL handle the tilde (~) symbol?

Thanks in advance,
Paul

http://dl.dropbox.com/u/1412205/AffiliateFutureSoccer - Condenced - Full.csv

http://dl.dropbox.com/u/1412205/AffiliateFutureSoccer - Condenced - Full.xlsx

http://dl.dropbox.com/u/1412205/AffiliateFutureSoccer - Condenced - Test.csv

http://dl.dropbox.com/u/1412205/AffiliateFutureSoccer - Condenced - Test.xlsx

http://dl.dropbox.com/u/1412205/Report1.txt

 

[bboffin]

You don't say how you are doing this but I would use the Data Import Wizard that you can access via Sql Server Management Studion 2008.

Open a connection to your server and select the target database.

From the right click menu select Tasks|Import Data...

For the Data Source select the Microsoft Excel provider and browse to the .xslx file you wish to import. This will automatically select the Office 2007 variant. Make sure that you check the 'First row has columns names' check box and click Next.

The destination will be your SQL 2008 database.

The rest of the defaults should be OK. I have just done this with your short test data and it completed without any errors.

Hope this helps


Regards

Bob Boffin


Bob Boffin

 

[fredericofonseca]

he has now solved his problem. sadly he has not updated this forum with the info.

http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/d6cfaf58-c77f-4725-b4d5-7db72c01289e

In case the link gets lost....

#

Hi Thehobbit,



I downloaded the .xlsx file from your link trying to reproduce your problem, and I imported it to my SQL Server 2008 R2, it was successful imported. So I think the file is okay for import.



According to the error message from "Report1.txt", "An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.", the possible cause for this issue should be the excel file is locked by other processes.



As a possible solution, would you please kindly resave this file and name it to other file name to see if the issue will be fixed?



Also it will be great if we can keep the ACE(Access Database Engine) up to date, would you please download the latest ACE and install it? Please check the following information for details:

Microsoft Access Database Engine 2010 Redistributable:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

If there are more questions, please feel free to ask.



Thanks,

Grace
#
Wednesday, October 20, 2010 9:05 AM
TheHobbit81


Thanks for the reply Grace.



I downloaded the ACE and ran it.

Opened the Excel file and saved it to my local HDD, renaming it in the process.

I made sure that Excel was closed properly and tried the import again.

Success!!!

It has worked, import complete.

Only problem is that now I've compleatly forgotton SQL, it's been about 8 years since I last used it. I can't even remember how to do a simple select statement to display my data.

Time to hit Google.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886