SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Importing csv file into a database -inserts quotes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnsley
Starting Member

31 Posts

Posted - 01/08/2013 :  11:12:38  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  11:34:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/09/2013 :  00:11:22  Show Profile  Reply with Quote
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 it

Otherwise you've to add derived column to use REPLACE() function expression to strip off " from all the column data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

barnsley
Starting Member

31 Posts

Posted - 01/09/2013 :  11:55:53  Show Profile  Reply with Quote
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 AB

I 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.
Go to Top of Page

barnsley
Starting Member

31 Posts

Posted - 01/09/2013 :  12:05:35  Show Profile  Reply with Quote
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.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 01/09/2013 :  19:47:06  Show Profile  Reply with Quote
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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/09/2013 :  22:51:26  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/10/2013 :  04:17:56  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000