I've got a couple dbf files (from Visual FoxPro) that I routinely import into a local SQL server (via OPENROWSET) for further manipulation. I've been doing this for a while now, but today I realized that not all of the data is getting imported correctly.
There are several columns that act as indicators, using a single character. There are 15 of the columns, and generally anywhere from 5 to 15 of them may be populated:
Record1 s w X f d + y
Record2 s w X f d +
Record3 s w X f _ y
Record y s _ F b d
...and so on. What I've noticed is that fields using a single underscore are getting imported as NULL. To further complicate things, the fields following the underscore are then NULL as well, but for some reason, the OPENROWSET recovers, and
the NULLs stop once the 15 indicator columns are finished. So basically a record that has "y s _ F b d" becomes "y s NULL NULL NULL NULL".
Obviously I can scrub this file before importing it, but it kind of defeats the purpose of automating the import via the OPENROWSET. I don't know if anyone else has run into this, as I searched the web and couldn't find anything, but any help would
be appreciated. Just for background, here is the OPENROWSET command I use:
EXEC('SELECT * INTO TABLENAME FROM OPENROWSET('MICRO
View Complete Post