Importing text into excel

[xcaliber13]

New to the forums and have been using AutoIt for a couple of months.   Here is my problem,  I have a text file that imports into excel with the correct columns if I use:

_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth)

I get the correct information into the correct number of columns that I expect. (8 columns)

The only issue that no matter what I try to do to correct is that in column 1 (first column) it is dropping the leading zero.

If I try to import any other way than FixedWidth  my data does not get into the correct columns.

The text file is not delimited other than spaces. 

Any suggestions as to get it from dropping the leading zero when imported using fixedwidth?

Thank  you

[water]

Welcome to AutoIt and the forum!

The function only imports data, no formatting. The data in column 1 is imported and then formatted by Excel using the default format. This default format does not show leading zeros.

So if you need the leading zeroa you need to change the formatting of the cells in column 1.

[xcaliber13]

Water   Thank you for the quick reply.  Yes I do understand that excel is formatting the column.  I am guessing that I did not make myself clear enough on what is my problem.   I have been researching this issue for 2 weeks.  I have tried all the different suggestions that have been in this forum. I have tried different variations of the follow code:

Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlTextFormat]
Local $aField4[2] = [4, $xlTextFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aField6[2] = [6, $xlTextFormat]
Local $aField7[2] = [7, $xlTextFormat]
Local $aField8[2] = [8, $xlTextFormat]
Local $aFieldInfo[8] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8]

_Excel_BookOpenText($oAppl, $sTextFile, Default, $xlDelimited, Default, True, "", $aFieldInfo, Default, Default)

Now this code will indeed format the columns correctly (No leading zeros dropped) but it does not import into correct columns. With the different variations I either get less columns or more columns of data going into the excel spreadsheet.

So using $xlFixedWidth I get the correct number of columns with the correct data in each column but leading zeros dropped

Using the other variations that I have tried I get more or less than the expected columns with the incorrect data in each column but no leading zeros dropped.

What the heck am I doing wrong?
 

[water]

This MSDN article describes how to format numeric data with leading zeroes.

Means you could import data as you did before and then format (even by using AutoIt) column A as needed.

[water]

Another approach:

Could you please post a signle line of your input file so we can play with it?

[xcaliber13]

Here are a few lines of the text file I am using.  Text modified so as NOT to be actual live data. But still in the same format as original file.

047182      12/01/2014 SMITH,JOE JOHN            SPINE CERVICAL, 2 OR 3 VWS      72040       JOHN JOE MD     SIGNED         65019325
047182      12/01/2014 SMITH,JOE JOHN            SPINE CERVICAL, 2 OR 3 VWS      72040       JOHN JOE MD     SIGNED         65022204
202038      12/01/2014 FELTLINK, MARIE BETH            DIGITAL MAM BILAT;W/CAD         77052       FRIDAY D. JOE,MD     SIGNED         61668406
077371      12/01/2014 JONES,ROBERT BOB JR          WRIST LEFT MIN 3 VIEWS          73110       MIKE S. SMITH,     SIGNED         65030280
077371      12/01/2014 JONES,ROBERT BOB JR          SHOULDER RIGHT COMPLETE MIN 2V  73030       MIKE S. SMITH,     SIGNED         65030280
077371      12/01/2014 JONES,ROBERT BOB JR          CT ABDOMEN W/WO CONTRAST        74170       MIKE S. SMITH,     SIGNED         65030280

Not showing here but all data is single line being with the numbers.  It is these first numbers that I am having a hard time to save the leading zero upon importing.

[water]

First try.

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $sTextFile = @ScriptDir & "\test.txt"
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth)
$oWorkbook.ActiveSheet.Range("A:A").NumberFormat = "0000000"

Formats colum A with 7 digits, adds 0s on the left where needed. Edited February 27, 2015 by water

[xcaliber13]

Water     YOU ARE THE BEST!!!!    That did exactly what I need.   Many, Many, and a thousand more THANK YOU's!!!!!!!!!!!!!!!!!

[water]

Glad to be of service