March 31, 2011 at 12:58 pm
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.
March 31, 2011 at 1:30 pm
Excel is your friend here unless you're dealing with a huge amount of data.
Pull it into Excel first. Format the fields appropriately. The you can either pull the Excel file in or simply copy and paste the data. My favorite way to overcome the preceding zeros issue is to copy and paste from Excel to SQL (at least you can in SQL 2005). You have to have the table structured the same as the Excel file, but it works like a charm that way.
Importing an Excel spreadsheet can be frustrating with preceding zeros. Even if you format the column to text, it doesn't always work right when the file is pulled into SQL Server. It's as if once Excel deems a column numeric, it won't give up on it.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 31, 2011 at 1:55 pm
It is a 400,000 record file. I can't do it that way, but thanks.
March 31, 2011 at 2:41 pm
Use BULK INSERT with a pipe delimiter to target MONEY datatype columns in a staging table. Then, transfer the data from the staging table to the final table.
As a side bar, you should almost always use a staging table to load data into so you can validate it before you put it into its final resting spot.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply