Solved

Importing csv to excel - text formating

Posted on 2016-11-24
14
20 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:Wass_QA
14 Comments
 
LVL 47

Expert Comment

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

Author Comment

by:Wass_QA
Comment Utility
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
Comment Utility
What happens if you use Workbooks.OpenText instead of just Workbooks.Open?
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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:Wass_QA
Comment Utility
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)
Comment Utility
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:Wass_QA
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Expert Comment

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

Open in new window

0
 

Author Comment

by:Wass_QA
Comment Utility
Invalid Character error
Destination:=Range("$BC$2"))
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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:Wass_QA
Comment Utility
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
Comment Utility
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
Comment Utility
Ditto: Wayne Taylor  (webtubbs)
0
 

Author Closing Comment

by:Wass_QA
Comment Utility
Thank you very much Wayne.
Your help is greatly appreciated.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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

10 Experts available now in Live!

Get 1:1 Help Now