Data uploads into JDEdwards - MS Office vs OpenOffice

I recently had to migrate some data into JDEdwards. Normally I use MS Excel to format the data and then MS Access to insert it into JDE tables. That can be done either with a simple copy/paste, or with update queries. The process is simple and intuitive.

This time around, however, the client did not have MS Office loaded on their fat client and I was forced to use OpenOffice. "Great", I thought - "this will give me a chance to play with a new product that's available for free and has been receiving a lot of hype lately".

The setup: using an AT&T DSL line, I create a VPN connection, and then RDP to the fat client. Data is inserted via an ODBC connection into EnterpriseOne 8.12 running on a SQL Server database.

OpenOffice results: the spreadsheet created in Excel opened up just fine in Calc and the formatting is OK. It was also easy to create ODBC-linked tables in Base. But to display the data took over a minute (1000 record table). Several times Base locked up and had to be terminated. Once the data was displayed, I would navigate to the bottom to display the last record. Selecting about 1000 records in Calc, pressing <copy> and then <paste> into Base produced.....nothing. After repeating several times, I could not get the data to paste into the linked table. I tried using an append query with the same results. So to summarize: OpenOffice was slow to respond, crashed several times and I couldn't figure out how to upload the data. Maybe one of the readers can help out and provide a method for next time.

MS Office results: after my miserable experience with OpenOffice, the client finally installed MS Office. Opening an ODBC-linked table in Access was immediate. Copy/Paste worked like a charm and nothing crashed.

I keep waiting for OpenOffice to be mature enough to be used for business. They've come a long way with version 3.2 but it's still not stable enough. Maybe the next release? Will there even be a next release now that Oracle has purchased Sun? We'll wait and see.

Hi Boris,
this is very interesting. I would like to encurage customers to use Open Office but from your experience it seams impraticable. Your test was on windows machine i suppose. I'll try too.

Regards

Bruno Condemi

I know you don't want to hear this but you are:

1- Using a numerical calculation tool (spreadsheet) to format data

and

2- Using a desktop/toy personal use database to manipulate large amounts of data

In addition, you are using FOSS apps and wondering why they don't behave like Office tools.


You are simply using the wrong tools for the job and then are surprised at the results? It is only by sheer luck that Excel and Access have worked thus far but they are still the wrong tools for the task.


Do yourself a favor and look into true database tools for data formatting and manipulation. Using DTS/SSIS and a few DML commands will not only get the job done easier and faster but will allow you to start using the intended tools for what you wish to do.




[ QUOTE ]
I recently had to migrate some data into JDEdwards. Normally I use MS Excel to format the data and then MS Access to insert it into JDE tables. That can be done either with a simple copy/paste, or with update queries. The process is simple and intuitive.

This time around, however, the client did not have MS Office loaded on their fat client and I was forced to use OpenOffice. "Great", I thought - "this will give me a chance to play with a new product that's available for free and has been receiving a lot of hype lately".

The setup: using an AT&T DSL line, I create a VPN connection, and then RDP to the fat client. Data is inserted via an ODBC connection into EnterpriseOne 8.12 running on a SQL Server database.

OpenOffice results: the spreadsheet created in Excel opened up just fine in Calc and the formatting is OK. It was also easy to create ODBC-linked tables in Base. But to display the data took over a minute (1000 record table). Several times Base locked up and had to be terminated. Once the data was displayed, I would navigate to the bottom to display the last record. Selecting about 1000 records in Calc, pressing <copy> and then <paste> into Base produced.....nothing. After repeating several times, I could not get the data to paste into the linked table. I tried using an append query with the same results. So to summarize: OpenOffice was slow to respond, crashed several times and I couldn't figure out how to upload the data. Maybe one of the readers can help out and provide a method for next time.

MS Office results: after my miserable experience with OpenOffice, the client finally installed MS Office. Opening an ODBC-linked table in Access was immediate. Copy/Paste worked like a charm and nothing crashed.

I keep waiting for OpenOffice to be mature enough to be used for business. They've come a long way with version 3.2 but it's still not stable enough. Maybe the next release? Will there even be a next release now that Oracle has purchased Sun? We'll wait and see.

