Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-12-15 : 05:45:37
|
| Greetings...I have some data stored as .CSV files which represents a NULL as -0-. This occurs in all the .CSV files and are not limited to a specific column.There are two approaches I can see to this. One is to get the data in to SQL Server tables and then scan through each column to replace the -0- symbols with NULLs or do the conversion while the data is being transformed from the .CSV files to the SQL Server tables.I know how to do the replacing for each individual data pump but I am more interested in a solution which would convert the -0- to NULLs in one go.Any ideas?________________Make love not war! |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-15 : 08:12:40
|
| Most the posts I've read regarding DTS speak of importing the CSV to a staging table first. Data is then transferred to the destination tables in a second step - principally so data can be massaged before insertion.I import all fields as varchar to the staging table, then convert to the destination datatype (numeric, bit, ... ) before inserting in the destination table. Conversion to NULL would happen at this step. |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-12-15 : 08:25:54
|
| Yes, I think I will do the manipulation once the data is in SQL Server tables.Thanks.________________Make love not war! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2003-12-15 : 10:19:20
|
| Have you considered removing the string before SQL Server tries to import it? For example if your data looks like:A1,-0-,37then do a search & replace to A1,,37You could probably whip up something pretty quick in .NET to do it using regular expressions. You might also be able to use some of the old unix tools (one of sed, grep or awk should do the trick). Heck, I'd even consider writing a word macro to do it. Of course, this depends on the size of your import file.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
DTS