Trim Spaces

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Yep – that’s the ticket!

Reply | Quote

Perfect!

Reply | Quote