Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Suggestion On How To Transfer Flat File Database

database

This topic has been archived. This means that you cannot reply to this topic.
7 replies to this topic

#1 Zer033x

Zer033x

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 50 posts

Posted 26 January 2015 - 09:02 PM

Hello, I want to know if anyone has any suggestions on what tech I should use for a database system where I will have to be able to select records based on criteria then create a flat file from the selected records to be used with other programs.

 

I have a flat file database of millions of records and I want to update it to be a relational database using more recent technology. Currently, the records are selected with an old IBM mainframe language where records are selected by byte position, it's a very old system. I want to know what would be some suggestions on how to convert the flat file database to a more up to date model. The database undergoes constant updating of records based on other input files where matching is performed and certain fields are updated, I'll need to be able to still do this.

 

The usage of the database is to select records from it based on specific criteria in each field or some combination of criteria where the output file of the selection is a flat file that is used in other programs for processing, so I'll need to be able to retain this functionality as well.

 

I know a little about current databases from a website I have that I've done a little bit with and it uses SQL so if that is the best avenue then let me know, but if there are alternatives to it that could be better or comparable I'm interested in those as well. It isn't just a database technology solution I'm looking for it is also the most reliable or easy way to convert from flat file to whatever database technology recommended. Any options available would be helpful if there are multiple competing solutions and a pro/con type thing to each could help. Thanks.



#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 27 January 2015 - 05:07 AM

For flat files, Access has some good tools for importing them based on offsets. That may help you out.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Zer033x

Zer033x

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 50 posts

Posted 27 January 2015 - 06:22 AM

Can access withstand millions of records?



#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 27 January 2015 - 07:01 AM

Unfortunately, that's a really vague question. How many tables are you talking about? How many records per table? What data types?

 

To be honest, I'd probably switch to SQL Server, which also has data import tools and would probably work better in that case. It can deal with hundreds of millions of records in a single table.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts

Posted 28 January 2015 - 06:15 AM

I think the first thing would be to import it into a database table or excel file and verify the formatting.  Once you have the correct format, you can normalize the database any way you wish, fairly easily, by creating a new table,and doing a simple insert/select sql statement.

 

Work with the lookup (or child table) table values first, then work towards the main tables, using the references from the lookup/child tables..


My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#6 Zer033x

Zer033x

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 50 posts

Posted 28 January 2015 - 06:39 AM

Unfortunately, that's a really vague question. How many tables are you talking about? How many records per table? What data types?

 

To be honest, I'd probably switch to SQL Server, which also has data import tools and would probably work better in that case. It can deal with hundreds of millions of records in a single table.

 

Currently the largest table is about 30 millin records of all types of data types from text to dates to int and floats. There are about 300 fields of data. When I was looking to do something similar to this about a year ago I came across something called SSIS which seemed like it was for data import, would that work in this case?


I think the first thing would be to import it into a database table or excel file and verify the formatting.  Once you have the correct format, you can normalize the database any way you wish, fairly easily, by creating a new table,and doing a simple insert/select sql statement.

 

Work with the lookup (or child table) table values first, then work towards the main tables, using the references from the lookup/child tables..

 

Can you explain this stuff a little more I don't quite follow. The tables I'm working with are also millions of records so can excel handle something like that? When you say import it into a database table or excel file and verify formatting what do you mean by formatting? Wanting to import into a database table is essentially the main thing I want to do. Also don't know what you mean by lookup (child tables) values first and main tables. I'm really only looking to have a single table with all my data. Now when I do maintenance or updating of the table I imagine I would have different tables to compare/add/subtract/change the main table with, but for the initial import of data I don't think I need that.



#7 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 28 January 2015 - 07:09 AM

The exact import technology depends a little on the version of MS SQL Server. SSIS could well be what you're looking for, though.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#8 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 28 January 2015 - 07:09 AM

What about using the IBM Migration Toolkit?

Here's the page, with the download link, it's for free.






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download