PDA

View Full Version : Database export



Stevo
22-Jul-2014, 20:53
Anyone have any experience exporting data from foxpro (maybe visual
foxpro) to be able to be converted / imported into sql?

--
Stevo

Dave Howe
23-Jul-2014, 15:52
On 22/07/2014 20:53, Stevo wrote:
> Anyone have any experience exporting data from foxpro (maybe visual
> foxpro) to be able to be converted / imported into sql?

odbc probably your friend there :)

Stevo
23-Jul-2014, 21:42
Dave Howe sounds like they 'said':

> odbc probably your friend there :)

So my response to Dave's comment is...

We don't have visual foxpro installed anywhere. This is from a small
software package developed by an outside source.

--
Stevo

Scott Campbell
23-Jul-2014, 21:51
Stevo wrote:

> Dave Howe sounds like they 'said':
>
> > odbc probably your friend there :)
>
> So my response to Dave's comment is...
>
> We don't have visual foxpro installed anywhere. This is from a small
> software package developed by an outside source.

You should still be able to download the ODBC Visual Foxpro driver and
point it to the database and then dump the data in to Excel. Then
you'll need to cleanse the data.

If you have data that format appropriately in to Excel (such as codes
that have leading zeros), then you can use something like WinSQL to
issue your queries against the ODBC datasource and then copy/paste it
in to something else...

http://msdn.microsoft.com/en-us/library/ms710251%28v=vs.85%29.aspx

Updates page which has link to the OLE driver and a reference to the
ODBC driver
http://msdn.microsoft.com/en-US/vfoxpro/bb190232

Scott Campbell
23-Jul-2014, 21:55
And further to that, if you do dump the tables in to Excel you can
create formulae which will create the INSERT statements for each line.
You'll just need to set up the destination tables in which-ever
database engine you are using first and make sure you honour data-types
and constraints.

Stevo
23-Jul-2014, 22:54
Scott Campbell sounds like they 'said':

> And further to that, if you do dump the tables in to Excel you can
> create formulae which will create the INSERT statements for each line.
> You'll just need to set up the destination tables in which-ever
> database engine you are using first and make sure you honour
> data-types and constraints.

So my response to Scott's comment is...

You sure are talking like I've done a bunch of db admin work before. ;-)

Only db stuff I've ever done pretty much is with Groupwise and I don't
really have to do any dba stuff with that.

--
Stevo

Scott Campbell
24-Jul-2014, 01:53
Stevo wrote:

> So my response to Scott's comment is...
>
> You sure are talking like I've done a bunch of db admin work before.
> ;-)
>
> Only db stuff I've ever done pretty much is with Groupwise and I don't
> really have to do any dba stuff with that.

I guess the question then becomes what are you comfortable with, with
databases?

Have you ever set up a DSN in the ODBC Manager?

Do you understand the standard sql statements, select, insert, update,
delete?

High level steps would be like this:

1). install the foxpro ODBC driver on your computer (ideally a 32bit
machine to avoid confusion because the ODBC Manager is different for
32bit and 64bit)
2). Add a DSN and configure the foxpro ODBC driver (through Start ->
Control Panel -> Administrative Tools -> Data Sources (ODBC)) to point
to a copy of your foxpro database.
3). If you are using Excel go to Data -> From Other Sources -> From
Microsoft Query
4). Choose the DSN created in step 2 and click on ok
5). enter credentials for the database if needed
6). you should be prompted to select a table from the database, click
next
7). click next
8). click next
9). select "Return Data to Microsoft Excel" and click finish
10). select where in the spreadsheet you'd like the data dumped to

And now you have the contents of the table dumped to Excel. From there
you need to insert the data, so in the past I would use concat
statements in Excel to create an INSERT statement, then copy and paste
that in to a tool like WinSQL to bulk load data.

You do need to be wary if you are moving between different database
enginers as some column types aren't supported or work differently -
mySQL plays it fast and loose, whereas DB2 can be a real syntax nazi.

And I believe foxpro is pretty pedantic about ODBC driver versions.

Susan
24-Jul-2014, 02:06
Steve:

You might check this:
http://stackoverflow.com/questions/16819811/how-to-convert-visual-foxp
ro-database-into-sql-server-database : )

--
Susan
Novell Community Chat Moderator

http://forums.novell.com/faq.php?faq=novfor#faq_rules
http://www.ncci.org NCCIrregulars Web Site
https://www.facebook.com/groups/NCCIrregulars

Please read the following before posting in here:
http://preview.tinyurl.com/27zopdy

Dave Howe
24-Jul-2014, 13:44
On 23/07/2014 21:42, Stevo wrote:
> Dave Howe sounds like they 'said':
>
>> odbc probably your friend there :)
>
> So my response to Dave's comment is...
>
> We don't have visual foxpro installed anywhere. This is from a small
> software package developed by an outside source.

Foxpro odbc driver is a free download from ms site.
you should be able to use it with DTS, or MS Access, to grab the data
from the tables and/or report on it directly.

Stevo
24-Jul-2014, 19:09
Susan sounds like they 'said':

> Steve:
>
> You might check this:
> http://stackoverflow.com/questions/16819811/how-to-convert-visual-foxp
> ro-database-into-sql-server-database : )

So my response to Susan's comment is...

I had already looked that over, still pretty greek to me.

For stuff like this, I'm usually done if the server can be pinged. ;-)

--
Stevo

Susan
24-Jul-2014, 23:41
Have you installed the FoxPro ODBC to see what you can see? :)

--
Susan
Novell Community Chat Moderator

http://forums.novell.com/faq.php?faq=novfor#faq_rules
http://www.ncci.org NCCIrregulars Web Site
https://www.facebook.com/groups/NCCIrregulars

Please read the following before posting in here:
http://preview.tinyurl.com/27zopdy

Stevo
24-Jul-2014, 23:45
Susan sounds like they 'said':

> Have you installed the FoxPro ODBC to see what you can see? :)

So my response to Susan's comment is...

I have not, but a person in our dept (who has taken dba classes) figure
out some stuff and I guess got the data extracted.

--
Stevo

Susan
25-Jul-2014, 15:32
Yayyyyyyy! :)

--
Susan
Novell Community Chat Moderator

http://forums.novell.com/faq.php?faq=novfor#faq_rules
http://www.ncci.org NCCIrregulars Web Site
https://www.facebook.com/groups/NCCIrregulars

Please read the following before posting in here:
http://preview.tinyurl.com/27zopdy

salisburyk
05-Sep-2014, 13:26
Stevo,

Great - caught your post very late. I have plenty experience with VFP
and data exporting if the need is still there.

Kevin


--
salisburyk
------------------------------------------------------------------------
salisburyk's Profile: https://forums.novell.com/member.php?userid=5436
View this thread: https://forums.novell.com/showthread.php?t=478404