Merger two csv files as two seperate worksheet

sam334 · Post by **sam334** » Fri Sep 18, 2015 10:41 pm

All, have a question. I have two jobs for two separate CSV output. Now I need to merge them into single CSV file with two different worksheet. Can we achieve this in Datastage?? Appreciate for any other suggestions.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 19, 2015 1:04 am

Not enough information, though the answer is probably yes. Are the metadata of the two CSV files identical? What does the resultant CSV file have to look like in terms of its columns? What has "worksheet" got to do with anything?

chulett · Post by **chulett** » Sat Sep 19, 2015 7:50 am

Meaning Excel is the ultimate target, I assume. Since you are on Windows, investigate the use of the Excel ODBC driver to see if this is possible.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 19, 2015 4:06 pm

Never assume.

The stated requirement is to merge two CSV files into one CSV file. That can be done easily with DataStage once the rules for merging are comprehended.

But why was "worksheet" mentioned in this case?

sam334 · Post by **sam334** » Sat Sep 19, 2015 10:12 pm

Ray and Craig, thanks for the reply. Let me go through the data again. I initially thought two files have complete separate data but seems like one key column is common. Then merger would definitely work.

Craig, Initial requirement was excel output. But 11x onwards we have excel output in unstructured stage. We have 9.1. Any helpful link on ODBC excel output would be great.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 19, 2015 10:16 pm

Given that you're running on Windows you should be able to use the Microsoft ODBC driver for Excel to write to an existing (even if empty) worksheet. Alternatively write to a CSV file that can be opened using Excel.

With a common key it should be a simple join. Start with a full outer join so that you can determine which rows exist in both CSV files and which in one only. Refine your design as appropriate.

chulett · Post by **chulett** » Sun Sep 20, 2015 7:38 am

<cough>

You'll never be able to approach this with a csv target if you really need the two files merged into one file with 'two different worksheets'. That doesn't mean you couldn't create two different csv files and then macro up something in Excel to bring them in. I've seen discussions on this and also where HTML stored with an XLS extension can work as well. Plenty of topics out there on reading Excel but I couldn't find anything on Excel as a target that does more than just mention ODBC as an option. You may want to contact support to see what documentation they may have... or see if anyone here has done it.

Vincent in the linked post above does mention writing to two 'tables' via ODBC to create one spreadsheet with two worksheets. You might want to just give that a shot, see what happens.