DTS: Import a number over 2 Billion?
-
Here's the DTS import question of the day:
How do you import a number over 2 Billion.
We are trying to import data from a CSV file into the database. The only time the data is quoted is when it is a null field, then you get double quotes. The problem is that some of our account numbers appear to be greater than 2 billion in the CSV file. Therefore it doesn't read them in because it see the first 100 or so rows as int, not bigint.
I want them imported as text. I tried a CSTR but that doesn't help. I cant get it to take CAST or CONVERT in the DTS select statement.
-- DTS Select statement -------------------------
select `Bank Number`,`Appl Id`,CSTR(`Account Number`) AS Account_Number,
`M Accounting Status Code`,...... Reporting`
from `\\SERVER\WHSE\Output\EOM`\`Eom_data.csv`
-- Example Data ---------------------------------
Bank Number,Appl Id,Account Number,M Accounting Status Code,......, Reporting
113,CCD,1369011111,"",
113,CCD,1369022222,"",
113,CCD,2160011111,"",
113,CCD,2160022222,"",
113,CCD,2160033333,"",
--------------------------------------------------
I'm also going to face this with 16 digit credit card numbers. Anyone have any ideas?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Hi Jim P.,
If you just follow the DTS Import Wizard in SQL 2000, use the text provider for the source (NOT EXCEL for CSV!!), point to your CSV file, then in the file format dialog, use delimited, text qualifier Double Quote, and First Column has column names if your file looks like your sample...
Then Specify the Comma as your comma delimiter, and you should see your sample rows. The Destination should already be pointing at the correct target database if you chose Import, so click next, and then click on the transform button, and make sure that you are using a VARCHAR(16) to handle up to Quadrillions. (And check the formats of your other columns...) Now make sure to save your DTS Job. I like to save it to the local server, and then from there save it as a VB file for advanced Manipulation.
(One trick is to create a small file as you did on your sample, create the DTS Package, then replace the small file with the large one, and rerun the package.)
If you are planning to append rows on a regular basis, rerun the wizard after the small table was created, so that that the Append Rows choice is Checked, this will also remover the create table task form the job which will fail each time after the first...
Finally, DTS Jobs keep the context of the Machine running the tool, so, unless a file path is common to both the server and to the Machine used to create the DTS Package, you may receive errors if you try to run the package directly from the server. (If you schedule it for example.) So make sure that the CSV file can be seen by the server directly.
Hope this helps.
John R. Hanson
-
I found a solution to the problem.
The DTS was picking up the first several hundred rows as a sample. DTS then assumed they were all an Int data type. What I had the programmers that export the data is sort the [Account Numbers] in descending order. That way the DTS is picking up the BigInt data type first.
I still have to go back and run an update query to get the leading zeroes back in. That turns out to be easy enough. We have 10 digit and 16 digit account numbers. The 16 digit accounts never start with a zero. So I just have to run the query:
Update TableName
Set Account_Number = Right('0000000000' + Account_Number,10)
Where Len(Account_Number)<10
It still qualifies as a real PITA
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Finally found the real solution....by trying to correct another problem.
It is in the ODBC Administrator (ODBCAD32). Within the "Microsoft Text Driver (*.txt; *.csv)" call setup you have a Options button and on the bottom is "Define Results". You edit in there to correct for the problems.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply