• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

how to import CSV data that consists of mixed numeric and character data into SQL 2000

I am using linked server to import data from a csv file into SQL 2000. However one column contains both integer values and character values and when I query the resultant import table I find that values that contain text have been set to null while the integer values have imported correctly
My file consists of 900 records, 720 have integer values for this column and the remaining records have the text value.
How can I ensure that both integer and character values will be imported into my SQL table?
0
antonms
Asked:
antonms
  • 4
  • 3
  • 2
  • +1
1 Solution
 
St3veMaxCommented:
If this is a one time import; In SSMS Right click on your DB and select Tools, Import.

This will look at your data and determine the best datatype based on the sample.

HTH
0
 
jmoss111Commented:
It would appear that receiving column is of a numeric data type, change to varchar should do the trick
0
 
Anthony PerkinsCommented:
As previously mentioned you need to use the lowest common denominator, in this case varchar or char (and not integer).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
antonmsAuthor Commented:
Hi St3veMax and jmoss111
Unfortunatley this is not a one time import, it will be used by one of our customers on a regular basis as part of an automated data import process. The field value in the csv file can be numeric, text or aphanumeric - all combinations are perfectly valid from a data perspective. What appears to be happening is the Jet 4.0 OLEDB text import provider is sampling the first few lines of csv data to determine what the datatype is, so, for example if the first 50 lines for this field are numeric  then Jet assumes athe datatype to be numeric however if subsequent lines have characters it inserts nulls into the target table (beause they are not numeric) - this seems to stack-up after doing some Googling. We have tried multiple datatypes for the receiving column, but this hasn't helped.
0
 
jmoss111Commented:
Use a staging table to import to and import to prod table from there. Its never a good idea to import directly into prod table.
0
 
Anthony PerkinsCommented:
>>What appears to be happening is the Jet 4.0 OLEDB text import provider is sampling the first few lines of csv data to determine what the datatype is<<
That is exactly what it is doing and I believe 16 is the magic number by default.  But you should not allow it to guess, just always import into a character data column.
0
 
antonmsAuthor Commented:
Thanks and yes I agree with what you guys are saying however the destination table actually uses varchar for all fields populated via Jet.  Jet does determine the datatype by sampling the first x field values in the csv file, however if the first x values are all numeric, then when it imports characters it writes nulls to the holding table even if it is of type varchar. No matter what we try this is what happens. There is a registry value that can be changed to force Jet to sample the whole csv file but we dont like the idea of asking customers to change registry settings on production SQL servers.
0
 
Anthony PerkinsCommented:
So why are you using Jet instead of a text driver? Or perhaps you are not using DTS.

If you insist on using JET perhaps this fix modifying the connection for Excel files when importing through DTS may help you:
Excel Inserts Null Values
PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?
http://www.sqldts.com/254.aspx
0
 
antonmsAuthor Commented:
Hi
We have resolved this problem by referencing this microsoft  support article Q194124.
Essentially we included IMEX=1 and HDR=NO (although the file  has header info) in the connection string.
Including the header info in the data ensured that the first processed record included text therefore Jet assigns the default mixed data type mode (when IMEX=1) which is text. All field values now import with no nulls. I have to say that Jet is a bit pants. Thanks to acperkins for giving us a good lead.
0
 
Anthony PerkinsCommented:
>>Thanks to acperkins for giving us a good lead.<<
You are welcome.  Now please close the question.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now