• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

Numeric field type for SQL table that allows commas in the number

This should be easy, but then, there is Microsoft.  I can't believe the difference between Access and SQL...it is nuts.  In Access 2000, I can import the text file I am using from our network into a table and choose a type of 'Double' to accept in a numeric value that has commas and decimals in the number....such as 1,000.000, etc.  No problem at all....would have been done days ago.

Now, I try to do a BULK INSERT into SQL, with the same pipe delimited text file on the network, but the SQL table will not import the numbeic value that has the commas and decimals in it, unless I make it into a money category.  I don't want it as money, I just want it as a number (could care less if the commas are in there or not, but need the decimals).

How??  It has been proposed to me to import as text into a temp table and then transfer that to the target table with the formatting the way I want, but why should I have to make the server work double duty for one import?  How is that efficient?

Thanks,
Matt
0
dabdowb
Asked:
dabdowb
2 Solutions
 
radcaesarCommented:
Remove the commas from file before export. SQL Server will not allow you (I Believe Access too). Having commas in numeric value is not consistent for calculations. Its a poor design.

http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes
0
 
Anthony PerkinsCommented:
>>In Access 2000, I can import the text file I am using from our network into a table and choose a type of 'Double' to accept in a numeric value that has commas and decimals in the number....such as 1,000.000, etc.  No problem at all....would have been done days ago.<<
Than why even bother with SQL Server?  If you are comfortable with MS Access than stay with that.
0
 
dabdowbAuthor Commented:
We are moving from Access to SQL Server as our primary analysis tool, so I have no choice but to move to SQL.  Our company is shifting to not supporting Access anymore.

I just find it a bit frustrating that the same company makes the software, yet chose t make SQL into a two step process to do something they clearly knew how to setup in one.  While I understand that using commas in the numerics is sloppy, it is what my director and VP want, so trying to give them what they want.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>We are moving from Access to SQL Server as our primary analysis tool, so I have no choice but to move to SQL. <<
Then the sooner you realize that MS Access and SQL Server are totally different animals developed by different teams in different countries the better.

>>Our company is shifting to not supporting Access anymore.<<
I am clearly not surprised.

>>yet chose t make SQL into a two step process to do something they clearly knew how to setup in one. <<
What you need to understand is that MS Access as well as a database file has a presentation layer that is absent from all true RDBMS.  There are no "commas in the numerics" whether it is MS Access or SQL Server or Oracle or MySQL.  What you are seeing in MS Access is the way that you have chosen to display the numerics using a format style.  This has nothing to do with the way it is stored in the database nor should it be.
0
 
dabdowbAuthor Commented:
I understand all that...I really do, and I am in favor of moving to a better environment for our data, especially since we are dealing with tables that are between 5 million and 30 million records long and up to 300 columns wide....just makes sense.

As you mentioned, even in Access data is not stored with commas in the numerics, yet, I can set the column type to "double" when I import it into Access and it pulls in the data just fine....no issues, no conversions necessary, quick and dirty.  Yet if I try that in SQL, error messages abound and no import.  Just figued SQL would have made the environment as friendly as that of Access and simply done all the conversions needed in the background.  For those who wish to splice the data differently, then they can do so with their expertise in the SQL language and the SSIS environment.

It sounds like everywhere I turn with this question, the answer is the same, import the data as text into a temporary table and then move it to a new table with the numeric format in place.  I can't remove the commas from the data that is being given to me without running the risk of removing commas that actually need to stay in place for other fields, such as material descriptions.

Unless, there is another trick up someone's sleeve?
0
 
Anthony PerkinsCommented:
None that I can think of.  The sooner you convert that data to a true numeric the better.  This was a bad design decision to begin with and you should not compund it by attempting to patch it when converting to SQL Server.

Incidentally, do not use the float data type, it suffers exactly the same problem as double in MS Access in that it is an approximate value.  Instead use numeric (or the equivalent decimal).
0
 
rmm2001Commented:
Have you tried using SSIS to import the data? You'll have to add in a conversion task to convert the data types but that should be faster than what you're doing now and less stressful. It will also give you more flexibility to pipe errors elsewhere when loading the data.

If not - why not make a new column in your access database and put the value to be the comma numeric value minus the commas. And then drop the "comma numeric" column and rename the new column with the old name.
0
 
dabdowbAuthor Commented:
I gave partial credit to rmm2001's answer because I am sure that solution would also work, but I am not fluent enough with SSIS yet to do it.  I ended up doing the way acperkins suggested.  My only criticism is that I didn't need a lecture about me supporting data types that are not valid for loading into SQL.  I have been working with databases for the past 10 years and I am well aware of normalized databases and proper data types, but when I am not in control of how the data is given TO me, then I am left only to work with what I am given.  Had I control of the data given to me, I wouldn't have had to post this question in the first place.  Fine to teach, but don't be so abrasive when I have already explained the data I receive is NOT in my control.
0
 
Anthony PerkinsCommented:
>>don't be so abrasive when I have already explained the data I receive is NOT in my control.<<
I apologize. I thought you had said you had been using MS Access, I did not realize you had so much database experience and that is why I had thought you would not be familiar with good database practices, including appropriate data types and what to expect from a RDBMS system such as MS SQL Server or Oracle.
0
 
dabdowbAuthor Commented:
It is all good acperkins...I know you all are just trying to help educate where you can.  I do have alot of experience with Access, which is what my company primarily uses for base users, but I have studied about 7 different programming languages over the years and have a computer science background, as well as some experience with the Oracle and SQL databases over the past years.  Problem is, I did not have any training, so sometimes I hit a wall (albeit many times a minor one once I understand the issue), but still frustrating, so left to figure it out.  I SOO wish our IS department was more cooperative on giving the end users data in formats that would be more useful for their application, but not the case.  Takes me more time to find work arounds then it would if they just let me get to the core data myself to spit out as I need it.  C'est la vie!  Thanks again for the help....and the advice ;-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now