Formatting question

[rollingzep]

Hi,

<p> I have a column A, to which I import data from a text file. It misses the leading zeros
For example the value 37833100 should be 037833100 but comes in as 3.78E+07

The column has data with values like 02376RAF9, 912796XP9, 37833100 (mix of both text and numeric) but length is 9

How to format so as to include the leading zeros?
will this help?

Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"

</p?

 

[Joe4]

If you want it to actually keep tjhe leading zeroes, then it needs to be handled while importing (explicitly import the field as text)

Once it is already in Excel, it it too late.
Although, you could use a custom number format of "000000000" to display any missing leading zeroes (if you know the length should always be nine and it is just the display and not the actual value you are concerned with).

 

[rollingzep]

If you want it to actually keep tjhe leading zeroes, then it needs to be handled while importing (explicitly import the field as text)

Once it is already in Excel, it it too late.
Although, you could use a custom number format of "000000000" to display any missing leading zeroes (if you know the length should always be nine and it is just the display and not the actual value you are concerned with).

I do use the code tpo import it as text. Or is there another way to format the column A while importing?

strfilename = Application.GetOpenFilename
   
    With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh

    End With

 

[Joe4]

I typically do not use that method for importing text files, but it looks like from this article, that you should be able to determine the data type of each field you are importing:

QueryTables.Add method (Excel)

Office VBA reference topic

docs.microsoft.com

Note this section of code here:

Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
    Connection := "TEXT;C:\My Documents\19980331.txt",
    Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
    .TextFileParsingType = xlFixedWidth
    .TextFileFixedColumnWidths := Array(5,4)
    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

You want to be sure to use the "xlTextFormat" option with the first column to keep the leading zeroes.

 

[rollingzep]

I typically do not use that method for importing text files, but it looks like from this article, that you should be able to determine the data type of each field you are importing:

QueryTables.Add method (Excel)

Office VBA reference topic

docs.microsoft.com

Note this section of code here:

Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
    Connection := "TEXT;C:\My Documents\19980331.txt",
    Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
    .TextFileParsingType = xlFixedWidth
    .TextFileFixedColumnWidths := Array(5,4)
    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

You want to be sure to use the "xlTextFormat" option with the first column to keep the leading zeroes.

Can you let me know what this is

.TextFileFixedColumnWidths := Array(5,4)

 

[Joe4]

Can you let me know what this is

.TextFileFixedColumnWidths := Array(5,4)

You don't need that part, since you have determined that it is a delimited file with these arguments:

        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True

You would use that other one if it was NOT a delimited file (but rather a fixed width file), so each field is a specific number of characters.

 

[rollingzep]

You don't need that part, since you have determined that it is a delimited file with these arguments:

        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True

You would use that other one if it was NOT a delimited file (but rather a fixed width file), so each field is a specific number of characters.

can I use

.TextFileColumnDataTypes := _
xlTextFormat, xlSkipColumn, xlGeneralFormat

 

[Joe4]

can I use

.TextFileColumnDataTypes := _
xlTextFormat, xlSkipColumn, xlGeneralFormat

No, that is not how you do it. You add the line in red I showed in post 4 to what you currently have, up above the ".Refresh" line.
The array shows the format for each field you are pulling in.

So, the structure would be list this:

    .TextFileColumnDataTypes := _
        Array(format of field 1, format of field 2, format of field 3, etc.)

The number of fields you are matching show match exactly the number of items you have listed in the Array section.

So in the example given in that link:

    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)

It is basically telling you that there are three fields, the first being Text (xlTextFormat), then to skip the second column (xlSkipColumn), and the third is General (xlGeneralFormat).

You haven't told us how many fields (columns) are being imported. If it is only one, then you would just need:

strfilename = Application.GetOpenFilename
 
    With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes := _
            Array(xlTextFormat)
        .Refresh

    End With

 

[rollingzep]

No, that is not how you do it. You add the line in red I showed in post 4 to what you currently have, up above the ".Refresh" line.
The array shows the format for each field you are pulling in.

So, the structure would be list this:

    .TextFileColumnDataTypes := _
        Array(format of field 1, format of field 2, format of field 3, etc.)

The number of fields you are matching show match exactly the number of items you have listed in the Array section.

So in the example given in that link:

    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)

It is basically telling you that there are three fields, the first being Text (xlTextFormat), then to skip the second column (xlSkipColumn), and the third is General (xlGeneralFormat).

You haven't told us how many fields (columns) are being imported. If it is only one, then you would just need:

strfilename = Application.GetOpenFilename
 
    With ws.QueryTables.Add(Connection:="TEXT;" & strfilename, Destination:=ws.Range("A2"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes := _
            Array(xlTextFormat)
        .Refresh

    End With

I import few files and they range from 30-40 fields(columns)
so how to modify the array, if suppose it is 36 fields?

 

[Joe4]

I import few files and they range from 30-40 fields(columns)
so how to modify the array, if suppose it is 36 fields?

You have an array, with 36 values separated by commas, one for each field type.

It is just like I explained in detail in my last post. Please take the time to read through that explanation slowly and carefully.
The example shown was for a file with 3 columns, hence there are 3 values in the array, one for each field (the first one applies to the first field, the second applies to the second field, and the third applies to the third field).