CODEWITHSUNDEEP
sqlsql-server
Soul Donut
Soul Donut

Reputation: 382

SQL Server trim whitespace from flat file on import

I'm using the SQL Server Import/Export wizard to upload several large (>100 GB) flat files to a SQL Server. These files are delimited, not fixed-width, but still have extraneous trailing whitespace within columns:

100,VALUE         ,VALUE           ,VALUE
1002,VALUE         ,VALUE           ,VALUE

I don't have control over how these files are created. Unfortunately, the whitespace is maintained in SQL following the upload. I noticed that I can append SET ANSI_PADDING OFF; to the SQL query auto-generated by the wizard, but it looks like changing that setting is ill-advised. Is there a better means to go about this process? Is it even worth doing?

Cleaning the files in advance is not possible, given that in total I have about 10TB of data to upload and the code would take days, possibly weeks, to run. I imagine running an ALTER COLUMN ... LTRIM(RTRIM([column_name])) is going to be very time-intensive as well.

Upvotes: 1

Views: 2542

Answers (1)

Joe C
Joe C

Reputation: 3993

The wizard will create a dtsx file if you choose to save it. Using SSDT, you can open that package and add derived or transform component to do the trimming while the data is being imported.

If you do not want to use SSIS in that way, you can do an update (opposed to alter column) to trim the columns after the import.

If you choose the SSIS route, here is a handy script that will trim all fields: http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html

Upvotes: 1

Related Questions