Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Question SQL File Export

More
22 Sep 2016 13:23 - 22 Sep 2016 13:25 #1 by Prashant.Sangle
SQL File Export was created by Prashant.Sangle
Hi,

Is it possible to export sql query output to excel file using SQLFileExport action?

I am able to export in csv file but not able export in .xlsx file

Regards,
Prashant
Last edit: 22 Sep 2016 13:25 by Prashant.Sangle.
More
23 Sep 2016 17:53 #2 by Thomas BLETON
Replied by Thomas BLETON on topic SQL File Export
Hi
It is not possible to produce xlsx files directly with SQLFileExport.
For writing to "xlsx" Excel files you need to use an Excel metadata.
Why not setting up a mapping:
- with a "Query" as a source
- with your excel sheet as a target
More
18 Apr 2017 11:28 #3 by Prashant.Sangle
Replied by Prashant.Sangle on topic SQL File Export
Hello Sir,

Thanks for solution. It works but it could be better if we get send to excel in sql file export action.

Regards,
Prashant Sangle
More
21 Apr 2017 17:20 #4 by Thomas BLETON
Replied by Thomas BLETON on topic SQL File Export
Actually you can create Excel files, just name the out file "export.xls" and it will open in Excel (adjust the field separator so that it opens nicely in excel, iI can't say which one will work for you, it depends on Excel's local settings).
I you absolutely need "xlsx" extension, then you can't use SqlFileExport : this action exports data to text files, not to binary files.
If you feel that xlsx creation with SqlFileExport is a must-have feature, feel free to submit a feedback :)
More
26 Apr 2017 08:29 #5 by Prashant.Sangle
Replied by Prashant.Sangle on topic SQL File Export
Hello,

Thanks for trick. Actually I have already tried that trick but it fail because of one field in my table named "remark" where data having lots of special character.
So that I cant not figure it out which field separator that I can use?

Regards,
Prashant
More
26 Apr 2017 11:39 #6 by Thomas BLETON
Replied by Thomas BLETON on topic SQL File Export
Which characters are in your data source?

I did a test with a source containing line breaks, quotes, tabs, apostrophies, commas, and it opens well in Excel with these settings:
- Sql Exp Field Sep: \t
- Sql Exp Row Sep: \r\n
- Sql Exp String Delimiter: "