[/ QUOTE ]

Jeff,

I understand what you're saying. I would agree that Excel/Access is only practical for smaller amounts of data. The question is how small? In the past, I was able to manipulate rows of up to 500,000 records in Access with a reasonable amount of speed. Of course Excel is much more limiting, with 65,000 row for the 2003 version, and up to 1Million rows in the 2007 version. The appeal of Excel is strong - its so easy to write formulas, perform sorting and cross-references via vlookup.

In your experience with DTS/SSIS, what is the average size of tables that you deal with?

[ QUOTE ]
Jeff,

I understand what you're saying. I would agree that Excel/Access is only practical for smaller amounts of data. The question is how small? In the past, I was able to manipulate rows of up to 500,000 records in Access with a reasonable amount of speed. Of course Excel is much more limiting, with 65,000 row for the 2003 version, and up to 1Million rows in the 2007 version. The appeal of Excel is strong - its so easy to write formulas, perform sorting and cross-references via vlookup.

In your experience with DTS/SSIS, what is the average size of tables that you deal with?

[/ QUOTE ]

I have yet to hit a limit with the SQL tools and I have moved massive amounts of data - entire databases of 300GB, tables with hundreds of millions of records, text data, numeric, BLOB, you name it.

SQL commands can be used to write formulas, sort and cross-reference. Once you learn the language you will find that SQL is far more powerful than Excel, you don't run into data type transformation issues nearly as much and you are working with tools from the designer of the database that were created specifically to natively work with the data.

In addition, DTS and SSIS were designed to work with disparate data sources so one is not limited to moving and manipulating SQL Server data. Also, SSIS is more like a visual programming tool and creating packages that flow data is dead easy. I cannot imagine using Excel/Access when these tools exist.

To be honest though, I have moved from DTS/SSIS to a more programmatic style of moving data using OPENDATASOURCE. This command allows me to grab data from another data source and non-SQL data sources can be used by specifying the provider name (even flat files and Excel). All other SQL commands for sorting, limiting clauses (WHERE), and formulaic commands can be used.


An example is below:


<font class="small">Code:</font><hr /><pre>
INSERT INTO JDE_PRODUCTION.PRODDTA.F4095
SELECT * FROM
OPENDATASOURCE(
'SQLNCLI',
'Data Source=SOURCESERVER;integrated security=sspi'
).JDE_CRP.CRPDTA.F4095
WHERE MLCO = '00250'
</pre><hr />


This combines an INSERT INTO (more on that below) with a SELECT from a 'foreign' data source and will insert records from JDE_CRP.CRPDTA.F4095 WHERE MLCO = '00250' on SOURCESERVER into JDE_PRODUCTION.PRODDTA.F4095 on the local server where you are running this command in SSMS.

You may also want to look into SQL's Linked Servers to move data between data sources if using OPENDATASOURCE doesn't suit you.


The point is there are a whole bunch of better ways to move and manipulate data than spreadsheets and 'personal use' desktop databases. As you found, you hit the limits of those toys pretty quickly.


Here's a couple of postings from my blog that you may find useful:

http://jeffstevenson.karamazovgroup.com/2009/06/quick-sql-table-backup.html

http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html


Although they are about performing a quick table backup and restore, the same commands (SELECT INTO and INSERT INTO) can be used to do what you are looking to do. INSERT INTO copies rows into an existing table and SELECT INTO creates a new table. As with any SQL commands you can combine limiting, sorting and mathematic commands with them.


<font class="small">Code:</font><hr /><pre>
INSERT INTO jde_archive.ardta.f0101 select * from jde_development.testdta.f0101
where ABAN8 NOT IN (jde_archive.ardta.f0101)
</pre><hr />

<font class="small">Code:</font><hr /><pre>
select * into JDE812.SY812.F986101_bak from JDE812.SY812.F986101
</pre><hr />



Here is where I write a summary of sorts but I am too damn tired and it is 10:00 on a Saturday night. Feel free to ask more questions but I'll have to get to 'em tomorrow.