The data I have are in separate text files as csv's.
A csv cannot have multiple worksheets in it.
I'm adding all the csv tables (4 in total) and making it one combined source.
Then I add a separate new table a try to left join the one combined source to the new table.
The result is it only joins with one csv table because the added csv files won't combine into one source.
I'm adding the files as follows:
File -> New
Connect to data: Text File -> point to first .csv -> OK -> Import All Data -> Save Extract
Right click on Extract -> Add Data From File ... -> Choose 2nd txt file. Should get "x rows successfully added."
(repeat as needed, but try adding 2 files and see what happens)
Data -> Connect to Data... -> Text File -> point to join file -> Ok -> Import all Data -> Save Extract
Attached is an example. If I left join the book1 with prod_info across prod and product....it cuts off the dates it joins to only october and not oct-dec.
Here is a custom sql that works on my computer. I had some localization issues, which were solved by creating a schema.ini file in the same folder.
.
SELECT d.date,
d.store,
d.qty,
d.product,
p.height,
p.length,
p.width,
p.[case qty]
FROM (
SELECT [date],
[store],
[qty],
[product]
FROM [Book1#csv]
UNION ALL
SELECT [date],
[store],
[qty],
[product]
FROM [Book2#csv]
UNION ALL
SELECT [date],
[store],
[qty],
[product]
FROM [Book3#csv]
) d
LEFT JOIN [prod_info#csv] p ON d.product = p.prod
.