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 2012 Forums
 Transact-SQL (2012)
 Bulk insert csv file with column having comma
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

81 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

3587 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

81 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  
 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.11 seconds. Powered By: Snitz Forums 2000