Concatenate multiple databases into a single database

[Alexis Podevin]

Hello to the community,

One of our customers has placed an order with us (not on ustore or anything, these are simple business-to-business orders). Each time the customer places an order with us, we create a database corresponding to that order. However, as customers place orders with us several times a month, we end up with several databases (for example, for this month we have 25 databases).

To generate the proofs for our customers, we have to import the databases one by one into uProduce, as the customer wants to see the orders separately.

However, when it comes to production files, we'd like to group all orders in a single database, i.e. have just one database containing all order data.

For example, if the customer sends us 4 orders, we'll have :
Order_1.txt
Order_2.txt
Order_3.txt
Order_4.txt

This method works well for us for proofs, but for production, we'd like to have a single database, e.g. :
source.txt (containing the 4 orders)

Thanks in advance,

[west-digital.fr]

Hello,

>> we create a database

>> we end up with several databases

>> we have 25 databases

Are they really "databases", as you wrote?

Or do you actually mean "uProduce hosted data sources" instead, i.e. "tables stored in the XMPDBHDS... database"?

In my knowledge, the only out-of-the-box tool available in uProduce to so-called "concatenate data" is the "Records > Add" tool (from inside an existing Data Source"); but it turns out that it can only append data taken from a CSV file (one file at a time), which is maybe not the best in your case:

On the opposite, maybe a SQL "UNION" clause (used either in a Plan Filter or in a SQL query, manually specified in the uProduce Dashboard "Recipients" pane) would be better in your case?

west-digital.fr

[Alexis Podevin]

Hello,

Yes, they really are databases, they are not hosted by uProduce, before our intervention.

This is not the best solution, since in our case our files are in .txt format...
 
Concerning the SQL clause "UNION", the procedure is as follows:

SELECT * FROM Order1
UNION
SELECT * FROM Order 2
UNION
SELECT * FROM Order 3
....

Or am I on the wrong track?

[Amit Cohen]

if as you say your databases are actually a bunch of comma-delimited text files, just search Google "merge csv files", and you'll get hundreds of solutions.

for example, one I found googling:  Merge CSV files Online (extendsclass.com)

[Alexis Podevin]

As these are customer databases with a lot of information, we don't want to go through a merge on google.

For security reasons.

[Jerry Clough]

I have several clients that I do something similar for. I use a macro created with Visual Basic in Excel to combine all the files into one. Mostly, I set the code to grab all the files in a certain folder and combine them. This might be what Mr Cohen is referring to, too.

[eko...@gmail.com]

I'd search Google for "combine csv powershell". 

I found a script a few years ago, fine tuned it for my needs, and it's been working without fail. It simply grabs all of the data files in a folder and combines them with only one header row. 

Or we also have a customer who uses Excel to combine a bunch of files for a monthly run.  Just search for: "Excel combine files from folder" for the steps. Just be careful using this method because Excel can automatically re-format a column depending on the contents in the data files. 

[Thomas Wäspi]

Hi, this would be a possible solution: https://www.dataqualityapps.de/

It's a toolset (Windows only), you can combine or split db's and a lot more of usefule things.

have a good day,

thomas

[Jerry Clough]

Upon further review, would the Power Query option work? I use it for something else similar, but I think it would be workable for what you want.