Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2015
    Posts
    3

    Question Hello - Advice creating a database from csv files

    Hi all,
    I've just joined the group and have a question - could you point me to the most appropriate group please?

    We're moving software from one supplier to another, and have been provided with an export of our database in cvs format - it's 52 csv files. About 8Mb in total.

    Is there a way of scanning these files to create a map of the data, as we've been provided with no schema or fields guide, and it's really confusing!


    Any advice would be very gratefully received.

    Thanks
    James

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Hopefully the CSV files will have at least the field names as the first line in the file. Most of the tools that import CSV files understand that convention and will automagically use those as column names when the tool imports the CSV file into a table for you.

    For only 8 Mb of data, you can use Microsoft Access or another desktop tool to investigate the CSV files to determine what data is in them and how it is organized/related. I think that this would be a great first step toward figuring out what you have and how you want to use it now.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2015
    Posts
    3

    Thumbs up

    Quote Originally Posted by Pat Phelan View Post
    Hopefully the CSV files will have at least the field names as the first line in the file. Most of the tools that import CSV files understand that convention and will automagically use those as column names when the tool imports the CSV file into a table for you.

    For only 8 Mb of data, you can use Microsoft Access or another desktop tool to investigate the CSV files to determine what data is in them and how it is organized/related. I think that this would be a great first step toward figuring out what you have and how you want to use it now.

    -PatP
    Thanks Pat, thanks very much for your quick reply!

    I'm actually the end user; I've provided the csv files to our new supplier (they've come from an export from the old supplier with very good column headings) and I just wondered whether Access (or something else), for example, can open multiple cvs's simultaneously and create a schema that shows the links between the individual files which can be translated into tables? This would ensure that the new supplier isn't missing an opportunity and is importing as much data as possible. I would usually leave them to it, but they told me that the data doesn't have decent date formatting - on talking to them it became apparent they'd opened and were viewing the csv with Excel, and it was displaying the date format columns in an improper way. Had they used good old notepad they'd have seen the true data.


    Thanks in advance.
    James

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Automatically, no
    What you want can be done in Access or other than I now if required. To be honest if the new supplier can't do it themselves then I'd run away from them faster than a.....
    If you don't have a copy of access then could repeat the exercise in say MySQL, but you'd have to download the server and Workbench. Import the data in Workbench and the reverse engineer the tables from the server into a diagram. If you have the time, expertise and resources do it. But if you are a 'user' without direct skills and a need to I wouldn't. If you have an IT function then they could probably do you tricky part (importing the car into a db)

    Besides which what you would be doing is documenting how the old system was designed, not how your data is modelled no how the new system should look.
    What might be worth exploring is, if you have the rights to is reverse engineer the existing design assuming you know what type of db it uses. I'm pretty certain data architect can do that for several if not many databases
    Last edited by healdem; 02-03-15 at 13:01.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    First and foremost, you would benefit a LOT from having a database professional help you with this task. I would expect that this could be done in under a day with a ROI (the cost of hiring a pro divided by the hosting cost per day that their work will save you) measured in days or weeks.

    If you bring the skills of a data professional and your skills as an SME (Subject Matter Expert) together to create a new schema, it isn't uncommon to cut the hosting cost in half compared to either a hosting company or an SME working on their own. As you've already discovered, your hosting company may be good at hosting but they don't fare so well at schema design!

    You obviously have NotePad.exe and at least a basic familiarity with data. Do you have other tools like Microsoft Access? If you don't have the time or money to get a data professional to help, we (the denizens of DBForums) can probably talk you through the process.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2015
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    First and foremost, you would benefit a LOT from having a database professional help you with this task. I would expect that this could be done in under a day with a ROI (the cost of hiring a pro divided by the hosting cost per day that their work will save you) measured in days or weeks.

    If you bring the skills of a data professional and your skills as an SME (Subject Matter Expert) together to create a new schema, it isn't uncommon to cut the hosting cost in half compared to either a hosting company or an SME working on their own. As you've already discovered, your hosting company may be good at hosting but they don't fare so well at schema design!

    You obviously have NotePad.exe and at least a basic familiarity with data. Do you have other tools like Microsoft Access? If you don't have the time or money to get a data professional to help, we (the denizens of DBForums) can probably talk you through the process.

    -PatP
    Thanks all for your fantastic support - I'm awaiting an email from the new supplier to tell me how much of the existing data can be used. I may be back to ask for advice when that happens, but will let you know. Thanks again, James

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •