Solved

Importing Data from CSV to multiple Tables

Posted on 2013-02-06
1
383 Views
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:
Stock
Year
Make
Model
Vin
Date In
Color
BodyStyle
Source
Odometer
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.
0
Comment
Question by:shdwmage
1 Comment
 
LVL 2

Accepted Solution

by:
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql ODBC Drivers for Mac 2 948
FileMakerPro 10 relationships 11 219
FilemakerPro 10 archiveing 2 46
My .JAR file will not open. What can I use to view my images? 2 55
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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