Sage 100

 View Only
Back to discussions
Expand all | Collapse all

Save as Tab dliminited TXT file from Excel.

  • 1.  Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 16:01
    Running into a wall when saving an Excel table as a tab deliminated (TXT) file.

    Excel tends to add an extra " (double quote) to a field where one already exists.  For instance, if a description is Bar - 1" X 1" X 10" in Excel, it will save in the TXT file as Bar - 1"" X 1"" X 10"" in the TXT file.

    My current solution is to import the table into Access, then export as a TXT tab file.  Access does it correctly.

    In v2021, I have tried importing into VI the actual Excel file, but Vi does not recognize the tables.

    Any tips, tricks to get Sage & Excel and TXT files to play in the sand box better?

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------


  • 2.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 16:34
    I use tab delimited all the time.  Try using Export instead of Save As.  In VI the file type is ASCII and the character code is 009.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------



  • 3.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 22:16
    VI is set for ASCII/code 009.  I don't see where the Export function is.  Tell me more @Doug Higgs
    ​

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 4.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 22:18
    File (upper left corner), Export.  I'll send screenshots tomorrow.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 5.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 22:30
    Found it.  Thanks

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 6.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-28-2021 23:09
    That didn't work either.  It seems that if description field has a " (double quote) as the last character, the save as/Export to a tab delimited TXT file will add two extra "" for a total of three.  VI includes two in the imported description.  To avoid this entirely, I import the Excel table into Access, then export as a tab delimited with NO text qualifiers.  This resolves the double quote issue.

    Is there a way in Excel to save as a as a tab delimited TXT file without Excel using "'s at the beginning and end of the fields?

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 7.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 00:23
    Can you do a find and replace in the tab delimited file?

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 8.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 08:22
    Been there, doing that as well.  I want to stop doing that.  Trying to figure out a simple way for clients to do item description editing.  Truly amazing how inconsistent folks are about entering descriptions of similar items.  So they export descriptions into Excel, clean them up and re-import only to notice later the dreaded double quotes.

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 9.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 08:43
    You can create a macro in Excel to create the tab limited text file.  Just run the macro.  Here's the link:

    https://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 10.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 08:45
    Select the cells in Excel you want to export then run the macro:

    Sub Export()
    Dim r As Range, c As Range
    Dim sTemp As String

    Open "c:\MyOutput.txt" For Output As #1
    For Each r In Selection.Rows
        sTemp = ""
        For Each c In r.Cells
            sTemp = sTemp & c.Text & Chr(9)
        Next c

        'Get rid of trailing tabs
        While Right(sTemp, 1) = Chr(9)
            sTemp = Left(sTemp, Len(sTemp) - 1)
        Wend
        Print #1, sTemp
    Next r
    Close #1
End Sub


    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 11.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 11:19
      |   view attached
    Excel is handling the double quotes correctly.  Sage 100's VI import file parser is the one that doesn't handle escaped double quotes correctly.  I wrote some perform logic at one point that ran on the "After Read" event.  I have another idea to handle this that would process the entire file before hand but I haven't actually implemented it yet.  The attached zip file contains two different perform logic files and the ReadMe PDF document details how to use them.

    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Attachment(s)

    zip
    VI_SourceRecordPerformLogic.zip   76 KB 1 version
    Original Message


  • 12.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 11:24
    Find and replace all double quotes with ^
    Then do a calculated field like this:
      • Sub(Temp001$,"^",chr(34))


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 13.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 11:37
    Just following up on this and I think I can do a one line perform logic to correct the entire file in one shot instead of line by line so it would just have to be assigned to the "On Execution" perform logic event.  Stay tuned.

    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 14.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 11:48
    I know it is extremely easy to export data to Excel and a lot of the time this is completely fine.  But whenever I extract data that I intend to import back in... especially if there is the possibility of Excel messing with the data, (removing leading 0's and data w/ quotes and commas in it), I take Excel out of the picture.  Instead, I create a Crystal report formatted to extract the data I am after and generally delimited with a pipe (|) symbol.  View the report and export it to a text file, being sure to set my CPI to the max of 16 and pagination to 0, (no pagination).

    In your case, is it possible to edit a file created this way with a text editor and then use V/I to re-import? .. Seems like it would eliminate a lot of other mucking around to me anyway.

    --
    Randy Marion * Software Systems Engineer *
    Victory Wholesale Group - Information Services
    400 Victory Drive
    Springboro, OH 45066
    Phone - (937) 746-1010 x1012



    Original Message


  • 15.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 12:29
    Edited by David Speck II 10-29-2021 12:32
    Got something that works on the After Read event as a one liner that doesn't require a separate file to hold perform logic.  To Do this with the entire source file requires a separate file to hold the perform logic.

    Here is the one liner.  It copies the IMP$ array to itself while performing the SUB function which replaces pairs of double quotes with a single double quote.  No need to specify columns like my previous one. 
    IMP${ALL}=SUB(IMP${ALL},QUO+QUO,QUO) ! '​

    The above perform logic will correct the following.



    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 16.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 10:03
    what about pipe | delimited instead?

    ------------------------------
    alan niergarth
    Velosio LLC
    ------------------------------



  • 17.  RE: Save as Tab dliminited TXT file from Excel.

    Posted 10-29-2021 13:27
    I user LibreOffice Calc (free Office replacement) for creating delimited files from .xls or .xlsx.  It will prompt you for delimiter and whether to quote fields so you have control over the output.

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------

    Original Message


Related Content