Import Ebay CSV Sales File into Tables

[ebayer]

I'm looking to create a database to manage my ebay sales.

I've attached a csv file which shows the format of that csv file is created in. The problem for a person with no programming experience is that when more than one item is ordered this is shown on more than one line so I can't just import it.

There are essentially 3 tables involved in the import:

1) Customers
2) Orders


3) Order Details

and my guess is that any process will need to follow that order of progression ie a customer needs created first so that a customer id can be inserted into the orders table and an order entry needs to be created before the order details as the order details depends on the order id.

I'm also guessing that I will probably want to split this up into several functions as I will want to check if the customer already exists before creating a new order and as ebay customers tend to use different user ids and email addresses I will want to check a range of fields to try and determine if the customer already exists. I will probably want to make that call myself as some customers have moved addresses and the person that has moved in has bought from me. That's just the nature of the type of business I'm in.

I've purchased the Access 2010 Inside Out so looking to focus my reading in the correct areas assuming I can make any sense of it so what do I need to accomplish this task.

I've also attached my database design as it currently stands so if you have any comments on it feel free to chip in.

[orange]

Welcome.
First a comment on your model/design --seems quite thorough and thought through -- quite a rare thing for someone with one post. Congrats!
And no embedded spaces in field names!!!
The csv file is unnormalized and will require that you process it in some manner before moving the data to your tables.

The first thing I would suggest is to determine if all the fields that you have in your design actually exist in the csv file. From a documentation and learning perspective, if you list all the fields in the csv, and map (identify) where each field ends up in your tables. That will help to familiarize you with the data, your tables and the processes/manipulations involved.

Any field in your tables that is not in the incoming csv, or can't be calculated from fields in the csv, will be an issue.

Since I don't know your background, but do see designed relationships in your database, I will comment for reference, but you may already be aware that:
You can not add/insert a record into a Many table that does not have a record in the 1 table for tables in a 1 to many relationship. Said differently, you can't add an OrderDetail, if there is NO associated Order record. So this will impact the order in which you add records to tables.

I could see some sort of staging table where you enter the csv data; then a series of queries to select, manipulate data from that into a "transaction table" to be validated and applied to your "real" tables according to your rules.

I'm sure there are many approaches, but that's what I see at first glance.

Good luck.

[ebayer]

Thanks orange. I've building simple access databases for a few years however I've never needed to do anything particularly complex. In that time I've acquired a bit of knowledge on database design so I'm glad that it has proved a little useful here.

Thanks for the tip re: the database format. I've added another attachment for mdb format - couldn't see how to delete the other one.

There are some fields that aren't in the csv file however they aren't required fields so hopefully it shouldn't affect import. I'd suspected that I'd have to do things in a specific order to meet requirements of tables in a relationship but that clarifies how this is worked out.

Not sure if you are suggesting that I might be able to accomplish this without having to code anything. Is that right?

[orange]

I'm not suggesting code is required or not. A lot will depend on how you application -- the interface with forms etc.
Somethings may require code.

Good luck - be sure to tell us how things are going as your project progresses.

[ebayer]

I'm not suggesting code is required or not. A lot will depend on how you application -- the interface with forms etc.
Somethings may require code.

Good luck - be sure to tell us how things are going as your project progresses.

Ah no worries I'm sure I'll be back with lots of questions (problems).

Started wading my through that book but it's rather hefty so it could take some time however I'll start by importing the data directly into an intermediate table and see how far I can get with passing it to the other tables and then at least I have something to focus on.