Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bulk insert csv file with column having comma
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

92 Posts

Posted - 08/09/2013 :  11:42:06  Show Profile  Reply with Quote
CSV file created by another program is giving me the following data:

    SIMULATION	   Period	    Product	      OUTPUT_NEP
        0	   2013Q2	USSIC_Aviation	      10,191,468
        0	   2013Q2	Avemco_Aviation        7,724,131


I'm trying to bulk insert this into SQL but the "OUTPUT_NEP" column is obviously going to give me errors because of the commas in the numbers. When I open the file as text, the OUTPUT_NEP column is in double quotes as well so it shows up as "10,191,468". Right now, that column in SQL is "float" format and I would like to get rid of the commas and quotes altogether so I don't get an error with the datatype. What's the best way to do this? Create a format file? If so, what's a simple example on doing this? I don't usually use format files.

Any help will be appreciated!

Edited by - kotonikak on 08/09/2013 11:54:07

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/09/2013 :  12:07:11  Show Profile  Reply with Quote
I don't think you can do a conversion on the fly with BCP. I *think* you'll have to import it as a string and then manipulate the data after you have loaded it.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 08/09/2013 :  12:08:54  Show Profile  Reply with Quote
Unfortunately there are no easy answers to that question. You might ask the vendor or provider of the data to give you the file with a different delimiter such as pipe. Alternatively, you can consider using SSIS, which if I am not mistaken, can handle the double-quote escape.
Go to Top of Page

kotonikak
Yak Posting Veteran

92 Posts

Posted - 08/09/2013 :  14:02:34  Show Profile  Reply with Quote
Thanks guys. I might just have to create a macro for all the files in that folder so that column gets switched to a number instead...
Go to Top of Page
  Previous Topic Topic Next 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.3 seconds. Powered By: Snitz Forums 2000