Solved

Importing Data from CSV to multiple Tables

Posted on 2013-02-06
1
376 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

20 Experts available now in Live!

Get 1:1 Help Now