Importing text file removes decimal separator

Hi,

I'm trying to import a semi-comma separated text file into a SQL db. I have a field in the text file that contains decimal number. As a decimal separator it's used a comma (15,35). When i use a DTS package to create a destination table and import all rows, the field is created as a float field. In this field the decimal comma is removed so the number in SQL becomes 1535. If I change the decimal separator to (.) i works OK. But I need to get it work with comma as decimal separator. In the DTS package the field form the text file is recognised as varchar (8000). Any ideas?

Ingar

You have to choose appropriate locale which threats comma as a decimal delimiter. There is the drop-down control for choosing locales on the first page of the Flat File Connection Manager UI.

Thanks.

|||

Hi, thanks for your answer. I can see that I didn't specify which SQL version i was using. I use SQL 2000 not 2005. I know that the theme in this forum is SSIS but it also states "transforms/data flow" and I thought DTS packages from SQL 2000 was included here. I appologize if I have misunderstood this but english is not my first language.

So as far as I know the Flat file connection manager is not available in SQL 2000.

But thanks anyway.

Ingar

Importing text file into database

Hello Everyone,

I would like to import a text file which contains one string (a large integer) per line not separated by commas or anything else except a carriage return. Does anyone know of an easy way to store this in a database file? I'm open to suggestions if there is more than one way to save this kind of information within a database. I have SQL server 2005 developer edition if that helps in any way. I'm also starting to learn about Linq so if there is some other way you would store this information for that purpose I would love to hear about that as well. C# code is preferable, but I can use the automatic translators if that's all you have. By the way, I'm a newbie to this subject (if you couldn't tell). Thanks in advance.

Robert

check out

- BCP

- Bulk Insert

|||

Your answer looks promising after a review of the information about it. I'll try it out and if it works then close this post. Thanks for your suggestion.

Robert

|||

Hello Everyone,

I did try this and I was amazed to find out that you can import plain text files straight into excel or access. Obviously with an access database in hand using it with SQL server 2005 should be straightforward. Thanks for your help.

Robert

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 excel column with multiple values separated by '/'

I would like to import two columns from an excel file into a sql server
table as an area code - time zone look up.
The area code column sometimes has multiple area codes in the area code
cell. eg. 207/208/209.
What is a good way to import those two columns so that 3 table rows are
created for each of those Excel rows that contain these multiple values
separated by the '/' character?
Thank you,
GregOn Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:

>I would like to import two columns from an excel file into a sql server
>table as an area code - time zone look up.
>The area code column sometimes has multiple area codes in the area code
>cell. eg. 207/208/209.
>What is a good way to import those two columns so that 3 table rows are
>created for each of those Excel rows that contain these multiple values
>separated by the '/' character?
Hi Greg,
Some useful techniques are disccussed at
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP|||Thank you Hugo, I'll take a look !
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:spdc0210aq8daomldhm1lmbpviqf7hp4gi@.
4ax.com...
> On Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>
> Hi Greg,
> Some useful techniques are disccussed at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Hugo Kornelis, SQL Server MVP

Importing data from web page into SQL Server table

Hi,
I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;
I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.
I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...
Thanks for your help
JohannYes you can get the content of the page dynamically by using the server.execute then put the content in a text file and read the stream|||Thanks for your help
The problem is that the page containing the data is not mine. I have no control over it, and it's on another server.
All I have is the url to display that page in a browser. So, can I direct a script to that url and make my script read the content ?
Thanks
Johann|||does not matter if you haven't got this page in your server with the server.execute you can capture the html code then play with then pragmatically follow this link with some exampleshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebhttpserverutilityclassexecutetopic.asp