Trust ID PBCEX logo 300x102

How to push from multiple spreadsheet to a single database?

10 years 6 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
Topic Author
How to push from multiple spreadsheet to a single database? #510
Question: I have a question I am going to do undertake a install of five card exchanges and five different sites and each site will be printing a total of 20 excel spreadsheets each which will contain a id which will always start at one on each spread sheet and at time of print all the information contained in the spread sheet will be pushed to a central database. How do I do this as the id will start at one for each spreadsheet and will overwrite the existing data.

Answer:There are several options that could be a solution for this problem, but what fits best depends on the data structure of both the spreadsheets and the database and of the way they want to have there data in the database.

First of all, you can consider using another field in the spreadsheet as primary key in CardExchange, for example an employee number. Please, check if there is any field that uniquely identifies a card holder in all spread sheets.

If this doesn’t work out, CardExchange also offers the possibility to always insert a new record when processing a storage item. To set this option, you have to click the Advanced options button on the Storage item page of the Data definition wizard.

[attachment=0:fqfgwx6k]<!-- ia0 -->Storage items - Advanced options.JPG<!-- ia0 -->[/attachment:fqfgwx6k]

If you select the option Always insert, what used to be the primary key in the spreadsheet will not be unique anymore in the database. Imagine that your spreadsheets has two fields: ID and Name, ID being defined as the primary key in CardExchange, and that the database table has three fields, ID, SpreadsheetID and Name, ID being an autonumbering field that has been defined as the primary key in the database. You could now define a storage item for this database table that specifies SpreadsheetID as primary key and Name as column name, together with the option Always insert. Each time you print a card, CardExchange will insert a recod in the database table, filling the SpreadsheetID and Name columns with the values from the spreadsheet. The database itself will generate a unique value for ID. With this options, printing a record twice for the same person means that that person will be twice in the database.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)


The forum is in read only mode.