| Author |
Message
|
| avinvm16 |
 Posted: Thu Jul 21, 2016 12:32 am Post subject: need to data from bunch of xlsx and tranform the datato text |
 |
|
Newbie
Joined: 21 Jul 2016
Posts: 1
|
Hi Friends,
I have a requirement described below can any one help me getting solution to this using Message broker v9.0.0.0
1. There are some n no. of xlsx files which has data in one particular coulmn of the xlsx which will be the last column in the xlsx.
2. I need to read that particular data along with this we also need one more column which is second column in the xlsx which describes the data.
3. I need to read each and every file in the folder and then I need to transfrom the data of each xlsx in to one row of pipe delimited info and store in two different files with each row having data of each xlsx
4. I have specific mapping for creating the two text files which mean some rows of the data will go into one file and some in other file
5. both the text files should be in sync which mean once you read one xlsx you should append the data in to two text files.
please suggest me guide me through the implementation part. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jul 21, 2016 3:54 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
How do you think it can be done?
What have you tried?
If so what went wrong?
Are you sure that you can read .xslx files successfully (and parse them)?
That is worth trying before you go in too deep.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jul 21, 2016 3:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need to create DFDL models to match your text files.
You need to construct a message flow to read your input data, perform the mapping into structures defined by the DFDL, and then output the message.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jul 21, 2016 4:41 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
You need to create DFDL models to match your text files.
You need to construct a message flow to read your input data, perform the mapping into structures defined by the DFDL, and then output the message. |
You especially need to work out how you're going to read an xlsx file with a non-Microsoft product that doesn't have out of the box access to the xlsx file format.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Jul 21, 2016 5:11 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqjeff wrote: |
You need to create DFDL models to match your text files.
You need to construct a message flow to read your input data, perform the mapping into structures defined by the DFDL, and then output the message. |
Er.... but.... Isn't .xslx supposed to be an XML file (Cough... Cough)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jul 21, 2016 5:19 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
| mqjeff wrote: |
You need to create DFDL models to match your text files.
You need to construct a message flow to read your input data, perform the mapping into structures defined by the DFDL, and then output the message. |
Er.... but.... Isn't .xslx supposed to be an XML file (Cough... Cough) |
Yes, so the OP needs to work out if the files to be processed as XML through a FileInput node (navigating the message tree as required through the slightly torturous layout to locate the data the OP mentions) or if the file is better processed with a .NET node that can leverage MSoft technologies to provide a more spreadsheet-like view for the OP.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jul 21, 2016 6:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Or convince the senders to send a CSV file instead of an xmlx...
Which requires another DFDL...
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jul 21, 2016 8:16 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| smdavies99 wrote: |
Er.... but.... Isn't .xslx supposed to be an XML file (Cough... Cough) |
 Have you tried opening an xslx file with anything but MS Excel?
Bunch of crap in there...
I managed by doing a saveas and finding an XML format in the list there.
And then I still had to go and replace & with & to make it viable for the browser... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jul 21, 2016 8:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| fjb_saper wrote: |
| Bunch of crap in there... |
I went with "slightly tortuous" but I accept your point.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
