Unanswered: Problems importing TXT files using DTS
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.
I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.
At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.
I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.
You are correct it was bad data. That should have been the first place I looked.
I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.