EXCEL read a text string as scientific notation

[Chuck Virtu]

I am working on a project that involes reading an Excel file, picking through
it, then creating a flat CSV file. One of the the data elements I am picking
up is a 9 character string that may be numeric, or contain one or more alpha
chatacters. Excel has a nasty habit of converting long numerics to
scientific notaion. I can overcome this by converting the "general" string
to "TEXT".



However, there is one instance where I can not get the function to
work...the string is "90333E108" ( the CUSIP for USU). Regardless, the text
string is saved properly into the CSV file. When opened in a text editor, it
reads correctly.



When opened with Excel, it is in scientific notation. Changing the field to
"text" from "scientific" still displays "9.03E+112".



Any ideas how to create an csv file that Excel will open and read correctly?



You can test this by creating an Excel workbook, format all cells as TEXT
and add the following data:



90333E108 123 2
abc 90333E108 r1312sda
123 abc 90333E108
321 123 abc
231 313 123


then use the following function:

ActiveWorkbook.SaveAs Filename:="test.csv", FileFormat:=xlCSVMSDOS,
CreateBackup:=False


("xlCVS" instead of "xlCSVMSDOS") makes no difference.



Open the file "test.csv" in note pad, you have :



90333E108,123,2

abc,90333E108,r1312sda

123,abc,90333E108

321,123,abc

231,313,123



Open in Excel and you have :



9.03E+112 123 2
abc 9.03E+112 r1312sda
123 abc 9.03E+112
321 123 abc
231 313 123







Any ideas?


Chuck

 

[Dave O]

Hey, Chuck-
This will work, up to a point and after a fashion (or at least until
one of the gurus responds with a better idea): change your CSV file
extension to TXT, and follow the wizard to import the file. When it
comes time to choose a format type, change "General" to "Text" for
columns that contain the E within the text string. This VBA command
worked with the sample data you included in your post (i.e., three
columns of data):

Workbooks.OpenText Filename:="C:\ok2del.txt", Origin:=437,
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=True,
Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2),
Array(3, 2)), _
TrailingMinusNumbers:=True

The only other idea I can think of is to write VBA code that will open
the CSV file, read each line, and write to cells while formatting E
entries as text. That's chunky but do-able, it just may be more than
the time is worth.

Dave O
Eschew obfuscation

 

[David Biddulph]

Don't allow Excel to open the CSV with its default settings. Instead of
File/ Open, or double clicking on the CSV, use Excel's Data/ Import External
Data/ ... route which invokes the text import wizard which allows you to
specify the column format as text before you pull the data into it. [If you
once let it treat 90333E108 as a number rather than text, then you can't
change it back to text by merely fiddling with the cell format, as the text
has already been lost.]

Another option is to name the file as txt, not csv, so that again it will
invoke the wizard.

 

[Chuck Virtu]

Thanks for the reply.

The purpose of creating the csv file is for a downstream application. I
don't know what the downstream application is, ( it is external to my
company, but am trying to find out), but the spec calls for a flat csv file.
Whenthe vendor tests the file, it blows up the scientific notation field.
Obviously they are using EXCEL or some other Office product to import the
file that is equally as arrogant as EXCEL because it is trying to interpret
the data, rather than just read it.

One day perhaps EXCEL will understand that a "comma seperated file" is just
and just read the data and get out of the way.


Thanks again

Don't allow Excel to open the CSV with its default settings. Instead of
File/ Open, or double clicking on the CSV, use Excel's Data/ Import External
Data/ ... route which invokes the text import wizard which allows you to
specify the column format as text before you pull the data into it. [If you
once let it treat 90333E108 as a number rather than text, then you can't
change it back to text by merely fiddling with the cell format, as the text
has already been lost.]

Another option is to name the file as txt, not csv, so that again it will
invoke the wizard.
--
David Biddulph

I am working on a project that involes reading an Excel file, picking
through
it, then creating a flat CSV file. One of the the data elements I am
picking
up is a 9 character string that may be numeric, or contain one or more
alpha
chatacters. Excel has a nasty habit of converting long numerics to
scientific notaion. I can overcome this by converting the "general"
string
to "TEXT".



However, there is one instance where I can not get the function to
work...the string is "90333E108" ( the CUSIP for USU). Regardless, the
text
string is saved properly into the CSV file. When opened in a text editor,
it
reads correctly.



When opened with Excel, it is in scientific notation. Changing the field
to
"text" from "scientific" still displays "9.03E+112".



Any ideas how to create an csv file that Excel will open and read
correctly?



You can test this by creating an Excel workbook, format all cells as TEXT
and add the following data:



90333E108 123 2
abc 90333E108 r1312sda
123 abc 90333E108
321 123 abc
231 313 123


then use the following function:

ActiveWorkbook.SaveAs Filename:="test.csv", FileFormat:=xlCSVMSDOS,
CreateBackup:=False


("xlCVS" instead of "xlCSVMSDOS") makes no difference.



Open the file "test.csv" in note pad, you have :



90333E108,123,2

abc,90333E108,r1312sda

123,abc,90333E108

321,123,abc

231,313,123



Open in Excel and you have :



9.03E+112 123 2
abc 9.03E+112 r1312sda
123 abc 9.03E+112
321 123 abc
231 313 123







Any ideas?


Chuck

 

[Chuck Virtu]

Dave - close but no cigar. I am not trying to read in the text into a csv
file, I am creating the csv file for a downstream app. The app is either
using EXCEL or other Office product, and is blowing up on the one data
element. I want the CSV file formated by EXCEL so that it understands not to
try to interpret the data.

Thanks for taking the time to post...

Chuck

 

[David Biddulph]

A CSV is *not* formatted; it is comma separated *text*.

The problem is not in in the CSV, but in the way that your downstream
application is interpreting the data from the CSV. You may be able to fool
the downsteam application by throwing in spaces, or things like CHAR(160),
or surrounding the text string with quotes, or preceding it by an
apostrophe, or a variety of other options, but that depends on how the
downstream application is going to read the data. Sensibly, the downstream
application would read it as text.

 

[Chuck Virtu]

Dave -

I found out what the actual issue is...the file I am creating is going to
feed a downstream system. The other company is running my file through a
test system. However, the person doing the test wants to "look at" my file
so he opens it in Excel, which interprets the field as scientific notaion
then when he closes the file HE SAVES IT. When he feeds it into the test
system it failed. I provided a clean file to him, instructed him not to fool
with it, and it worked.

Thanks again....
Chuck

 

[David Biddulph]

Glad you got to the bottom of the problem. Thanks for letting us know the
cause.