Free Excel-to-Text converter

bunchukokoy
Regular Participant
Posts: 195
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

Post by bunchukokoy » Mon May 20, 2013 9:48 am

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. :mrgreen:


Thanks very much,

Bunchukokoy

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Free Excel-to-Text converter

Post by rmackenzie » Mon May 20, 2013 10:26 am

File -> Save As -> Text (Tab delimited) (*.txt)

Is that what you're looking for?
Robin Mackenzie

bunchukokoy
Regular Participant
Posts: 195
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

Post by bunchukokoy » Mon May 20, 2013 10:29 am

Yes Mackenzie. But I'm looking for a VB script or a like, which can be executed by a TI.

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Free Excel-to-Text converter

Post by rmackenzie » Mon May 20, 2013 11:05 am

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

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Mon May 20, 2013 12:09 pm

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Free Excel-to-Text converter

Post by rmackenzie » Tue May 21, 2013 3:27 am

Wim Gielis wrote:If you want to use With...End With construct instead of (object) variables:
Wim - good suggestion. So the complete script would read:

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
And can be called from TI by:

Code: Select all

sCommand = 'cscript.exe "c:\yourscripts\convert.vbs" "c:\yourexcel\sheet.xlsx" "c:\yourtext\converted.txt"';
ExecuteCommand ( sCommand, 1 );
Robin Mackenzie

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Tue May 21, 2013 7:22 am

Robin, that's neat code, thanks !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

bunchukokoy
Regular Participant
Posts: 195
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

Post by bunchukokoy » Tue May 21, 2013 3:11 pm

Mackenzie and Wim, thank you very much for all the replies. I really appreciate your generosity. :)

User avatar
PowerDim
Posts: 11
Joined: Thu Aug 07, 2014 12:44 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: Excel 2016
Location: Moscow, Russia

Re: Free Excel-to-Text converter

Post by PowerDim » Thu Sep 10, 2020 2:48 pm

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?
Best regards,
Dmitry

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Thu Sep 10, 2020 3:00 pm

PowerDim wrote:
Thu Sep 10, 2020 2:48 pm
Is it possible to add such lines of the script so that the cell format is cleared before saving the file?
Did you try this yourself for 5 minutes in the macro recorder of Excel VBA, to generate the syntax ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Thu Sep 10, 2020 3:01 pm

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
PowerDim
Posts: 11
Joined: Thu Aug 07, 2014 12:44 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: Excel 2016
Location: Moscow, Russia

Re: Free Excel-to-Text converter

Post by PowerDim » Thu Sep 10, 2020 3:46 pm

Wim Gielis wrote:
Thu Sep 10, 2020 3:01 pm
For example, see line 2:
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

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
PowerDim
Posts: 11
Joined: Thu Aug 07, 2014 12:44 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: Excel 2016
Location: Moscow, Russia

Re: Free Excel-to-Text converter

Post by PowerDim » Thu Sep 10, 2020 6:09 pm

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.
Yes, that's a good idea.
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
""
When I manually remove the quotes or use the prepared file with this script, it works fine.

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);
Maybe you have an opportunity to suggest what can be fixed in this situation?
Best regards,
Dmitry

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Thu Sep 10, 2020 6:16 pm

I would do AsciiOutput for every line, rather than 1 ridicously long string with difficult concatenations.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Emixam
Posts: 76
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

Post by Emixam » Thu Sep 10, 2020 6:36 pm

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.
I agree. I usually do something like this. It's clean and easily reusable.

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;
Also, you might want to use DatasourceASCIIQuoteCharacter in your TI:

Code: Select all

DatasourceASCIIQuoteCharacter = '';
Last edited by Emixam on Thu Sep 10, 2020 6:54 pm, edited 2 times in total.
Sorry for my English :)

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Free Excel-to-Text converter

Post by Wim Gielis » Thu Sep 10, 2020 6:39 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
paulsimon
MVP
Posts: 763
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

Post by paulsimon » Thu Sep 10, 2020 8:34 pm

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

lotsaram
MVP
Posts: 3429
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

Post by lotsaram » Thu Sep 10, 2020 9:06 pm

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.

User avatar
PowerDim
Posts: 11
Joined: Thu Aug 07, 2014 12:44 pm
OLAP Product: TM1
Version: PA 2.0.8
Excel Version: Excel 2016
Location: Moscow, Russia

Re: Free Excel-to-Text converter

Post by PowerDim » Thu Sep 10, 2020 10:05 pm

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

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
EPILOG

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

Post Reply