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 tab-delimited file into SQL Server 2005

I use SQL server 2005...
I have a tab delimited file which I want to import into my SQL server database.

My sql server table setup is:

CountryID int (autogenerated, identity specification)
CountryName nvarchar(40)
CountryAbbreviation nvarchar(3)

In my tab delimted file I have two columns:
CountryName and CountryAbbreviation

How can I best solve this?

If you use one version of SQL SERVER 2005 other than Express, you can use data import/Export Wizard to get the job done.

1.Right click on your database, select Task menu, click Import Data, the Wizard will start;

2.Click NEXT, you need choose a Data source. In your case, you choose the Flat File Source from the Data Source drowdown menu, then Browse to your text file. If you text file includes column names, you need check the checkbox infront of "Column names in the first data row"; click next;

3.You will see part of your data file in two clolumns, click Next;

4. Destination data base, use SQL Native Client (Default) as destination data source, pick your database from the Databasr dropdownlist, click next;

5.In this window, you will see both source and destination. You need pick your table from the destination column, click Edit button for mapping, make sure you match your source column with your destination column. (you cannot touch your identity field here) click next;

5. click next again, then click Finish. Data should be imported to your destination table along with their new ids.

I hope I make this process clear enough to follow.

Let me know if you are running into issues.

|||Great! explanation was clear enough :)
It all works now!

Importing a tab delimited file into SQL Server

I am trying to impport a tab demlied txt file, into SQL SERVER, using OPENROWSET. Howver, the file gets imports, into SQL, BUT ONLY in 1 column... The source file really has 3 columns, but when it gets isnerted into SQL, it only oputs it all in 1 column.

select * into #tmp from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

How do I insert the data into 3 seperate colms, instead of 1?

Try

BULK INSERT
http://msdn2.microsoft.com/en-us/library/ms188365.aspx

or
BCP
http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx|||I would like to use the OPENROWSET Only. Is that possible with Openrowset?|||

Code Snippet

CREATE TABLE #newIn
(
Ident varchar(max),
subIdent int identity(1,1)
subItem varchar(max)
)

DECLARE @.posA int,
@.posB int,
@.srch char(1),
@.in varchar(max)

SET @.srch = ',' --or char(9) for tab

DECLARE csr CURSOR FOR
SELECT item from #tmp

OPEN csr

FETCH NEXT FROM csr
INTO @.in

WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.in = LTRIM(RTRIM(@.in))+@.srch
IF len(@.in)>1
BEGIN
SET @.posB = CHARINDEX(@.srch, @.in)
SET @.posA = 0
WHILE @.posB > 0
BEGIN
INSERT INTO #newIn (Ident, subItem) VALUES (@.in, substring(@.in, @.posA, @.posB-@.posA))
SET @.posA = @.posB + 1
SET @.posB = CHARINDEX(@.srch, @.in, @.posA)
END
END
END

CLOSE csr
DEALLOCATE csr

select *
from #newIn

DROP TABLE #newIn

This converts the single column into multiple rows that you can then extract back as you want.

If not exactly what you need, then it's a starting point for other variations.|||

I created a schema.ini file, with all the format specifiactions for my source file. Here is the code for that. However, when I import, it most of the data is imported fine, except for when the value has "" in the data. eg if the data has value such as 'pending "store" ', it imports the data as only "pending", and omits the store..

Here is my specificaiton of the schema file

[vwConstrLendingmapLoanDefiCdDesc.txt]
ColNameHeader=False
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=Doccd Char Width 50
Col2=Docdesc LongChar
Col3=Docshcd Char Width 100

- And this is the openrowset:

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\c:\SQLRSReports;','select * from vwConstrLendingmapLoanDefiCdDesc.txt')

Pl advise