formatting number in csv file import. Number being rounded / decimnal places truncated

[barbosajulio77]

Hi,

have the code below when importing csv file the invoice # with 12 digits get imported like this "4.00003E+11
" when the number should imported like this "400003395647" please help to modify the code that import the correct number.


here is the code:

Sub ImportRawData()
 
    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    Dim vFile
    
    vFile = Application.GetOpenFilename("CSV Files(*.csv),*.csv", , "please select a file", MultiSelect:=False)


    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:E17")
        
        Filepath = "C:\Users\jose.rossi\Desktop\AP_NCL_VENDOR STATEMENT.xlsm"
        Filename = "apvtrn.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                'Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apvtrn").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("DM", "DI", "DN", "DP", "DQ")
       
' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row - rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                                     
                   rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
            Next Col
        End With
        
End Sub

Thank you,

[DocAElstein]

Hello again

I think it may be a lot easier for someone at excelfox to help you if we can have a copy of your workbook, and some data in a small .csv file

So we need to have
_ a copy of your workbook which has worksheet "STATEMENT" in it
_ a csv file, apvtrn.csv


Please change or remove any sensitive data. Please try to keep the data size small: We only need a small amount of data – we only need as much data as necessary to demonstrates the problem.

( here are some notes on attaching a file at excelfox
https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthrea...ll=1#post15589 )



Alan

[barbosajulio77]

Hi

files attached.

i had to save the apvtrn.csv file as xls format will not let attach csv file.



thank you,

[DocAElstein]

Thanks for the files.
Please could you try to upload the csv file again as a csv file. - I have changed the system so that you should now be able to upload csv files
( I did test, and I think now we can upload csv files at excelfox )
Thanks
Alan

[barbosajulio77]

Done.

attached in csv format.

Thank you,

[DocAElstein]

Hi

The numbers appear to be imported correctly.
I looked in your .csv text file :
https://excelfox.com/forum/showthrea...ll=1#post15602
https://excelfox.com/forum/showthrea...ll=1#post15603
https://i.imgur.com/PgEalNM.jpg
LargeNumbersImportedCorrectly.JPG
_____ Workbook: apvtrn.csv ( Using Excel 2007 32 bit )

Row\Col	DI
1	PY00000000000000004918
2	4.00E+11
3	PY00000000000000005064
4	4.00E+11
5	PY00000000000000005149
6	4.00E+11
7	PY00000000000000005260
8	4.00E+11
9	PY00000000000000005453

Worksheet: apvtrn

There are no numbers like "400003395647"

If you have a text file with data that cause the problem, please gives us a sample of that. ( Remember to please keep the sample file size small - we only need 2-3 lines of data in order to investigate the problem. But the data must be chosen so as to demonstrate the problem )

Alan

[p45cal]

I suspect the csv file has been saved from Excel as a csv file, so Excel has already mangled it.
What version of Excel are you using?
You could record a macro of you using the Text Import wizard, where you'll be able to choose the 5 columns you want to import as well as ensure that the account numbers are imported as text and convert the date columns to real dates. Then it'll be a matter of tweaking the recorded macro for more general use.
If you have Get & Transform Data (Power Query) which is built in to the more recent versions of Excel you'll be able extract, transform and load your data exactly as you want it.

[barbosajulio77]

thank you for your suggestion will do that going forward.