Solved

Importing csv to excel - text formating

Posted on 2016-11-24
14
46 Views
Last Modified: 2016-11-25
Hello,
Can you please help,
I'm trying to Copy data from a csv file File to my Excel Sheet. (Excel 2007)
Trying to Copy Columns A-AZ.
I formatted Column BD on my excel sheet, as text before Copying from the csv file.
Still, the numbers are showing as +11, +21,....
if I format Column BD as Custom 0, the numbers are showing with trailing zeroes.
9.61102E+21    6.48029E+11      7.75632E+11    7.756E+11    1.9747E+14

This is the code I'm using.

Dim fd As FileDialog
Dim strWorkbookName As String
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 With fd
      .InitialFileName = "TEXT;C:\Users\Wass\Desktop\**.*"
     .Filters.Clear
     .Filters.Add "", "*.csv", 1
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        strWorkbookName = ActiveWorkbook.Name

'Copy Range
Range("A1:AZ25000").Copy

'''Paste
    Windows("Ground.xls").Activate
    Sheets("Orders").Range("BC1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Windows(strWorkbookName).Activate
    ActiveWindow.Close
    End If
 End With

Your help is greatly appreciated,
0
Comment
Question by:W.E.B
14 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41901058
What do the values in the CSV file look like?
0
 

Author Comment

by:W.E.B
ID: 41901061
If I open the csv in note Pad, I see the full numbers.
12 digits up to 24 digits.

if I open the csv file, I see same as Excel.
9.61102E+21
6.48029E+11
6.64773E+11
0
 
LVL 33

Expert Comment

by:Norie
ID: 41901065
What happens if you use Workbooks.OpenText instead of just Workbooks.Open?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41901066
Excel has a maximum 15 digits of precision, so when you open the CSV file in Excel, it will be converted to Scientific notation, with the remaining digits > 15 removed. Formatting as number doesn't help, as there is still only 15 digits of precision in the value.

The work around for your instance is to not open the CSV file in Excel, but import the data as Text. Record a macro while going to Data > From Text. Select Comma as the delimiter then select Text format on the appropriate data column.
1
 

Author Comment

by:W.E.B
ID: 41901092
Hi Wayne,
this is what I've been doing for a long time.
but, now I need to incorporate this into a bigger macro that I need to give to some of our Employees to use.
I was hoping for a solution that don't require any additional steps.

thanks.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41901097
That's why I suggested recording a macro while doing the import. Incorporate the code from the recorded macro into your bigger macro.
0
 

Author Comment

by:W.E.B
ID: 41901102
thanks for your help,

how do I change this
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Wassim\Desktop\orders", Destination:=Range("$BC$2"))
        .Name = "orders" ...........................

To allow me to choose the file
Application.FileDialog(msoFileDialogFilePicker)

thanks again.
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41901103
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1) & ", Destination:=Range("$BC$2"))

Open in new window

0
 

Author Comment

by:W.E.B
ID: 41901107
Invalid Character error
Destination:=Range("$BC$2"))
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41901109
Sorry, should actually be this...
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$2"))

Open in new window

0
 

Author Comment

by:W.E.B
ID: 41901118
Hi Wayne,
Sorry,
It's not doing anything,
it's only  opening the csv file

Dim fd As FileDialog
Dim strWorkbookName As String
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 With fd
      .InitialFileName = "TEXT;C:\Users\Wassim\Desktop\**.*"
     .Filters.Clear
     .Filters.Add "", "*.csv", 1
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        strWorkbookName = ActiveWorkbook.Name
    End If
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$1"))
         ''''''''''''''''''''''''''''' .Name = Trim(.SelectedItems.Item(1)) '''''''''''''''''''''''''''''''''''''''''''''''
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

        End With
        End With
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41901119
You use the import instead of opening the CSV file....

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "TEXT;C:\Users\Wassim\Desktop\**.*"
    fd.Filters.Clear
    fd.Filters.Add "", "*.csv", 1
    If fd.Show = -1 Then
        With ActiveSheet.QueryTables.Add(Connection:= _
              "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$1"))
             .FieldNames = True
             .RowNumbers = False
             .FillAdjacentFormulas = False
             .PreserveFormatting = True
             .RefreshOnFileOpen = False
             .RefreshStyle = xlInsertDeleteCells
             .SavePassword = False
             .SaveData = True
             .AdjustColumnWidth = True
             .RefreshPeriod = 0
             .TextFilePromptOnRefresh = False
             .TextFilePlatform = 437
             .TextFileStartRow = 1
             .TextFileParseType = xlDelimited
             .TextFileTextQualifier = xlTextQualifierDoubleQuote
             .TextFileConsecutiveDelimiter = False
             .TextFileTabDelimiter = True
             .TextFileSemicolonDelimiter = False
             .TextFileCommaDelimiter = True
             .TextFileSpaceDelimiter = False
             .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1)
             .TextFileTrailingMinusNumbers = True
             .Refresh BackgroundQuery:=False
        End With
    End If

Open in new window

0
 
LVL 24

Expert Comment

by:SunBow
ID: 41901324
Ditto: Wayne Taylor  (webtubbs)
0
 

Author Closing Comment

by:W.E.B
ID: 41901418
Thank you very much Wayne.
Your help is greatly appreciated.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overā€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question