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:
When imported this data need to be split into 3 different tables.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.