Importing text file fails

I try to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message

Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). 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)

The column 19 which reported a problem contains this information:
?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067

However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file.

The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing.

Please help someone!

BjrnWhat datatype you have on column 19 destination table?|||I use string [DT_STR] with a column width of 500

/Bjrn|||

Quote:

Originally Posted by gulllet

I use string [DT_STR] with a column width of 500

/Bjrn

...And I'm using the locale Swedish. Saving in Notepad or Excel is done in ANSI.

/Bjrn

Importing Data Into Sql Server Database

I am experiencing intermittent connection problems with the Northwind database and sql server express. The most frequent error message indicates that I am already logged in and will not allow another user.

The odd thing is that even when I am locked out I can access a Northwind database from another connection on Server Explorer.

I ran code to demonstrate simple binding for two text boxes, no problem. I added a project to show complex binding using the datagrid view tool, but could not get past selecting the database.

I tried the same simple binding project on another computer running a beta version of VS 2005 Pro and could not connect to Northwind on that computer also. (Two different Northwind databases, different computers)

A couple days ago I restored the database in Solutions Manager and that helped for a while, but I should not have to resort to that, I know.

How do I remove the user lock? I tried Solutions Manager Express with the first computer but could not access the database in the management module.

The exact text of the error would help. It sounds like you might be trying to access the data through both a User Instance and through the parent instance. This doesn't work since only one copy of SQL can access a database at a time.

Mike

|||

Mike-

I learned that whenever I had a problem with connecting to a database, it was because I was using the dataset from the prior project in the solution, in a new project.

Thanks for replying.

Ruel

P.S.

I don't know if I should start a new thread, but how do I pull data from an Excel spreadsheet into a sql server database table?

|||

Hi,

i would suggest to refer

http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=293466&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088614&SiteID=1

HTH

Hemantgiri S. Goswami

|||

Thanks Hemantgiri -

The links were helpful for understanding the process of pulling data files into sql server. However, I am still running into a roadblock. For instance, the following:

===================================================================

bulk insert

AAS Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

===================================================================

Where:

AAS = Database Name

Accounts = Table Name

Gives the following error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Accounts'.

|||

Hi,

run it like,

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

or

bulk insert

AAS.ObjectOwner.Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads'

Hemantgiri S. Goswami

|||

Tried the following as you suggested

bulk insert AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

With the resulting error message:

Msg 4863, Level 16, State 1, Line 1

Bulk load data conversion error (truncation) for row 1, column 1 (AccountNo).

Column 1 of table Accounts is nchar(15) - I raised it from 10 to 15 to see if that would take care of the truncation issue.

The format for column 1 of the xls file is general.

I have had a similar experience with an xml file.

I also tried the bcp utility to format a file for input but I get a message telling me that either the server does not exist or I do not have access.

Thanks for your assistance

Ruel

|||

Could not get to the server with the bcp utility so I tried bulk insert. I found that I had to use "firstrow=2" to get past error messages.

bulk insert

AAS..Accounts

from 'c:\documents and settings\ruel\desktop\invest\aas downloads\dailydownloads\2a20070112.xls'

with

(

firstrow=2

)

However, the above returns the following message in SSMSE:

(0 row(s) affected)

|||

Hi,

Could ensure the length of the all field!!!!

Hemantgiri S. Goswami

importing an excel file and save data into a database in my web account

Hi everyone, sorry if this message is not supposed to be posted here.

I'm learning asp.net , and would like to know how I can insert data from a excel file into a database on my web account. Pretty much insert/update information in the database using excel file or a access file.

Thanks a lot in advance

Checkout thses links:

http://support.microsoft.com/kb/311731

http://www.msdner.com/forum/thread302753.html

http://support.microsoft.com/kb/316934

http://www.vbdotnetheaven.com/UploadFile/mahesh/AccessExcelDatabase04252005014321AM/AccessExcelDatabase.aspx

I hope they will help.

Good luck.

|||

Thanks a lot. I found an interesting article that talks about this. again thanks for your assistance

|||

If my post was helpful for your then please mark it as answer.

If not, please share the answer with us in a post and mark it as answer.

Remeber we have to cooperate to help each other.

Thanks and you are welcome.