none
Need Help using OpenText Methord to read a CSV formmated file into an excel spreadsheet RRS feed

  • Question

  • I am trying to load a csv into excel with formated columns, the 4th column is alphanumeric but after loading the values in the four column the column is define as general and it drops the leading zeros on values that have the leading zeros

    i can not use a sub string to preload the zeros due to varing length and format the csv file has them correctly anybody have a solution on how to correct?

    the VBScript looks like;

    Set objExcel = CreateObject("Excel.application")
    Const xlDelimited  = 1
    Const xlGeneralFormat = 1
    Const xlTextFormat = 2
    oFile = "I:/temp/MEMAPRPT3P 2014-08-04.csv"
    wfile = "I:/temp/MEMAPRPT3P 2014-08-04"
    filetime = "_2015_01_20_10_28_00"
    tagname = "sample"
    objExcel.application.visible=false
    objExcel.application.displayalerts=false
    objExcel.Workbooks.OpenText Filename, , 1, 1, , False, False, False, True, False, , ,Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat))
    Set objExcelBook = objExcel.ActiveWorkBook
    Saveasfile = trim(wfile) & trim(filetime) & ".xlsx"
    objExcelBook.SaveAs Saveasfile, 51
    objExcel.Application.Quit
    objExcel.Quit  
    Set objExcel = Nothing
    Set objExcelBook = Nothing

    The csv data look like

    "HEDA","Fred","Detroat","0598"

    "SDRA","Steve","Jericho","Q23456"

    "ADAS","Tim","Home","000892356"

    


    

    Tuesday, January 20, 2015 6:43 PM

Answers

  • i can not use a sub string to preload the zeros due to varing length and format the csv file has them correctly anybody have a solution on how to correct?

    The issue is that the cell isn't formatted as text, that's a bug within OpenText.

    You can use a query table instead and emulate OpenText. The benefit is that you can import the data in the current file.

    Andreas.

    Sub Test()
      Dim FName As String
      Dim Q As QueryTable
      
      FName = "C:\Users\Killer\Documents\test.csv"
      
      'Add a new sheet after the current sheet
      Worksheets.Add After:=Sheets(ActiveSheet.Index)
      'Add a query table
      Set Q = ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & FName, Destination:=Range("$A$1"))
      With Q
        'Setup the importsettings
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat)
        'Import the data
        .Refresh BackgroundQuery:=False
        'Delete the query table (we don't need a permanent link to the file)
        .Delete
      End With
    End Sub
    

    • Proposed as answer by ryguy72 Saturday, January 24, 2015 7:03 PM
    • Marked as answer by George HuaModerator Tuesday, February 3, 2015 11:34 AM
    Friday, January 23, 2015 11:06 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.
    Wednesday, January 21, 2015 5:35 AM
  • I can think of two ways to go at the problem.

    1. Put a single quote in front of the string in your csv file: "HEDA","Fred","Detroit","'0598"  The leading quote 'tells' Excel to left justify the string and (should) retain the leading zero character.

    2. Open the text file in read mode, read each row, and parse each value into the appropriate cell.

    Wednesday, January 21, 2015 1:49 PM
  • Hi deyetted,

    Base on my test, I found that the specified column format not work if it opens the csv file, if it is working for txt file.

    I tried to record the macro in excel, I found that it doesn’t use OpenText() method to open csv file (Use Open() method instead).

    So, I suggest that you could change your csv file to txt file. (just change the file extension)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 22, 2015 7:14 AM
    Moderator
  • i can not use a sub string to preload the zeros due to varing length and format the csv file has them correctly anybody have a solution on how to correct?

    The issue is that the cell isn't formatted as text, that's a bug within OpenText.

    You can use a query table instead and emulate OpenText. The benefit is that you can import the data in the current file.

    Andreas.

    Sub Test()
      Dim FName As String
      Dim Q As QueryTable
      
      FName = "C:\Users\Killer\Documents\test.csv"
      
      'Add a new sheet after the current sheet
      Worksheets.Add After:=Sheets(ActiveSheet.Index)
      'Add a query table
      Set Q = ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & FName, Destination:=Range("$A$1"))
      With Q
        'Setup the importsettings
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat)
        'Import the data
        .Refresh BackgroundQuery:=False
        'Delete the query table (we don't need a permanent link to the file)
        .Delete
      End With
    End Sub
    

    • Proposed as answer by ryguy72 Saturday, January 24, 2015 7:03 PM
    • Marked as answer by George HuaModerator Tuesday, February 3, 2015 11:34 AM
    Friday, January 23, 2015 11:06 AM