Solved

Importing csv to excel - text formating

Posted on 2016-11-24
14
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

733 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