Results 1 to 5 of 5

Thread: Trim Spaces

  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trim Spaces

    We receive periodic tables in the TXT (CSV) format. We are to import them to an SQL Server table, but unfortunately the source TXT tables are not consistent over the periods (i.e. sometimes there's a missing field, sometimes we get spaces before and after the content of a certain field, etc). One of the problems we have is that in a date field, ocassionally, there are blank spaces.
    We need to import this field to a CHAR field which is 10 chars long. So whenever we get spaces before the date, we end up with an incomplete string. For example: "15/12/2005" might get imported as " 15/12/20".

    Is there a neat way to deal with this? I have read something about linked servers in SQL Server. I had thought about creating a linked server that points to the source TXT file, then run some TRIM query, but I'm not sure if this can be done. Anyway, I couldn't pull it off at once. I think I need to create a schema.ini file in the same directory the source TXT is stored, but I don't know how that's done.

    Any thought about this or other solution?
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim Spaces

    How are you currently importing this?

    Are you using a DTS package? If so, you can create a custom function using VBScript with the Trim function.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim Spaces

    Hi Mark,

    No, I'm not using a DTS Package (since the source files are so variable it's difficult to standardize the procedure).
    However, when importing via the Corporate Manager, there is an option to transform the data using VB Script Language.
    The standard import script (before manually modifying it) looks like this:

    '************************************************* *********************
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************************************************* ***********************

    Function Main()
    DTSDestination("destination_field_name") = DTSSource("source_field_name")
    Main = DTSTransformStat_OK
    End Function

    Is there a function to trim the source field? Something like

    DTSDestination("destination_field_name") = TRIM(DTSSource("source_field_name"))

    I don't know anything about VBScript but after your post I suspect this can be done.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim Spaces

    Yep - that's the ticket!

  5. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim Spaces

    Perfect! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

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