Free Excel-to-Text converter

[bunchukokoy]

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

[rmackenzie]

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

Is that what you're looking for?

[bunchukokoy]

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

[rmackenzie]

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

[Wim Gielis]

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

[rmackenzie]

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 );

[Wim Gielis]

Robin, that's neat code, thanks !

[bunchukokoy]

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

[PowerDim]

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?

[Wim Gielis]

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 ?

[Wim Gielis]

For example, see line 2:

Code: Select all

  With .Workbooks.Open(strInputExcelFile)
    .Worksheets(1).UsedRange.NumberFormat = "General"
    .SaveAs strOutputTextFile, xlCurrentPlatformText
    .Close 0
  End With

[PowerDim]

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?

[Wim Gielis]

Then I would suggest AsciiOutput in the Prolog tab to a text file and execute it in the Epilog tab.

[PowerDim]

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?

[Wim Gielis]

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

[Emixam]

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 = '';

[Wim Gielis]

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.

[paulsimon]

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]

Or just set the quote character to empty string.

[PowerDim]

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