December 13, 2007 at 5:59 pm
I would like to import a DBF file into my database, but when I try to "import data", bringing up the import data wizard, there's no option for a DBF file as a "source" in the dropdown list.
Does anyone know how to get new options in that list?
(I remember I had a similar problem with "Flat File" not showing up, and it turned out to be something extremely strange -- like some other program interfering in some way. I'm hoping this is not the same problem.)
December 13, 2007 at 8:12 pm
DBF? Sounds like another product's equivalent of an MDF or MDB, which you can't import directly to the best of my knowledge - you need to have that database up & running in its own product, then connect via ODBC to suck the data out.
What happens if you open the file with something like Notepad (assuming it isn't more than a few 10s of MBs in size)? Do you see delimited text or random garbage?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 13, 2007 at 8:15 pm
Try the "dBase File" ODBC driver.
SQL = Scarcely Qualifies as a Language
December 13, 2007 at 9:02 pm
or Foxpro...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 10:19 am
Well ... Here are my only choices:
Net Framework Data Provider for Odbc
Net Framework Data Provider for Oracle
Net Framework Data Provider for SqlServer
Flat File Source
MediaCatalogDB OLE DB Provider
MediaCatalogMergedDB OLE DB Provider
MediaCatalogWebDB OLE DB Provider
Microsoft Access
Microsoft Excel
Microsoft OLE DB Provider for Analysis Services 9.0
Microsoft OLE DB Provider for Data Mining Services
Microsoft OLE DB Provider for Internet Publishing
Microsoft OLE DB Provider for OLAP Service 8.0
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for Outlook Search
Microsoft OLE DB Provider for SQL Server
SQL Native Client
SQLXMLOLEDB
SQLXMLOLEDB 4.0
VSEE Versioning Enlistment Manager Proxy Data Source
I have worked on systems before where I know there were more choices. It all seems so random. Does anyone know where this list comes from?
(The workaround is to convert the DBF files to Access and then import them ... but it would be nice to be able to skip that step.)
December 14, 2007 at 10:25 am
Scott: if I open it in notepad it looks kind of like this:
CDSCODE C C C D C S C CSNUM C ENUM N FPANUM N FLNUM C MNUM N SNUM N SSNUM N ALLNUM N PERALL N
01612000134536 01 61200 0134536 2 1 1 3 1 8 4 01612590107169 01 61259 0107169 1 1 8 01612590107417 01 61259
So they look like fixed-width columns of text (the HTML hides most of the blanks), but there is some garbage in the beginning.
December 14, 2007 at 11:21 am
Try this:
First start by creating an ODBC data source:
- Control Panel, admin tools, data sources (ODBC)
- switch to the System DSN tab, click on Add. Pick one of the following drivers:
Microsoft DBase Driver (*.dbf)
Microsoft DBase VFP Driver (*.dbf)
Microsoft FoxPro Driver (*.dbf)
Microsoft Visual FoxPro Driver
(you might need to try several to find the best match)
- click next, give the data source a name
- uncheck the "current directory" check box, and use the buttons there to point to the specific directory where this thing is, and possibly any indexs you might have. Try to pick the best match for which version of DBase/foxpro created this thing (start higher and work backwards if need be).
- you may opt to show or not show deleted records (you would "delete" by marking things as deleted, but not get rid of them until a file was packed, so you might have records that still haven't been packed out).
- click on Finish
Once you do that - go back into import wizard and use the .NET provider for ODBC, and you should be be able to access the DSN you just created (look for the name you used earlier).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 12:06 pm
Thanks Matt -- I was able to create an ODBC data source using your excellent instructions.
However ... when I try to import it into SQL Server using the NET ODBC provider, it ends up wanting me to write a SQL script to import the data. I really have no idea how to write a script to get something out of an ODBC data source.
So, unless that's a really easy thing to do ... I may just stick to importing into Access, and then importing that into SQL Server.
March 1, 2009 at 5:55 pm
SSC Rookie - Did you ever get a response to this thread? I'm trying to import data, via ODBC, in an SQLServer 2005 database and I am seeing the same list of options.
I don't see an option to select the System DSN ODBC data source that I created. Back in SQL 2000 you could easily see ODBC data sources.
When I make it past this, like you, all I see is the "Write a query to extract data". I want to select the "Copy data from one or more tables..." but it is grey'd out.
Any help would be much appreciated.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply