data importing

[cruel]

Hi, I am new to MS SQL server. I have a csv file sitting on a NT windows (it has all kinds of format, e.g. with % and $ signs), and try to import the data into SQL server database. All data are identified by MS applications as char even though most of them are numeric, integer, money or date. Since the csv file is too big and has a large number of variables, it makes it very inconvenient to manipulate the file. In the SQL script for importing data, I tried to redefine the destination data format accordingly. But importing failed because the source data are strings. I guess I can try import them as char and change data format inside the database. But it will be a very very long sql to write and a big waste of resources. Any suggestions? Appreciate it!

 

[nigelrivett]

I would bcp them all as char fields into a global temp table then insert from there.
You could use a format file or an activex script in dts but these will still force you to code for each field that needs changing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[DrewConn]

I would build a blank table in SQL with all the correct data types then if you use Enterprise manager, just use the import wizard. Make sure to change the destination table from the default one selected to the blank table you just created. This works well.

If you don't use EM, then just use bulk insert to load the new table.

Good luck.