Importing Data from CSV to multiple Tables

Posted on 2013-02-06
Last Modified: 2013-02-06
Good Morning Experts,

I have been wracking my brain for awhile on this now and have come up with some solutions that work, but aren't really doing everything that I want them to.

I work for a chain of used car dealerships and we are in need of a database that centralizes quite a bit of custom information we want to be able to keep track of because of changes to various state laws and rules that have made business kind of a pain in the butt.

We currently have 2 pieces of software that track a very specific set of information, but don't track a lot of other information that has nothing to do with either process. The problem comes to tracking all of the information that the office staff uses that isn't in "accounting."  We currently have 20+ spreadsheets that track all of the office junk but with tons of redundant data and work.  Each one of these spreadsheets has a very specific purpose but would be much better if they were all tied together.

I have most of the structure of the database put together, my problem is getting the data into that structure.

I need the import the following fields from our accounting software:
Date In
Options 1 through 10

When imported this data need to be split into 3 different tables.
The basic vehicle information that will stay the same no matter where it is moved. (year, make, model, vin, etc)
The additional information table that stores data that can change. (stock, mileage, price, etc)
The floorplan table that ties the vehicle and the floorplan together for tracking purposes. Allowing for a many-to-many relationship.

The problem comes down to the VIN and Stock Number are not good unique vehicle identifiers. Because of our multiple store status the same vehicle can be moved from dealership to dealership giving it a new stock number. Additionally we may take that same vehicle in on trade at a later date and what a whole new set of information that is completely separate from the last time it was in the database.

I can do a lookup to see if the vehicle is already in the database. However this won't create a new unique identifier for the vehicle if it is not already in the database nor will it know if the vehicle has been sold before.

My thoughts on this method would be that it allows me to do multiple import scripts from that table into the correct data tables thereby reducing the amount of time that it takes the script to run.  

I have successfully scripted manually finding, checking, and adding if necessary but the script takes a long time to run. Lots of layout switching, storing to variables, etc, etc, etc.

You get the idea.

Any help would be greatly appreciated. I'm apparently stuck in the box on this one.
Question by:shdwmage
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment

Accepted Solution

shdwmage earned 0 total points
ID: 38861610
Well I figured it out finally. After posting all the information here it forced me to rehash the entire thought process and merge the two thoughts together.

Here is what I did to resolve the issue, just in case someone comes across a similar issue and are looking for the answer.

Step 1 - I import the data from the CSV file. During this time a lookup is done for the id and the status of the vehicle.
Step 2 - A script runs through and looks for any "completed" vehicles. Meaning they were sold and funded.  If it is completed the script clears the content of the vehicle_id box
Step 3 - I run an import from inside the database. Moving the data from the import table to the first table of vehicle information. I put a calculation field here to check the vehicle_id against. This way if the vehicle is not currently active in the database it will be added as well as a new ID automatically generated.
Step 4 - Force a relookup based upon the VIN in the imported information table
Step 5 - Import the data to the secondary information table linking it to the primary table by this new "vehicle_id"

There may be a better way to handle it, but it's the best way I could come up, and it's quick.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question