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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now