xcaliber13

Importing text into excel

9 posts in this topic

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

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?
 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Another approach:

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Glad to be of service :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now