Excel macro imports data containing 'E' and converts it to exponential format instead of remaining as text.

Hi All,  I'm acontaining 'E' and converts it to exponential format instead of remaining as text. part of a small engineering company that collects home energy efficiency parameters throughout our Province as part of a Federal Grant to homeowners who upgrade their homes.  We use software provided by the Government, and it in turn yields a TSV of all the information on each house - about 3/4 of a page in Notepad.   We have an Excel sheet with a macro that is supposed to import the data from the TSV, and puts them into columns that have conditional formatting that flags ( turns the background red ) on any cell that has a value outside fixed ranges.   This all worked flawlessly when all the houses had ID numbers in the form of 1510A00234, and 1510B00234.  The numberering scheme is set Federally, and the first two digits ( 15 ) represent our Province.  the next two digits represent the ID number of our Field Evaluator ( We have 22 currently testing homes ).   The character A or B represents the first evaluation(A), or the follow-up evaluation(B) after the homeowner has done upgrades to the home ( furnace, windows, insulation, etc )
The program changed gears last year and the evaluations now carry a D or E in the ID number, and the problem occurs when we feed ID's containing an E.  
  For example:  1523E00183 is converted to 1.52E+186 in column D.
I fiddled with the macro, and was able to see that it creates a new Excel workbook, imports the TSV, and copies that data into the TSV Checker.xls   The problem goes away once the house number exceeds about 320 - a house file of 1523E00340 maintains it's form of text without changing to an Exponential Value.
We can use the TSV Checker.xls to point to about 25 TSV files at once.  This exponential problem has caused about 3000 E files to hit a bottle-neck so assistance is greatly appreciated.
I'm including the TSV Checker, as well as a sample TSV file that the Gov't housing software generates.
We use the TSV Checker to make sure dozens of parameters are within expected bounds before we submit the data to Ottawa - otherwise their own 'TSV checker' kicks it back to us with 'data out of range errors'

Thanks

Tim
TSV-Checker.xls
1523E00183.txt
town_cobblerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmundsackCommented:
If you specify that column's data type as "Text" it imports without converting it to scientific notation.

When I recorded a macro while doing the import manually, this is the code it generated:

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Temp\1523E00183.txt", _
        Destination:=Range("A1"))
        .Name = "1523E00183"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I believe the .TextFileColumnDataTypes is the property that allows you to specify the data type (which is 1 for "General" which means let Excel choose, and 2 for "Text" which means always treat the value as a string).
0
town_cobblerAuthor Commented:
Can the macro be altered to import the TSV directly into the TSV Checker workbook, rather than it creating a new workbook in the background ( with all the cells formatted as General )  I've tried creating the TSV checker from scratch by first making all three sheets with cells formatted as text, and then copying over the data and macro from the original TSV Checker.xls  .   I expected this to work until I discovered that the macro was creating a new workbook in the background - pulling the TSV data into it - which means it was being imported to a sheet that had cells with General formatting - which caused the 1523E00183 to change to exponential even before it was coppied to the 'TSV Checker' workbook.  I'm not sure how to get the macro to import the TSV directly ( without opening another workbook in the background )

Thanks
0
jmundsackCommented:
The problem is in the OpenText method you need to specify the field format for all columns up to and including column 4 (the one being converted to scientific notation).  There is an optional parameter called FieldInfo which is documented as:

FieldInfo   Optional xlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip Column

You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used.

The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting.

Notes

If you specify that a column is to be skipped, you must explicitly state the type for all remaining columns or the data will not parse correctly.

If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the above date formats for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General.
This example causes the third column to be parsed as MDY (for example, 01/10/1970), the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 3), Array(1, 2))
                  
If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.


Sub EGH_data_macro()
'
' EGH_data_macro Macro
' Macro recorded 07/05/99 by Sylvain Blais
'
' Keyboard Shortcut: Ctrl+a
'
    Application.DisplayAlerts = False
    Set x = Cells(ActiveCell.Row, 1)
    D = Application.GetOpenFilename
    f = Dir(CurDir & "\*.TSV")
    Workbooks.OpenText FileName:= _
        f, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array( _
            Array(1, xlGeneralFormat), _
            Array(2, xlGeneralFormat), _
            Array(3, xlGeneralFormat), _
            Array(4, xlTextFormat) _
            )
 
    Range("A1").EntireRow.Copy
    x.PasteSpecial
    Set x = x.Offset(1, 0)
    Range("a2").EntireRow.Copy
    x.PasteSpecial
    Set x = x.Offset(1, 0)
    ActiveWorkbook.Close
    f = Dir
    Do While f <> ""
        Workbooks.OpenText FileName:= _
            f, Origin:=xlWindows, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="|", _
            FieldInfo:=Array( _
                Array(1, xlGeneralFormat), _
                Array(2, xlGeneralFormat), _
                Array(3, xlGeneralFormat), _
                Array(4, xlTextFormat) _
                )
        Range("a2").EntireRow.Copy
        x.PasteSpecial
        Set x = x.Offset(1, 0)
        ActiveWorkbook.Close
        f = Dir
    Loop
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

town_cobblerAuthor Commented:
I should clarify that I'm using Excel 97 to alter the macro - as the original one was done with 97, and most of the staff still have Office 97 on their computers.  

I tried both solutions you've offered; the first one gives me too many line returns? in the macro - probably a limitation of Excel 97 vs Excel 2003.  I added the lines you suggested in your second reply jmundsack, but for some reason it still returns an exponential - I think because it's opening the TSV with a new workbook, and that's were the conversion takes place.
0
jmundsackCommented:
That's odd.  When I ran it the field was imported correctly.  Of course I am using 2003 so maybe the FieldInfo parameter did not exist on 97?  Can you check the helpfile for 97 to see (look at the OpenText function)?

I have attached the file I tested with.

TSV-Checker.xls
0
jmundsackCommented:
Or maybe 97 does have the FieldInfo parameter but uses a slightly different syntax?

I don't have 97 otherwise I'd test it myself.
0
town_cobblerAuthor Commented:
I tried it on a box running Office 2007 and it worked great - I went back to my workstation and ran it on the Excel 97 and it yields Run-time error '1004': Method 'OpenText' of object 'Workbooks' failed, so there must be some VB language that 97 can't handle.   When I hit the Debug button, the following lines of the code are highlighted in yellow:

Workbooks.OpenText FileName:= _
        f, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array( _
            Array(1, xlGeneralFormat), _
            Array(2, xlGeneralFormat), _
            Array(3, xlGeneralFormat), _
            Array(4, xlTextFormat) _
            )

It may be the "FieldInfo" that is not understood by Excel 97

Thanks
0
byundtMechanical EngineerCommented:
Hello Tim,
According to the on-line help, all the parameters are the same in Excel 97--but the mnemonics (integer constants) are different. You might try the following tweak to the highlighted statement:

Workbooks.OpenText FileName:= _
        f, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=False, Other:=True, OtherChar:="|", _
        FieldInfo:=Array( _
            Array(1, 1), _
            Array(2, 1), _
            Array(3, 1), _
            Array(4, 2) _
            )


Regards,

Brad
0
town_cobblerAuthor Commented:
Ah! thats it!  The slight change in the format offered up by byundt has solved the problem.  Works like a charm now! Thanks to both of you for your insight and input.  Much appreciated!  Enjoy your weekend.
0
town_cobblerAuthor Commented:
Can a monitor fix my typo:
 "Excel macro inports data...

to

"Excel macro imports data...
thanks
0
byundtMechanical EngineerCommented:
Tim,
I fixed the question title plus a few other typos in the question body per your request.

byundt--Microsoft Excel Zone Advisor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.