Solved

Importing csv to excel - text formating

Posted on 2016-11-24
14
35 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now