truncate text in SQL column

One of the easiest ways when doing cut-and-paste (which bypasses every other option for modifying data) is to change the field in the table to a size that is the same as your maximum size desired.  For example, if you want only 255 characters, then change the size of the text field to 255 characters long.  You may get a "data truncated" message, but hopefully it won't prevent you from pasting everything else in.

If it gives you a problem, another option is to paste the data into an Access table with the proper field sizes and then take those records and paste into SQL Server, if that's what you want to do.  Access is a lot more "forgiving" than SQL Server.

You haven't indicated that you've tried anything that didn't work, or mentioned any other limitations, so let's try everything....

If you are using a query, why not just take the leftside of so many characters of the string:
[FieldAlias] = LEFT([FiedName],250)

p.s.  What do you mean by "manually"?  Cut-and-paste?  Create a custom SQL query and append it into the SQL Server table?  Wave a magic wand?....

Hi again Mark,
I haven't had time to devote to this project till now.
Let me begin by sharing what I have started doing.
* I have 87 csv files to copy to a SQL database.  (more than I originally thought)
* I used the Management Studio and chose source 'Flat File' and destination OLE DB for SQL Server
   all went well till the end when it tripped up on the long text column. Error mentioned 'Truncate'
As there are 425 or so columns I really should change the default 50 varchr or does it only use what it needs. ?
I recall a long time ago doing this and even the blank space in a column was treated as data  eg if set at 50 and only 5 chrs then it would regard it as 50 chrs as far as space goes. Is that the case now ? If I can make it 255 chrs and still doesn't push the SQL limits then I'll do it.
I wasn't aware you could copy and paste the data how does that work ? or is that another question ?

Manually ? meaning using the GUI as explained in last comment

OK, I see what you mean.  You must not have seen my last comment before you posted your last response.
Since you selected "flat file", that means you are importing a .csv or .txt file, as you would have selected the appropriate data source for any other type of file.

One of the advantages of importing from a database like Access is you can setup a query to modify your data as you import.  You don't have that option with a flat file.

I just tested using the SSMS Tasks-Import Data to import a .csv file into an existing table with a shorter text field than what the data in the source has.  All I got was a truncate warning, but the data went in at the truncated length.

Super easy.  Just set your table field length to the max wanted and go for it.

by the way, are each of these files going into the same table, or are we "creating" a new table with each import?

I would like to put them into one table and then create views and access them from Excel

also, do have any "identity column" issues?

Sounds like to you can make a table, set the column widths to whatever you want, and then import your flat file into it.
On your import build screens, you have a page named "Selected Source Tables and Views", which should have a "Edit Mappings" button on it.  You can use this to set the field length of your new table columns before you make it.  See if that helps you get things set the way you want.  If not, just change things after you import and create the table.

You can also use the import to append to an existing table, so go for it....

Yes every 2-3 months I plan to append to the big file.
Let me try this now...........re my Q last comment ... is space recorded for width nominated ? or just space used ?

also, you DO want to keep the column length in your table to the just the max needed to hold the data.  A field where the data is only 5 characters long should have its field length set to 5 or just slightly more... not the default 50.

There are 420 columns that would take a long time :)

can I use a query to set all column width to say 7 and then alter the text files ?

There is no append function so does that mean when you copy data to the 'big' file it automatically appends rather than overwrite ?

When you select your destination, you can edit mapping and pick a table to append to or create a new table.  That is where you make the distinction.

Also, use a query to do what, where.  the source file, the sql server table?  If you have 450 columns to deal with, you'll have to deal with it, otherwise import a fewer number of columns that you can handle.  You can use T-SQL to make changes to your table, but you are still going to have to individually address the changes to each field, or you can setup a loop in code somewhere to step thru each field individually and set it's length.   If you have 450 fields to do, you better get crackin'.....

I'll be glad to lend assistance as long as it doesn't take more than 5 minutes.

I just tried and same error even after changing width to 255 chr
Below is error report
- Executing (Error)
      Messages
      * Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Systems" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
       (SQL Server Import and Export Wizard)
      
      * Error 0xc020902a: Data Flow Task 1: The "Source - 2012_01_January_csv.Outputs[Flat File Source Output].Columns[Systems]" failed because truncation occurred, and the truncation row disposition on "Source - 2012_01_January_csv.Outputs[Flat File Source Output].Columns[Systems]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
       (SQL Server Import and Export Wizard)
      
      * Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\HEINZ\SQL-PROJECT\15000_v66a_2012 (3)\2012_01_January.csv" on data row 2.
       (SQL Server Import and Export Wizard)
      
      * Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - 2012_01_January_csv returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
       (SQL Server Import and Export Wizard)

by the way, are these .csv files or .txt files?  What type of "flat file"?

The type of errors you are getting will require specific, detailed knowledge of the source file data and the target table.  I wouldn't even try to figure those errors out with that information.

csv

How about I eliminate that sql column how can I do that ? That text info is not that important

I'm surprised there's no query that can set all varchar to a number say 7

Your error messages indicate that the import wizard is set to fail on data truncation.  You need to change that so it can truncate without failing.

deleting the column could be a way ?

I don't think that the method you are using allows you to exclude specific columns.  If you were importing from Access, you could.

You've got some specific requirements that are too troublesome for the import method you've selected.  Either change to something that will allow you to do what you want a lot easier, or plan to spend the time wrestling around with trying to do it this way.

so what about the copy and paste you mentioned ?

Alternatively I could delete the problem column in all of the 87 files and then import them into SQL ?

I was thinking of cut-paste from Excel, but that's got a 255 character limit.
Just what exactly is the problem.  It sounded like you wanted to truncate your imported data.  Do you really want to do that?  Would you rather import ALL the data, no matter how long?  Was the truncate just a workaround for the problems you were having?

Access has a maximum of 255 columns for a table/query, so that's out....  By the way, why 420 columns, sounds like an old flat-table.  It really limits your options.

now one of the columns has "£2000" which contents straddles two columns  I tried numeric, decimal and int and they don't work which is correct type ? thanks and is last question. You've been amazingly patient.

Oh ignore that last comment as all I need to do is change the format in the csv file...silly me :)

I deleted two text columns and formatted a £ column in the csv file and imported the data and all worked fine.
Thank You for your remarkable patience Mark.
Ian

Cheers Mark now the work begins.

Glad you got what you needed.  Sometimes it can seem like we go round-and-round trying to get into each other's heads.
But, it all comes out in the wash...  Go Yankees....