One more problem with importing csv files

Hi,

I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:

...,Bearing Double "D" Flange,...

And the result is: ... | Bearing Double | null (all following columns are null)

The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

string str =Convert.ToString(dr[8]);

}

// Bulk Copy to SQL Server

//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))

//{

// bulkCopy.DestinationTableName = strSqlTable;

// bulkCopy.WriteToServer(dr);

//}

}

}

Any idea is highly appreciated.

shz

Hello shz,

I would recomend using a TextReader for processing a .csv file.

Hope this helps

regards,

G

|||

Thanks Gonzo. However, TextReader should be the last thing I'd like to use.

shz

sql

One data files to multiple data files.

Hello.
I have a database with single data file and i want to split that data across
multiple data files.
Coulf u pls help me how to achieve this
Thanks,
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1please see BOL for Files and Filegroups

One data files to multiple data files.

Hello.
I have a database with single data file and i want to split that data across
multiple data files.
Coulf u pls help me how to achieve this
Thanks,
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
please see BOL for Files and Filegroups
sql

One data files to multiple data files.

Hello.
I have a database with single data file and i want to split that data across
multiple data files.
Coulf u pls help me how to achieve this
Thanks,
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1please see BOL for Files and Filegroups

OleDB Connection to Excel

Hi,

My application connects to excel files.
I am using this code:

dc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=YES;\"");

It works fine on Excel 2000 on all computers. Also it works on some computers with Excel 2002/XP, but on some computers with Excel 2002/XP it doesn't work.

How can I solve it?

Thank's
AlexeiHi,
I used above code sample for my program. but i did not worked.can u help me. I want to do ,take excel information to one record set or data set.I can't understand how to do it.I send u my connection coding.please check it send me wht is the correct syntax or correct code and tell me wht is the componts want. this program using the .net
following has my codes:

MyConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=D:\Oven Sheet (Track)\Data For Symix_23082005-Tracks.xls;Extended Properties=\Excel 8.0")

|||Are you sure you have the right version of Jet on the machine? Check this by right clicking on the desktop and create a new text file, call it demo.udl. This is a datalink file. If you double click on the file it should show you a list of OLE DB Providers, check that Jet 4.0 is installed.

If it is installed then try changing \Excel 8.0 to plain Excel 8.0

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

Old problem, but... Cannot open user default database. Login failed. Login failed for user '

I didn't use SQL Express before, now I am trying to use it. I thinkk it is convinient if you just want to copy db files to user's machine. like this

<add name="schsecurity" connectionString="Data Source=.\SQLExpress;Integrated Security=true;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

as long as user has sql express installed, he should be able to use the db once he copy the file aspnetdb.mdf over.

but when I tried it, I got

Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

it looks simple, SQL express can not verify user 'NT AUTHORITY\NETWORK SERVICE'

so I connect SQL express with SQL server management studio, and add the user into login of the SQL express and set the user as sysadmin. But I still get the error.

Any idea?

thanks!

I doubt it has anything to do with copying files to your machine. The default database does not have proper permissions for access. I receive the same message when attempting to log in using username and password. I know the username and password are correct. I'm not sure of how to give the default db permission for access.|||

You should not post like that, it is not helpful.

Can anyone help?

thanks

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..