| Author |
Topic |
|
barnsley
Starting Member
34 Posts |
 Posted - 2013-01-08 : 11:12:38
|
| We are using a SSIS package to insert a series of CSV files (saved in excel) into a database table.The problem is, the SSIS package inserts " quotes before every entry into every field imported.There are no quotes in the CSV files at all, yet they appear in the database.The SSIS package uses a Multiple Flat File connection, Row delimiter is {CR}{LF}.Is there a quick and easy way in SSIS package or in the database to stop the quotes from being inserted?thanks.mark. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 11:34:24
|
| You mean there is a " at the start of the value but not at the end?Are you sure it is not in the file - you can't tell from excel - you would need to open the file in something that does not try to interpret the data - e.g. notepad.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 00:11:22
|
| is it a straight pull or are you using some logic in between which might be reason for these quotes to appear?in any case what you could do is to add text qualifier as " in csv connection manager and see if it fixes itOtherwise you've to add derived column to use REPLACE() function expression to strip off " from all the column data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
barnsley
Starting Member
34 Posts |
Posted - 2013-01-09 : 11:55:53
|
quote:
Originally posted by nigelrivett
You mean there is a " at the start of the value but not at the end?Are you sure it is not in the file - you can't tell from excel - you would need to open the file in something that does not try to interpret the data - e.g. notepad.
Yes, I get " at the begining and not at the end.I opened in Notepad and now and I can see " quotes at the begining and end of each piece of data in every field.The reason for not getting the " quotes at the end iis becasue the database field is set to limit character length to 8 characters.Here is what a typical field looks like in notepad:"AB123AB""AB12 AB"so with 8 characters it looks like:"AB123AB"AB12 ABI presume the best way around this (as its a straight pull) is to have another job / step to remove the first character or remove all " quotes?like this:quote:
Originally posted by visakh16...Otherwise you've to add derived column to use REPLACE() function expression to strip off " from all the column data
mark. |
|
|
|
barnsley
Starting Member
34 Posts |
Posted - 2013-01-09 : 12:05:35
|
| I am new to ssis.I have noticed there is an option under toolbox, called Derived Column.And also a REPLACE function,but I'm not sure how to implement this?any ideas please?thanks.mark. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 19:47:06
|
I don't know about SSIS but MS products normally have a checkbox somewhere for using "Text Qualifiers" and generally allow you to identify what the character will be.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:51:26
|
quote:
Originally posted by barnsley
I am new to ssis.I have noticed there is an option under toolbox, called Derived Column.And also a REPLACE function,but I'm not sure how to implement this?any ideas please?thanks.mark.
first open connection manager for file and see if you've given text qualifier as "------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-10 : 04:17:56
|
| You should be able to view a sample of the data in ssis when you set up the datasource to see what you will get.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Importing csv file into a database -inserts quotes