Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "David Biddulph" wrote: 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 "Chuck Virtu" <Chuck wrote in message ... 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "Dave O" wrote: 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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. -- David Biddulph "Chuck Virtu" wrote in message ... 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 "Dave O" wrote: 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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote: 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. -- David Biddulph "Chuck Virtu" wrote in message ... 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 "Dave O" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
show value as text not scientific notation | Excel Discussion (Misc queries) | |||
Prevent conversion of numeric text string to scientific notation | Excel Discussion (Misc queries) | |||
Scientific Notation - I want excel to read it as a text not a numb | Excel Discussion (Misc queries) | |||
Scientific notation in text cells | Excel Discussion (Misc queries) | |||
Using scientific notation in TEXT() function | Excel Worksheet Functions |