Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Importing Data from CSV to multiple Tables

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.
1 Solution
shdwmageAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now