Free Excel-to-Text converter
-
- Regular Participant
- Posts: 197
- Joined: Thu Dec 03, 2009 8:47 am
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2.x
- Excel Version: 2010
- Location: Singapore
Free Excel-to-Text converter
Guys,
. Again.
Just happened to need a utility, that my TM1 can use to convert Excel files into Text files. Is there anyone who would want to share.
We used one before, that was from my prev company. And ommits decimal places.
Anyone who'd like to share.
Thanks very much,
Bunchukokoy
. Again.
Just happened to need a utility, that my TM1 can use to convert Excel files into Text files. Is there anyone who would want to share.
We used one before, that was from my prev company. And ommits decimal places.
Anyone who'd like to share.
Thanks very much,
Bunchukokoy
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Free Excel-to-Text converter
File -> Save As -> Text (Tab delimited) (*.txt)
Is that what you're looking for?
Is that what you're looking for?
Robin Mackenzie
-
- Regular Participant
- Posts: 197
- Joined: Thu Dec 03, 2009 8:47 am
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2.x
- Excel Version: 2010
- Location: Singapore
Re: Free Excel-to-Text converter
Yes Mackenzie. But I'm looking for a VB script or a like, which can be executed by a TI.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Free Excel-to-Text converter
Code: Select all
Option Explicit
'variables
Dim objXl, objWb
'automate excel
Set objXl = CreateObject("Excel.Application")
'open and save as text
Set objWb = objXl.Workbooks.Open("D:\DVD List.xlsx")
objWb.SaveAs "D:\DVD List.txt", -4158
'close wb
objWb.Close (False)
'quit excel
objXl.Quit
'clean up
Set objWb = Nothing
Set objXl = Nothing
Robin Mackenzie
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
If you want to use With...End With construct instead of (object) variables:
Code: Select all
With CreateObject("Excel.Application")
With .Workbooks.Open("D:\file.xlsx")
.SaveAs "D:\file.txt", -4158
.Close 0
End With
.Quit
End With
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Free Excel-to-Text converter
Wim - good suggestion. So the complete script would read:Wim Gielis wrote:If you want to use With...End With construct instead of (object) variables:
Code: Select all
Option Explicit
'constant for text file
Const xlCurrentPlatformText = -4158
'variables
Dim strInputExcelFile, strOutputTextFile
'retrieve and validate inputs
With WScript
If .Arguments.Count <> 2 Then
.Echo "Missing arguments"
.Quit
Else
strInputExcelFile = .Arguments(0)
strOutputTextFile = .Arguments(1)
End If
End With
'perform save as tab delimited text
With CreateObject("Excel.Application")
With .Workbooks.Open(strInputExcelFile)
.SaveAs strOutputTextFile, xlCurrentPlatformText
.Close 0
End With
.Quit
End With
Code: Select all
sCommand = 'cscript.exe "c:\yourscripts\convert.vbs" "c:\yourexcel\sheet.xlsx" "c:\yourtext\converted.txt"';
ExecuteCommand ( sCommand, 1 );
Robin Mackenzie
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
Robin, that's neat code, thanks !
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Regular Participant
- Posts: 197
- Joined: Thu Dec 03, 2009 8:47 am
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2.x
- Excel Version: 2010
- Location: Singapore
Re: Free Excel-to-Text converter
Mackenzie and Wim, thank you very much for all the replies. I really appreciate your generosity.
- PowerDim
- Posts: 13
- Joined: Thu Aug 07, 2014 12:44 pm
- OLAP Product: TM1
- Version: PAL 2x
- Excel Version: Excel 2019
- Location: Earth
Re: Free Excel-to-Text converter
Mackenzie, Wim, it's really works! Thank you!
Could you help with one more thing?
Is it possible to add such lines of the script so that the cell format is cleared before saving the file?
Could you help with one more thing?
Is it possible to add such lines of the script so that the cell format is cleared before saving the file?
Best regards,
Dmitry
Dmitry
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
Did you try this yourself for 5 minutes in the macro recorder of Excel VBA, to generate the syntax ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
For example, see line 2:
Code: Select all
With .Workbooks.Open(strInputExcelFile)
.Worksheets(1).UsedRange.NumberFormat = "General"
.SaveAs strOutputTextFile, xlCurrentPlatformText
.Close 0
End With
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- PowerDim
- Posts: 13
- Joined: Thu Aug 07, 2014 12:44 pm
- OLAP Product: TM1
- Version: PAL 2x
- Excel Version: Excel 2019
- Location: Earth
Re: Free Excel-to-Text converter
Wim, thank you so much!
Sorry, I followed the difficult path and completely forgot about the simple)
Everything works!
While I was setting up the script, I got the idea to abandon this script file and write everything through the ti-process? Is it possible to run a script using ExecuteCommands without calling the vbs-file? In this case, TI-process will be self-sufficient and not depend on the presence of a file with a script.
Perhaps you have any ideas?
Best regards,
Dmitry
Dmitry
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
Then I would suggest AsciiOutput in the Prolog tab to a text file and execute it in the Epilog tab.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- PowerDim
- Posts: 13
- Joined: Thu Aug 07, 2014 12:44 pm
- OLAP Product: TM1
- Version: PAL 2x
- Excel Version: Excel 2019
- Location: Earth
Re: Free Excel-to-Text converter
Yes, that's a good idea.Wim Gielis wrote: ↑Thu Sep 10, 2020 4:01 pm Then I would suggest AsciiOutput in the Prolog tab to a text file and execute it in the Epilog tab.
But the file with VBscript is unloaded with the text in quotes.
I tried closing quotes both at the beginning and at the end. Unfortunately, such a file does not work.
Script, which not working:
Code: Select all
""
Option Explicit
Const xlCurrentPlatformText = -4158
Dim strInputExcelFile, strOutputTextFile
With WScript
If .Arguments.Count <> 2 Then
.Echo "Missing_parameters"
.Quit
Else
strInputExcelFile = .Arguments(0)
strOutputTextFile = .Arguments(1)
End If
End With
With CreateObject("Excel.Application")
With .Workbooks.Open(strInputExcelFile)
.Worksheets(1).UsedRange.NumberFormat = "General"
.SaveAs strOutputTextFile, xlCurrentPlatformText
.Close 0
End With
.Quit
End With
""
TI-code:
Code: Select all
sScript = '
"
' | Char(10) | '
Option Explicit
' | Char(10) | '
Const xlCurrentPlatformText = -4158
' | Char(10) | '
Dim strInputExcelFile, strOutputTextFile
' | Char(10) | '
With WScript
' | Char(10) | '
If .Arguments.Count <> 2 Then
' | Char(10) | '
.Echo "Missing_parameters"
' | Char(10) | '
.Quit
' | Char(10) | '
Else
' | Char(10) | '
strInputExcelFile = .Arguments(0)
' | Char(10) | '
strOutputTextFile = .Arguments(1)
' | Char(10) | '
End If
' | Char(10) | '
End With
' | Char(10) | '
With CreateObject("Excel.Application")
' | Char(10) | '
With .Workbooks.Open(strInputExcelFile)
' | Char(10) | '
.Worksheets(1).UsedRange.NumberFormat = "General"
' | Char(10) | '
.SaveAs strOutputTextFile, xlCurrentPlatformText
' | Char(10) | '
.Close 0
' | Char(10) | '
End With
' | Char(10) | '
.Quit
' | Char(10) | '
End With
' | Char(10) | '
"
';
ASCIIOutput(csScriptPath, sScript);
Best regards,
Dmitry
Dmitry
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
I would do AsciiOutput for every line, rather than 1 ridicously long string with difficult concatenations.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Free Excel-to-Text converter
I agree. I usually do something like this. It's clean and easily reusable.Wim Gielis wrote: ↑Thu Sep 10, 2020 6:16 pm I would do AsciiOutput for every line, rather than 1 ridicously long string with difficult concatenations.
Code: Select all
#===============================================================================================
# Define lines
#===============================================================================================
sLine1 = 'This is the first line of your output';
sLine2 = '';
sLine3 = 'This is the third line';
sLine4 = '';
sLine5 = 'and so on !';
sLine6 = '';
sLine7 = '';
sLine8 = '';
sLine9 = '';
sLine10 = '';
sLine11 = '';
sLine12 = '';
sLine13 = '';
sLine14 = '';
sLine15 = '';
sLine16 = '';
sLine17 = '';
sLine18 = '';
sLine19 = '';
sLine20 = '';
#===============================================================================================
# Use a loop to generate the AsciiOutput (Change nMax if needed)
#===============================================================================================
k = 1;
nMax = 20;
sStaticString = 'sLine';
While( k <= nMax );
sCurrentLine = Expand( '%' | sStaticString | NumberToString( k ) | '%' );
AsciiOutput( sFileName, sCurrentLine );
k = k + 1;
END;
Code: Select all
DatasourceASCIIQuoteCharacter = '';
Last edited by Emixam on Thu Sep 10, 2020 6:54 pm, edited 2 times in total.
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Free Excel-to-Text converter
I don't fancy numbering lines, but I do understand for the loop and Expand to work.
Rather, I would just repeat the AsciiOutput and wrap the text within such a function call.
Rather, I would just repeat the AsciiOutput and wrap the text within such a function call.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Free Excel-to-Text converter
Hi
I think I hit a similar problem with the quotes recently.
I traced the problem to the fact that when you output a string from textoutput/asciioutput it will always enclose that string in double quotes so if you put double quotes around every bit of text it doesn't work. The answer is just to omit the first and last double quote and let the asciioutput put them in
Regards
Paul Simon
I think I hit a similar problem with the quotes recently.
I traced the problem to the fact that when you output a string from textoutput/asciioutput it will always enclose that string in double quotes so if you put double quotes around every bit of text it doesn't work. The answer is just to omit the first and last double quote and let the asciioutput put them in
Regards
Paul Simon
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Free Excel-to-Text converter
Or just set the quote character to empty string.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- PowerDim
- Posts: 13
- Joined: Thu Aug 07, 2014 12:44 pm
- OLAP Product: TM1
- Version: PAL 2x
- Excel Version: Excel 2019
- Location: Earth
Re: Free Excel-to-Text converter
Dear community, thank you so much for your help!
As a result, I got the following TI code and it works fine for me at this moment.
This code will be supplemented in the part of overriding the source files for processing, but the main point is below.
Maybe later I can update this post and add a more complete version of this TI-process.
SOURCE - None
PARAMETERS - None
PROLOG
EPILOG
As a result, I got the following TI code and it works fine for me at this moment.
This code will be supplemented in the part of overriding the source files for processing, but the main point is below.
Maybe later I can update this post and add a more complete version of this TI-process.
SOURCE - None
PARAMETERS - None
PROLOG
Code: Select all
#*** Prolog: Start
#<...>
#<description etc.>
#<...>
### Constants
#----------------------------------------------------------------------------------------------#
csFolder = <...YOUR_FOLDER...>;
csScriptAppPath = 'C:\Windows\System32\cscript.exe';
csScriptName = 'ConvertExcelToTXT.vbs';
csScriptPath = csFolder | csScriptName;
### Config ASCIIOutput (for removing quotes from ascii-file with script)
#----------------------------------------------------------------------------------------------#
DatasourceASCIIQuoteCharacter = '';
### Create VB-Script (*.vbs)
#----------------------------------------------------------------------------------------------#
# Define lines
sLine1 = 'Option Explicit';
sLine2 = 'Const xlCurrentPlatformText = -4158';
sLine3 = 'Dim strInputExcelFile, strOutputTextFile';
sLine4 = 'With WScript';
sLine5 = ' If .Arguments.Count <> 2 Then';
sLine6 = ' .Echo "Missing_parameters"';
sLine7 = ' .Quit';
sLine8 = ' Else';
sLine9 = ' strInputExcelFile = .Arguments(0)';
sLine10 = ' strOutputTextFile = .Arguments(1)';
sLine11 = ' End If';
sLine12 = 'End With';
sLine13 = 'With CreateObject("Excel.Application")';
sLine14 = ' With .Workbooks.Open(strInputExcelFile)';
sLine15 = ' .Worksheets(1).UsedRange.NumberFormat = "General"';
sLine16 = ' .SaveAs strOutputTextFile, xlCurrentPlatformText';
sLine17 = ' .Close 0';
sLine18 = ' End With';
sLine19 = ' .Quit';
sLine20 = 'End With';
# Generate the ASCIIOutput (check "nMaxLines")
i = 1;
nMaxLines = 20;
sLineCaption = 'sLine';
While( i <= nMaxLines );
sCurrentLine = Expand( '%' | sLineCaption | NumberToString( i ) | '%' );
ASCIIOutput(csScriptPath, sCurrentLine);
i = i + 1;
END;
#*** Prolog: End
Code: Select all
#*** Epilog: Start
sFileName = <...YOUR_FILE...>;
sFileFormat = '.xlsx';
sFullFileName = sFileName | sFileFormat;
sFilePath = csFolder | sFullFileName;
sExportFilePostfix = '_Exported';
sExportFileName = sFileName | sExportFilePostfix;
sExportFileFormat = '.txt';
sExportFullFileName = sExportFileName | sExportFileFormat;
sExportFilePath = csFolder | sExportFullFileName;
sCommand = csScriptAppPath | ' "'| csScriptPath | '" "'| sFilePath |'" "' | sExportFilePath |'"';
ExecuteCommand(sCommand, 1);
ASCIIDelete(csScriptPath);
#*** Epilog: End
Best regards,
Dmitry
Dmitry