Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing CSV to SQL 1

Status
Not open for further replies.

Naith

Programmer
Joined
May 14, 2002
Messages
2,530
Location
GB
Hi,

I'm using SQL Server 8, and coming from the school of Oracle, I'm unfortunately an utter noob with SQL Server.

I'm attempting to import a csv file into SQL, like the following:
Code:
ID    Name
----  --------
  01  One
  02  Two
  03  Three
The SQL table is expecting a float and varchar value for col 1 and 2 respectively. However, the csv import fails because col 1 is being read as "01"; a string.

What's the best way around this? Remember, my SQL Server is very limited!
 
Well there might be a better way to do this, but I'd bring it into a separte holding table that you create during the import. Then I would check to see if any of the data actually was string and not numeric data.
Code:
Select sum(isnumeric(IDField)), count (*) from HoldingTable

If the two numbers match, then you are ok and you can insert to the table you want. If there are existing records that need updating do that first. Then do the insert of new records , then delete any that are no longer needed if you have that as part of your process.

YOu can use Cast or convert (See Books online for the syntax) to convert the data to an integer in your update and insert statements. Be aware that once you change this to an integer, you will lose the leading zero.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
IF you are using the DTS Import/Export application thru Enterprise Manager, you should come across a screen with a label "Select Source Tables and Views". It should be the 2nd or 3rd screen before you actually execute the DTS package. Here you can transform your columns to what you need them to be.
 
Hi,

Thanks for your responses guys.

SQLSister, I did think that using a staging table comprised entirely of varchar columns, then running a SELECT INTO converting all the relevant numerical columns to float would be one way forward, but it seemed a bit clunky to do it this way. I'm having varying degrees of success like this. I wondered if it was possible to have the data import process clean the data during the import.

PRPHx, I am using the DTS Import/Export application , and I actually did try your suggestion prior to coming online, thinking it would provide a solution as you mentioned. However, when I doctor the columns via the Transform functionality to be float where appropriate, then attempt to execute the package, I get an error complaining about datatype inconsistency between the source and destination:
Code:
Error during Trandformation 'DirectCopyXform' for Row number 1. ... Conversion invalid for datatypes on column pair 17 (source column 'Bill Qty' (DBTYPE_STR), destination column 'Bill Qty' (DBTYPE_R8))
Am I perhaps missing something in the import process?

Naith
 
By the way float is a really bad datatype to use.

It is an approximate datatype and will not be good if you want exact numbers or if you do math on the data. IOnteger or desimal or money datatypes are much better to use in general for numeric data than float or real.

I do create a process to clean the dat during import, but i almost alwys bring it itno a holding table first and then scrub with SQL statments before doing my last import. You can do this in DTS by using the Exec SQl task as part of your DTS package, so once it is set up, it can be an automated task.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Thanks Sister,

I'll work on that approach. Cheers for the heads up on float, too.

Have a Merry Xmas now,

Naith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top