CSV text data altered on file open


Mike P

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!


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)
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
RowCount = RowCount + 1

End If
End Sub

Peo Sjoblom

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



Peo Sjoblom

Peo Sjoblom

I was going to say that but I don't recall what it is called in Excel 2007
the OP uses. Also one has to make sure one changes the filetypes to *.*
or else the CSV file won't be visible



Peo Sjoblom

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question