dabdowb
asked on
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
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
>>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.
Than why even bother with SQL Server? If you are comfortable with MS Access than stay with that.
ASKER
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.
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.
>>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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
>>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.
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.
ASKER
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 ;-)
http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes