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 |
|
stujensen
Starting Member
2 Posts |
 Posted - 2009-10-02 : 10:56:30
|
| Hello,I have an Export/Import process written in VB.NET 2005 using the JET OLEDB Text Driver to create a DataReader and SqlBulkCopy to populate the Table. I use Schema.ini files to define the columns and data types. All works very well except for one issue.The Source DB contains columns of various text data types like varchar + char where DBNull and Empty string need to be maintained across the Export/ImportDuring the Import Process the OLEDB Text Driver is treating any column with no data between the delimiters as DBNULL. I've tried just about everything I can think off.Empty quotes ""Quotes with spaces " "NIL CharacterQuoted NIL Character(BCP uses char zero for empty strings)All result in a DBNULL when I access the CSV via OLEDB Text Driver.I know I can resort to creating a DataTable and perform my text translation and poplate the table one row at a time, then bulk load that. However, I am dealing with almost 1 million rows and the performance is way to slow. Another option is to kick off the BCP process, but then I loss some control for handling errors.There must be a way to distinguish an empty string from DBNull in the same fashion that BCP can?Thanks for any helpStuartExample schema.ini. The %xxx% gets replaced accordingly[%CSVFileName%]ColNameHeader=%HeaderRow%Format=Delimited(%Delimiter%)DateTimeFormat=MM/dd/yyyyCharacterSet=65001Col1=CustomerId ShortCol2=TerrId LongCol3=EffDt DateTimeCol4=TerrCd Text |
|
|
|
|
|
Export/Import CSV File DBNull vs Empty String