Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kotonikak
Yak Posting Veteran
92 Posts |
 Posted - 2013-08-09 : 11:42:06
|
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! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-08-09 : 12:07:11
|
| 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. |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-09 : 12:08:54
|
| 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. |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-08-09 : 14:02:34
|
| 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... |
|
|
|
|
|
|
Bulk insert csv file with column having comma