Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CSV text data altered on file open
I am opening a CSV file in which the first column is a text field. The text
field contains a 9 digit alpha numeric characters. Excel makes the assumption that the field is a number, which then alters the content of the alpha numeric text data by removing all leading zeros and if the letter "E" or "e" exists in the text field, Excel turns it into Scientific notation. After I open the file, I select the cell, then set format to text, but it is too late. The scientific notation is set and all leading zeros are gone. Is there a way to not have this happen? I am using Excel 2007 Thanks in advance for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CSV text data altered on file open
Use this code to read the CSV data. Before running the macro format the
cells to the correct format. The code will not change the format when the data is read. Sub GetCSVData() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Const Delimiter = "," Set fsread = CreateObject("Scripting.FileSystemObject") 'default folder Folder = "C:\temp\test" ChDir (Folder) FName = Application.GetOpenFilename("CSV (*.csv),*.csv") RowCount = LastRow + 1 If FName < "" Then 'open files Set fread = fsread.GetFile(FName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine 'extract comma seperated data ColumnCount = 1 Do While InputLine < "" DelimiterPosition = InStr(InputLine, Delimiter) If DelimiterPosition 0 Then Data = Trim(Left(InputLine, DelimiterPosition - 1)) InputLine = Mid(InputLine, DelimiterPosition + 1) Else Data = Trim(InputLine) InputLine = "" End If Cells(RowCount, ColumnCount) = Data ColumnCount = ColumnCount + 1 Loop RowCount = RowCount + 1 Loop tsread.Close End If End Sub "Mike P" wrote: I am opening a CSV file in which the first column is a text field. The text field contains a 9 digit alpha numeric characters. Excel makes the assumption that the field is a number, which then alters the content of the alpha numeric text data by removing all leading zeros and if the letter "E" or "e" exists in the text field, Excel turns it into Scientific notation. After I open the file, I select the cell, then set format to text, but it is too late. The scientific notation is set and all leading zeros are gone. Is there a way to not have this happen? I am using Excel 2007 Thanks in advance for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CSV text data altered on file open
Change the extension to text (txt) instead and the text import wizard will
open, then you can set the import in a particular column as text and Excel won't try to convert it to a number. -- Regards, Peo Sjoblom "Mike P" wrote in message ... I am opening a CSV file in which the first column is a text field. The text field contains a 9 digit alpha numeric characters. Excel makes the assumption that the field is a number, which then alters the content of the alpha numeric text data by removing all leading zeros and if the letter "E" or "e" exists in the text field, Excel turns it into Scientific notation. After I open the file, I select the cell, then set format to text, but it is too late. The scientific notation is set and all leading zeros are gone. Is there a way to not have this happen? I am using Excel 2007 Thanks in advance for your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CSV text data altered on file open
Or open it with Data/ Import External Data.
-- David Biddulph "Peo Sjoblom" wrote in message ... Change the extension to text (txt) instead and the text import wizard will open, then you can set the import in a particular column as text and Excel won't try to convert it to a number. "Mike P" wrote in message ... I am opening a CSV file in which the first column is a text field. The text field contains a 9 digit alpha numeric characters. Excel makes the assumption that the field is a number, which then alters the content of the alpha numeric text data by removing all leading zeros and if the letter "E" or "e" exists in the text field, Excel turns it into Scientific notation. After I open the file, I select the cell, then set format to text, but it is too late. The scientific notation is set and all leading zeros are gone. Is there a way to not have this happen? I am using Excel 2007 Thanks in advance for your help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CSV text data altered on file open
I was going to say that but I don't recall what it is called in Excel 2007
which the OP uses. Also one has to make sure one changes the filetypes to *.* or else the CSV file won't be visible -- Regards, Peo Sjoblom "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Or open it with Data/ Import External Data. -- David Biddulph "Peo Sjoblom" wrote in message ... Change the extension to text (txt) instead and the text import wizard will open, then you can set the import in a particular column as text and Excel won't try to convert it to a number. "Mike P" wrote in message ... I am opening a CSV file in which the first column is a text field. The text field contains a 9 digit alpha numeric characters. Excel makes the assumption that the field is a number, which then alters the content of the alpha numeric text data by removing all leading zeros and if the letter "E" or "e" exists in the text field, Excel turns it into Scientific notation. After I open the file, I select the cell, then set format to text, but it is too late. The scientific notation is set and all leading zeros are gone. Is there a way to not have this happen? I am using Excel 2007 Thanks in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open text file with excel | Excel Discussion (Misc queries) | |||
open text file in excel | Excel Worksheet Functions | |||
Open text file in excel that has multiple rows of data | Excel Discussion (Misc queries) | |||
Lock a file so it can't be altered unless saved as another file | Excel Worksheet Functions | |||
When I open a *.CSV file, text appear in the first column? What c. | Setting up and Configuration of Excel |