[ale] OT: Data Mapping Tool

[Jim Philips]

If it were up to me, I would export the data from Excel in delimited
form and get it on a box where you can use awk. There is an awk version
for Windows out there on the Net if you need it. You can use awk to
split the strings into your new fields and insert a delimiter. Then you
either import directly into your new database or, if you have to reorder
the fields, go back to Excel and reorder, then dump again in the order
you need for importing into your final database. It's not elegant, but
if your choice of tools is limited, it will work. I'm assuming here that
you don't have a huge amount of data.


On Wed, 2002-03-13 at 16:08, Kevin O'Neill Stoll wrote:
> Sure. Something like I have some lease data. The database and the
> spreadsheet contain the same datatype type information but the columns are
> not named the same. As well, the spreadsheet contains data that in the
> database is really broken up into three or four tables with relationships
> to tie them back together.
> 
> Hopefully, this will do because If you are asking can I give you a couple
> of recordsets from each data source, sorry, no, I like being employeed :)
> 
> Kevin
> 
> 
> 
> --- Jim Philips <jcphil at mindspring.com> wrote:
> > Well, if you're going to use standard UNIX tools, awk would do just
> > fine--especially if you give it clear delimiters to work with. Then you
> > could use whatever import tool works with the given database. But all of
> > this may be too simple for the task at hand. Can you give us an example
> > of your "from" and "to" data?
> > 
> > 
> > On Wed, 2002-03-13 at 15:54, James P. Kinney III wrote:
> > > Sounds like a job for perl and regular expressions. If you can define
> > > the data types that go in each table, the separation into discrete
> > lists
> > > by a comparison shouldn't be too hard. Once split, iterate through
> > each
> > > list and insert into the designated table.
> > > 
> > > As most spreadsheets have some sort of common factor joining column
> > and
> > > row relationships, it should be quite easy. Even easier if you have
> > > "column M is table M" and "columns A-L are tables A-L". Probably not
> > :)
> > > Too easy.
> > > 
> > > 
> > > On Wed, 2002-03-13 at 15:42, Kevin O'Neill Stoll wrote:
> > > > Hello All,
> > > > 
> > > > I am in need of a tool that can handle a combination of importing
> > and
> > > > mapping data to a database.
> > > > 
> > > > Example of my problem would be this:
> > > > 
> > > > I have a spreadsheet of data that I wish to import into a database
> > BUT..
> > > > this spreadsheet really contains data that needs to be divided up
> > into
> > > > three or four different tables in my database.
> > > > 
> > > > 
> > > > What I would like to be able to do is choose a delimited file to
> > import
> > > > and then choose the 'mapping schema' that matches my database. In
> > other
> > > > words, import data from one file into multiple tables that don't
> > have the
> > > > same field name or number of fields but do have the proper data
> > types.
> > > > 
> > > > 
> > > > Thanks for your help in advance, I have always received awesome
> > answers
> > > > back from this list. Keep up the great work :)
> > > > 
> > > > Kevin Stoll
> > > > 
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Try FREE Yahoo! Mail - the world's greatest free email!
> > > > http://mail.yahoo.com/
> > > > 
> > > > ---
> > > > This message has been sent through the ALE general discussion list.
> > > > See http://www.ale.org/mailing-lists.shtml for more info. Problems
> > should be 
> > > > sent to listmaster at ale dot org.
> > > > 
> > > -- 
> > > James P. Kinney III   \Changing the mobile computing world/
> > > President and COO      \          one Linux user         /
> > > Local Net Solutions,LLC \           at a time.          /
> > > 770-493-8244             \.___________________________./
> > > 
> > > GPG ID: 829C6CA7 James P. Kinney III (M.S. Physics)
> > > <jkinney at localnetsolutions.com>
> > > Fingerprint = 3C9E 6366 54FC A3FE BA4D 0659 6190 ADC3 829C 6CA7 
> > > 
> > > 
> > > 
> > > ---
> > > This message has been sent through the ALE general discussion list.
> > > See http://www.ale.org/mailing-lists.shtml for more info. Problems
> > should be 
> > > sent to listmaster at ale dot org.
> > > 
> > 
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free email!
> http://mail.yahoo.com/



---
This message has been sent through the ALE general discussion list.
See http://www.ale.org/mailing-lists.shtml for more info. Problems should be 
sent to listmaster at ale dot org.