Import data from Excel to a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import data from Excel to a table

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 14:06:31

Hi evryone,

I'm trying to import about 7000 rows from an Excel sheet. This excel sheet can also be viewed as CSV file. This is means I tried the following tests on Both files.

using a script:

SELECT * INTO cDestination FROM OPENROWSET('Microsoft.Jet.OLEDB.12.0','Excel 7.0;Database=C:\Users\Downloads\csvtest.xls', 'SELECT * FROM [csvtest$]')

gives an Error:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

SSIS:

I tried following the steps of the link below to create an SSIS package to do the job but without luck.

http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

here is my sample of data when i try to open that Excel CSV file as text file, notice that the double quotes are automatically added when you open a file as text:


XXXXXXX,XXXXXXX,"IMD 120 TG440B-12x3",",G","ABT","  4 CHANNEL, NO CHANNEL "
XXXXX,XXXXX,"IMD 000 VS440B,12x7",",H"," MNKL ","  3 CHANNEL, 5 CHANNEL"

Any help will be appreciated.
Thanks,

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 14:20:25

whats the version of excel? shouldnt it be excel 8.0 in connectin string?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 14:23:17

I looked up the version it is 2007 (12.0.XXX).
I also have tried 8.0 and 12.0 in case It was a the reason it fails!

Thanks you.

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 14:25:25

if its 2007 shouldnt file be of extension .xlsx?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-30 : 14:33:55

What is the error message you got when you try to import the CSV file using SSIS? I was able to import the two rows of data that you posted into a database using Import/Export Wizard - the only thing I had to make sure I did was to indicate the Text Qualifier as double quotes on the first screen of the Import/Export Wizard. After I imported, this is what I see in my database table.

Column 0	Column 1	Column 2	Column 3	Column 4	Column 5
XXXXXXX	XXXXXXX	IMD 120 TG440B-12x3	,G	ABT	  4 CHANNEL, NO CHANNEL 
XXXXX	XXXXX	IMD 000 VS440B,12x7	,H	 MNKL 	  3 CHANNEL, 5 CHANNEL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 14:40:56

openrowset issue might be related to 32 bit incompatibility. i think you might need to dolwnload 64 bit equivalents components

see

http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 14:53:40

I have tried Exp/Imp wizard but I think my data is very weird: this is the error;

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Config" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)

@visakh16:
I made both changes .xlsx and 8.0 still getting the same error.
Where to please find the 64bit components for this specific issue?

@sunitabeck:

the error I get using SSIS (Excel to SQL table) :

Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

if you notice when I open that CSV file as a text file, I get some of my column with commas as a delimiter and if the data has spaces, it will be quoted into double quotes.

by the way can you try the same sample of data as an excel file please instead of CSV?

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 15:43:42

quote:
Originally posted by xhostx

I have tried Exp/Imp wizard but I think my data is very weird: this is the error;
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Config" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

@visakh16:
I made both changes .xlsx and 8.0 still getting the same error.
Where to please find the 64bit components for this specific issue?

@sunitabeck:

the error I get using SSIS (Excel to SQL table) :
Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

if you notice when I open that CSV file as a text file, I get some of my column with commas as a delimiter and if the data has spaces, it will be quoted into double quotes.

by the way can you try the same sample of data as an excel file please instead of CSV?

Thanks,

see steps outlined in article in comments section.
do you know whether your server is 32 or 64 bit?>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 15:46:48

@visakh16:

My machine is a 64bit, this is where I'm doing all tests.
thanks

--------------------------
Get rich or die trying
--------------------------

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-30 : 16:33:56

quote:
Originally posted by sunitabeck

What is the error message you got when you try to import the CSV file using SSIS? I was able to import the two rows of data that you posted into a database using Import/Export Wizard - the only thing I had to make sure I did was to indicate the Text Qualifier as double quotes on the first screen of the Import/Export Wizard. After I imported, this is what I see in my database table.
Column 0 Column 1 Column 2 Column 3 Column 4 Column 5
XXXXXXX XXXXXXX IMD 120 TG440B-12x3 ,G ABT 4 CHANNEL, NO CHANNEL
XXXXX XXXXX IMD 000 VS440B,12x7 ,H MNKL 3 CHANNEL, 5 CHANNEL

this is the latest error I'm getting when trying SSIS!


Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 17:09:09

quote:
Originally posted by xhostx

@visakh16:

My machine is a 64bit, this is where I'm doing all tests.
thanks

--------------------------
Get rich or die trying
--------------------------

This is what article suggested

After many struggles with this issue, I found the following solution:

1. On 64-bit servers and boxes, you need to first UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components. Yes, its a headache but the only way I found to install the new replacements for the JET engine components that need to run on 64-bit machines.

2. Download and install the new component from Microsoft:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
* This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.

3. Open up SQL Server and run the following:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0', N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0', N’DynamicParameters’, 1
GO

* This sets the parameters needed to access and run queries related to the components. Address ‘null

4. Now, if you are running OPENROWSET calls you need to abandon calls ,made using the old JET parameters and use the new calls as follows:

(*Example, importing an EXCEL file directly into SQL):

DONT DO THIS….

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0',’Excel 8.0;HDR=YES;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,'select * from [sheet1$]‘)

USE THIS INSTEAD…

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0', ‘Excel 12.0;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,'select * from [sheet1$]‘)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-31 : 10:27:41

@visakh16:

Thank you very much for your help. but somehow this issues is no longer the main problem. it is more about the data conversion.

I have created a table with the same number of columns matching the CSV columns, as well as giving every column a max of nvarchar 4000 character to make sure the data isn't going to cause in error if is is very long.

now, by trying to just import data using the Import Wizard it is generating this error:

 The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

thanks

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 10:39:30

is your source data also unicode?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-31 : 11:41:33

I guess NO, because during the other method (SSIS) I tried to import the same data, then I used to get this error:

...cannot convert between unicode and non-unicode

I guess this message is telling me that the data isn't uni-coded.
again, here the SQL server error:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 11:52:00

then why not declare fields as normal varchar rather than unicode?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-31 : 12:00:23

I hop we talking about the something;

I'm guessing that that I should create a table in SQL server with varchar(XX). is that what you referred to ?

This is my actual table:

create TABLE cDestination
(
[Col1] VARCHAR(4000),
[col2] VARCHAR(4000)
)
thx

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 13:10:00

yep...if your data is non unicode then you should be using varchar against nvarchar

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-31 : 15:16:34

Resolved,
I have to run a Find/Replace on the csv file, because the wizrd reserve " for text delimiters.
It was more because some of the text has already " character, which caused a lot of trouble for the import wizard to succeed.

Thank you all. :)

--------------------------
Get rich or die trying
--------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources