CVS file Convertion to access, unique situation.

[BPB6]

I have a POS program on an ipad that has very little reporting capabilities but allows to export a "detailed sales" cvs file. I have been importing the cvs file into Access allowing Access to define a primary key. I do this to generate reports and manipulate the data. I cannot create a primary key with the info in the cvs file because there is not one field that has a unique number. So far it has worked great with one exception. I am only able to export the data by date ranges. I can export from the ipad app using one day as my range, however, if I want to export the file more than one time a day I cannot figure out how to import the data with out duplicating the information. Since Access assigns the primary key all of the same information for that date range is imported but under a new primary key creating duplicate data. I will need to constantly be updating the access database with new orders that come in from the ipad app. Making a pos in access is not an option. 1.) I need to use the ipad app for other non access related options and 2) I cannot afford a POS program that integrates the ipad with more comprehensive reporting features. The ipad app does an awesome job collectiong the data, but as previously mentioned is lacking in how the data is reported as well as exported. Any ideas? One last thing, I do have import specifications that only import the fields I have designated (too many fields I do not need otherwise).


Thank you!

[June7]

You are using the import wizard or TransferText method in VBA?

Third alternative is open the text file as an object in VBA and read in each line of the text file, parse the line into distinct values, and write to table. Review https://www.accessforums.net/program...ing-28711.html

This will allow you to control the pk/fk values saved with each record.

[pbaldy]

Is there some combination of fields that would provide uniqueness?

[BPB6]

Unfortunately no, there is a name, date, item number, item description, quantity and invoice field. All fields can be used multiplied times in the database.

[June7]

So can there be the same item number more than once on the same date for the same name? If not, then that would be a unique compound ID. Does date have time component?

[BPB6]

So can there be the same item number more than once on the same date for the same name? If not, then that would be a unique compound ID. Does date have time component?

Yes there can be more than one item number on the same date with the same name. There is a time component but I believe it still could be the same time as it records only by minutes and not seconds, therefore allowing the possibility of the same invoice number with the same date and time with different item. Any other thought?

[June7]

Multiple same item in the same minute for the same name?

Only option I see is VBA coded import as per reference I provided.

[BPB6]

Multiple same item in the same minute for the same name?

Only option I see is VBA coded import as per reference I provided.

Thank you for input, but I am not a programmer and unfortunately this is above my comprehension. Any other thought are greatly appreciated.

Thanks

[ssanfu]

Can you post a export CSV file with sensitive data munged?? Maybe seeing the other fields we might be able to come up with something.

[BPB6]

Can you post a export CSV file with sensitive data munged?? Maybe seeing the other fields we might be able to come up with something.

The phone numbers with the names have been covered, but that is how the cvs is uploaded, the Customer name and phone number are in one field. I have no control over it. I left all of the field that are in the cvs. When I upload the file to my database, I only bring in the field that have information in them. I looked at making one of the other fields a primary field but just like the other fields there is the possibility of having the same data for more than one entry.
Thank you for all who are thinking about this.

Test Database.zip

[ssanfu]

You didn't say how you were importing the CSV file (manually or VBA) but this might work:

"DATA" table is the table that you store the imported "detailed sales"
"TEMP" is the table to temporarily import the sales records.

1) Click a button to start the import process
2) Import the CSV file to a TEMP table
3) Get the max date & time in the database from the DATA table.
4) In the TEMP table, delete any dates/times that is earlier than the max date/time

The DATA table max date/time is "10/2/2012 9:12 PM". In the TEMP table (CSV data), delete any date/time that is "10/2/2012 9:12 PM" or earlier.

5) Execute an append query to move the remaining data from the TEMP table to the DATA table.


So you are checking for the latest (max) date/time from the last import, deleting any date/time before that max date/time, and appending the remaining data (records).

[BPB6]

ssanfu,
Thank you for the solution. I am importing manually. This definitely works and so far is the best solution for a non programmer such as myself. Unless any other ideas come about this will probably be the path I take.
Thanks again!