using OpenText to avoid saving in scientific notation

Question

I've tried to use the OpenText method described in this post on a file which looks like this:

Ref,AccNum,SCode,RollNum
999,15697668,404040,4921817045040610

When I run this code the fourth column still appears in scientific notation:

Workbooks.OpenText FileName:="H:\DataFile.csv", Origin:=3, StartRow:=1, _
        DataType:=1, TextQualifier:=1, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
        Other:=False, FieldInfo:=Array(Array(4, 2)), _
        TrailingMinusNumbers:=True

Do you know what I'm doing wrong?

Did you start by recording a macro ? You FieldInfo parameter seems like it should have more items in the array. — Tim Williams, Jul 23 '12 at 15:00

score 1 · Answer 1 · answered Jul 23 '12 at 16:09

1

This works:

FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2))

Seems you can't chop out the Array() values you're not interested in.

answered Jul 23 '12 at 16:09

Tim Williams

154,628
8
97
125

Thanks for replying; did you get the scientific notation when you used just one Array() value? Even with four, I still do. http://msdn.microsoft.com/en-us/library/office/bb223513%28v=office.12%29.aspx seems to say that not all columns need to be detailed in FieldInfo. – user1546084 Jul 24 '12 at 09:49
Yes, I did, and switching back to four array members fixed it. – Tim Williams Jul 24 '12 at 14:56

score 0 · Answer 2 · answered Jul 23 '12 at 15:16

0

Excel automatically makes these conversions on large numbers, but you work around it by displaying the numbers in a text format.
This example shows the syntax:

ThisWorkbook.Sheets(1).Columns(4).NumberFormat = "@"

answered Jul 23 '12 at 15:16

html_programmer

18,126
18
85
158

Thanks for replying; I'm not concerned with how it displays; the cell value is changed to 4.92E+15 when the file is opened in Excel and I need to prevent this so that when I close the file, it does not save like this. – user1546084 Jul 24 '12 at 10:17
Just a question; you say you are not concerned with how it displays. But then what is the problem having it in the form 4.92E+15? Excel doesn't change the actual value of the cell, only the way it's displayed. – html_programmer Jul 24 '12 at 10:22
that is a good question... the problem is, when I save the file (still in csv format) and I open the file in notepad, it has the scientific notation. However, I'm sorry I doubted your NumberFormat = "@" suggestion, because I have now tried this and the file did save without E+ although the number was 12345678901234567890 in the original file and 12345678901234500000 in the finished file. – user1546084 Jul 25 '12 at 11:08
I posted a new reply, this way it should definitely work. But take care that it will be saved as a string in Excel. – html_programmer Jul 25 '12 at 12:37

score 0 · Answer 3 · answered Jul 25 '12 at 12:36

An alternative method is to read the csv line by line, load it into a multidimensional array, and add an apostrophe before the string.
I have tested this and I'm positive that it works.

Sub ReadLines()

Dim oFSO                As FileSystemObject
Dim sLine               As String
Dim vArray()            As String
Dim vArray_Final        As Variant
Dim oTS                 As TextStream
Dim iCols               As Integer
Dim iRows               As Integer

Dim lCnt                As Long
Dim lCnt_X              As Long
Dim lCnt_Y              As Long

Set oFSO = New FileSystemObject
Set oTS = oFSO.OpenTextFile("U:\DataFile.csv")

lCnt_X = 1
lCnt_Y = 1

iCols = 4

Do Until oTS.AtEndOfStream
    sLine = oTS.ReadLine
    iRows = iRows + 1
Loop
Set oTS = Nothing

Set oTS = oFSO.OpenTextFile("U:\DataFile.csv")
ReDim vArray_Final(1 To iRows, 1 To iCols)

Do Until oTS.AtEndOfStream

    lCnt = lCnt + 1
    lCnt_X = 1
    lCnt_Y = 1

    sLine = oTS.ReadLine
    vArray = Split(sLine, ",")

    For lCnt_Y = 1 To iCols
        vArray_Final(lCnt, lCnt_Y) = vArray(lCnt_Y - 1)
    Next lCnt_Y

Loop

Set oTS = Nothing
Set oFSO = Nothing

For lCnt_X = 1 To iRows
    For lCnt_Y = 1 To iCols
        If lCnt_Y = iCols Then
            ThisWorkbook.Sheets(3).Cells(lCnt_X, lCnt_Y).Value = "'" & vArray_Final(lCnt_X, lCnt_Y)
        Else
            ThisWorkbook.Sheets(3).Cells(lCnt_X, lCnt_Y).Value = vArray_Final(lCnt_X, lCnt_Y)
        End If
    Next lCnt_Y
Next lCnt_X

End Sub

tbaker818 · Answer 4 · 2017-10-27T23:01:04.773

0

Using the FieldInfo argument to specify text works unless it's a .csv, in which case you could just change the extension to .txt. For some reason, this type of info is ignored with CSV files. But the array has to cover all fields, not just the ones you want to import (skip is one of the options.)

edited Oct 27 '17 at 23:01

answered Oct 27 '17 at 22:43

tbaker818

121
1
10

using OpenText to avoid saving in scientific notation

4 Answers4