Importing text file with numeric

[abenitez77]

I have a text field that uses | (vertical bar) as the field delimiter. It has numeric data and uses commas with periods (ie 168,324.00). I am having problems importing this text file. It does not like the commas and truncates the number to 168.32. How can I Import this? I tried importing into a table i created that has real type and I tried numeric type. Both don't work. I also have a column that has "010" in it and it truncates leading zeros because it puts it into a real column.

 

[SQLBill]

Real and Numeric don't accept a comma because a comma is not a 'number'.

If you want to keep the comma, you will need to use a VARCHAR type (varchar, nvarchar, char, nchar, etc) column.

If you want to use numeric/real, you will need to import the values into a staging table that is VARCHAR, remove the comma(s) and then import that into the numeric/real table.

Either way, you will lose the leading 0. The only way to keep that is to use VARCHAR.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[abenitez77]

thanks...how about if i import it into a currency column? mysql has no problems importing that ...it is smart enough to strip the comma.

 

[SQLBill]

Now are you using MySQL or Microsoft SQL Server? They aren't the same thing and this is the forum for Microsoft SQL Server. There are other forums on this site for MySQL.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[abenitez77]

I'm using MS SQL Server...but the person that gave me the text file ...imported the file into mysql, with no problem.

 

[SQLBill]

You could use Money (SQL Server doesn't have Currency). That will retain the comma.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[abenitez77]

thanks, money worked like a charm.