Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Nederland
    Posts
    2

    Unanswered: problem with DTS and text file

    Hello,
    i have the following problem:
    i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.

    Someone any idea what's wrong?

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    does your comma separate decimals from the significant digits?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: problem with DTS and text file

    Originally posted by troj
    Hello,
    i have the following problem:
    i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.

    Someone any idea what's wrong?
    Nothing's wrong...although I'm suprised it didn't blow up...comma's in numbers are just a presentation issue...unlike precision...

    But for example...you can't do..

    CREATE TABLE myTablefloat (col1 float)
    INSERT INTO myTablefloat(col1) SELECT 1,203.45


    But for some reason in a text file it allows it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Location
    Nederland
    Posts
    2
    Originally posted by ms_sql_dba
    does your comma separate decimals from the significant digits?
    it are money values, for example 10,22
    in sql table it becomes 1022

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by troj
    it are money values, for example 10,22
    in sql table it becomes 1022
    What's the collation, language of your sql server box?

    Sounds like it doesn't handle comma as precision.

    Load the data in to a work table that the column defined as varchar..

    Then populate your final table by doing an insert and convert your data..something like

    SELECT CONVERT(float,REPLACE(col1,',','.'))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    If changing datatype is a problem you can even use the ActiveX replace function in the DTS transformation to replace "," by "."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •