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 |
|
skillile
Posting Yak Master
208 Posts |
 Posted - 2002-10-15 : 18:30:44
|
| I am doing a bulk insert from a *.csv. My file is getting messed up with money fields ie.june,july,august,"$10,00",sept...the money field is throwing off my insert. Is there a way to get around this?Thanksslow down to move faster... |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-15 : 20:07:41
|
| skillile,Are you saying you have a field titled "$10,00"? Or is that just sample data?This is a bit of an abstract answer because I can no longer find the link to MS's article, but here goes... I ran into something that sounds kinda like what you're describing. When I would import data from an Excel file, it would scan the first 5 rows or so in order to guess what the datatype was. Well, as luck would have it, sometimes it would get confused between a field that appeared to be numeric and in later rows there was text, etc., etc. I was able to verify this by chopping the table down into smaller pieces until I found the first row that was bombing. Of course the trick to this is that if I uploaded any row by itself, everything worked because it could accurately guess data types.So, what I found, buried somewhere in the MS Knowledgebase, was a registry entry that determines how many rows it scans before guessing the data type. Once I set that to a ridiculously high number... or maybe there was a setting for "entire file", I forget... my problem went away.So if that sounds anything like your problem, I'd start banging on the MS Knowledgebase. |
 |
|
|
skillile
Posting Yak Master
208 Posts |
Posted - 2002-10-15 : 23:10:44
|
| Great info.I guess my question is this. I have a report that comes out what I am guessing is formating on the money fields. Here is some sample data. january, june, "$10,000", mayEverytime I hit the , in the money field it blows up. So is there a way to convert field, field, field to "field", "field", "field" without to much trouble.Thanksslow down to move faster... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-16 : 00:36:33
|
quote:
slow down to move faster...
Hey skilliledon't meant to hassle you, but after reading your last post, I'm still none the wiser what your problem is. You appear to have pretty much ignored the nice man's questions....quote:
So is there a way to convert field, field, field to "field", "field", "field"
huh? Do you mean convert "field,field,field" to "field","field","field"? (in which case it's not going to solve your money question....)or do you want to disregard commas in a .csv when inside quotes?Some smart guy once said to me "slow down to move faster"...guess he was right.Edited by - rrb on 10/16/2002 00:37:20 |
|
|
|
skillile
Posting Yak Master
208 Posts |
Posted - 2002-10-16 : 09:34:41
|
Sorry for not being specific. quote:
huh? Do you mean convert "field,field,field" to "field","field","field"? (in which case it's not going to solve your money question....) or do you want to disregard commas in a .csv when inside quotes
My problem is that the money fields coming back in the *.csv files have format to them ie. "$10,000" all the other fields are just comma sep ie. field, field, field.So my file now looks like this: field, field, field, "$10,000", field, fieldI think I need to make everything: "field", "field", "field", "$10,000"but I am not sure how.Any ideas on this...Thanksslow down to move faster... |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-16 : 11:52:18
|
| But surely BULK INSERT doesn't understand any form of CSV quoting?If all the fields are quoted, you can try to get round it by doingWITH (FIELDTERMINATOR = '","', ROWTERMINATOR = '"\n"')but it will include the initial quote in the first value of the first line and the final quote in the last value of the last line, so you'd probably be making trouble for yourself.If you can do it, your best bet is to export with a separator that isn't present in the data and no quoting. Or use DTS.Edited by - Arnold Fribble on 10/16/2002 11:59:06 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-10-16 : 18:56:17
|
| I'd pre-parse the csv to turn commas which are not quoted into some distinct character...like chr(x) or whateverI'd never be one to quibble....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
CSV and money fields