Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import and Modify Data in SQL Server

Posted on 2011-10-03
6
Medium Priority
?
240 Views
Last Modified: 2012-05-12
I have a CSV file that I am trying to import into SQL Server 2008.  This file contains values that are all surrounded with quotation marks.  I want to remove the quotation marks from the data as I insert the data into the database; the reason being some of the fields are integers, and therefore cannot accept quotation marks.

Is there some way I can use a SQL function such as REPLACE() to remove the quotation marks from the data with SQL before it gets inserted?

Thanks,

Nick

0
Comment
Question by:nickgross19
  • 2
4 Comments
 

Author Comment

by:nickgross19
ID: 36904349
If you could show me the full code to create a table and insert the data from a CSV File that would also be helpful.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36906543
How have you tried to do the import? Are you using the Import and Export Wizard in SQL Management Studio. Additionally, are you going to a staging table first? If so, you can convert numbers after the fact as well as other tasks to ensure data is clean before moving to production table. Otherwise, you would set the data type during import. If this is a repeated import, there are other options, but figure I would start with the simplest.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36909016
if you are doing using SSIS then it will be possible using the Derived column. In that replace the quotation with blank.
0
 

Author Closing Comment

by:nickgross19
ID: 37058173
I think this solution is one way to do it; you can import the data into the table and modify it after you insert it.  This solution did not address how to remove values during the insertion process though.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question