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/Import
During 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 Character
Quoted 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 help
Stuart
Example schema.ini. The %xxx% gets replaced accordingly[%CSVFileName%]
ColNameHeader=%HeaderRow%
Format=Delimited(%Delimiter%)
DateTimeFormat=MM/dd/yyyy
CharacterSet=65001
Col1=CustomerId Short
Col2=TerrId Long
Col3=EffDt DateTime
Col4=TerrCd Text
|
|
|
|
|
|
Export/Import CSV File DBNull vs Empty String