0

When I import tilde-delimited text files, Excel 365 is truncating them at 50 columns, with the remaining columns silently discarded. I can't find a setting that controls this limit. How can I import delimited files with more than 50 columns?

Here's a single line from the file (425 columns):

Schema_InvcHead~RptLanguageID~AGAuthorizationCode~AGAuthorizationDate~AGDocConcept~AGDocumentLetter~AGInvoicingPoint~AGLegalNumber~AGPrintingControlType~AGUseGoodDefaultMark~ApplyDate~ARLOCID~AutoPrintReady~BillingDate~BillingNumber~BillToInvoiceAddress~BlockedFinChrg~BlockedFinChrgReason~BlockedRemLetters~BlockedRemLettersReason~BranchID~ChangeDate~ChangedBy~ChangeTime~CHISRCodeLine~CMReason~CMType~CNConfirmDate~CNTaxInvoiceType~CollectionsCust~Company~Consolidated~ContractDate~ContractRef~CopyLatestInvoice~CorrectionInv~CounterARForm~CreditMemo~CurrRateDate~CustAgentName~CustAgentTaxRegNo~CustNum~CycleCode~CycleInactive~DebitNote~Deferred~DepositAmt~DepositCredit~DepUnallocatedAmt~DNComments~DNCustNbr~DocDepApplied~DocDepositAmt~DocDepositCredit~DocDepUnallocatedAmt~DocInvoiceAmt~DocMaxValueAmt~DocPECharges~DocPECommissions~DocPEDetTaxAmt~DocPEDischargeAmt~DocPEInterest~DocPENoPayPenalty~DocRecurBalance~DocRounding~DocumentPrinted~DocWithholdAmt~DueDate~Duration~EDIReady~EInvException~EInvoice~EInvRefNum~EInvStatus~EInvTimestamp~EInvUpdatedBy~EndDate~Excluded~ExportDocDate~ExportDocRefNum~ExportReportNo~ExportType~FiscalCalendarID~FiscalYearSuffix~GUIDateOfExport~GUIDeductCode~GUIExportBillNumber~GUIExportBillType~GUIExportMark~GUIExportType~GUIFormatCode~GUITaxTypeCode~HeadNum~HoldInvoice~INExportProcedure~InPrice~INShippingPortCode~InstanceNum~INTaxTransactionID~InvInCollections~InvoiceAmt~InvoiceComment~InvoiceDate~InvoiceNum~InvoiceNumList~InvoiceRef~InvoiceType~IsAddedToGTI~IsRecurring~LastChrgCalcDate~LastDate~LegalNumber~LockTaxRate~MainSite~MaxValueAmt~MXAccountNumber~MXApprovalNum~MXApprovalYear~MXCancellationMode~MXCancelledDate~MXCBB~MXCertificate~MXCertificateSN~MXCertifiedTimestamp~MXConfirmationCode~MXDigitalSeal~MXDomesticTransfer~MXExportCertOrigin~MXExportCertOriginNum~MXExportConfNum~MXExportCustDocCode~MXExportOperationType~MXExternalCode~MXFiscalFolio~MXFolio~MXIncoterm~MXInvoiceRelationType~MXMovingReason~MXMovingReasonFlag~MXNumRegIdTrib~MXOriginalAmount~MXOriginalDate~MXOriginalFolio~MXOriginalSeries~MXOriginalString~MXOriginalStringTFD~MXPaidAs~MXPartPmt~MXPaymentName~MXPaymentNum~MXPostedTimeStamp~MXPurchaseType~MXResidenCountryNum~MXSATCertificateSN~MXSATSeal~MXSerie~MXTaxRcptType~MXTaxRegime~MXTotalPayments~OrdExchangeRate~OrigDueDate~OurBank~OurSupplierCode~Overpaid~OverrideEndDate~OvrDefTaxDate~PayDiscDays~PayDiscPartPay~PayDiscPer~PBProjectID~PEAPPayNum~PEBankNumber~PEBOEIsMultiGen~PEBOEPosted~PEBOEStatus~PECharges~PECommissions~PEDebitNote~PEDebitNoteReasonCode~PEDetTaxAmt~PEDetTaxCurrencyCode~PEDischargeAmt~PEDischargeDate~PEGuaranteeAddress1~PEGuaranteeAddress2~PEGuaranteeAddress3~PEGuaranteeCity~PEGuaranteeCountry~PEGuaranteeName~PEGuaranteePhoneNum~PEGuaranteeState~PEGuaranteeTaxID~PEGuaranteeZip~PEInterest~PENoPayPenalty~PEReasonCode~PEReasonDesc~PERefDocID~PESUNATDepAmt~PESUNATDepDate~PESUNATDepNum~PIPayment~Plant~PMUID~PONum~PostedRecog~RateGrpCode~ReadyToBill~ReadyToCalc~RealEstateNo~RecalcBeforePost~RecurBalance~RecurringState~RecurSource~RefCancelled~RefCancelledBy~ReversalDocAmount~RevisionDate~RevisionNum~Rounding~Rpt1DepGainLoss~Rpt1DepositAmt~Rpt1DepUnallocatedAmt~Rpt1MaxValueAmt~Rpt1PECharges~Rpt1PECommissions~Rpt1PEDischargeAmt~Rpt1PEInterest~Rpt1PENoPayPenalty~Rpt1RecurBalance~Rpt1WithholdAmt~Rpt2DepGainLoss~Rpt2DepositAmt~Rpt2DepUnallocatedAmt~Rpt2MaxValueAmt~Rpt2PECharges~Rpt2PECommissions~Rpt2PEDischargeAmt~Rpt2PEInterest~Rpt2PENoPayPenalty~Rpt2RecurBalance~Rpt2WithholdAmt~Rpt3DepGainLoss~Rpt3DepositAmt~Rpt3DepUnallocatedAmt~Rpt3MaxValueAmt~Rpt3PECharges~Rpt3PECommissions~Rpt3PEDischargeAmt~Rpt3PEInterest~Rpt3PENoPayPenalty~Rpt3RecurBalance~Rpt3WithholdAmt~SEBankRef~ServiceInvoice~ShipDate~SiteCode~SoldToCustNum~SoldToInvoiceAddress~SummarizationDate~SysRevID~SysRowID~TaxPoint~TaxRateDate~TaxRateGrpCode~TaxRegionCode~THIsImmatAdjustment~TotFinChrg~TranDocTypeID~TWDeclarePeriod~TWDeclareYear~TWGenerationType~TWGUIExportDocumentName~TWGUIExportRemarks~TWGUIExportVerification~TWGUIGroup~TWGUIRegNumBuyer~TWGUIRegNumSeller~TWPeriodPrefix~UseAltBillTo~UseAltBillToID~WithholdAmt~WithTaxConfirm~XRefContractDate~XRefContractNum~Division_c~Calc_BillToAddressList~Calc_BottomAddress~Calc_BTAddressList~Calc_CHBankAcctIBANCode~Calc_CHBankAcctISRPartyID~Calc_CompanyAddressList~Calc_ConReference~Calc_ConStartDate~Calc_CounterARForm~Calc_CreditMemo~Calc_CurrDesc~Calc_CurrDocDecimalsGeneral~Calc_CurrDocDecimalsPrice~Calc_CurrDocDesc~Calc_CurSymbol~Calc_CustContactName~Calc_CustEMailAddress~Calc_CustFax~Calc_CustID~Calc_CustPartOpts~Calc_CustPhone~Calc_CustResaleID~Calc_DiscountTotal~Calc_DocDspTaxAmt~Calc_DocInvoiceAmt~Calc_DropShipPackSlip~Calc_DteOrdrd~Calc_ExtPriceTotal~Calc_fFOB~Calc_GuiFormatCodeDesc~Calc_InvoiceRefDate~Calc_InvoiceRefLegalNumber~Calc_InvSubTotal~Calc_InvSuffix~Calc_IsBaseCurr~Calc_IsDraftCopy~Calc_MscTotal~Calc_MtlTotal~Calc_MulPackNum~Calc_mulponum~Calc_MulShipDate~Calc_MulShipTo~Calc_MulShipVia~Calc_MulShpCon~Calc_MultSoldTo~Calc_MultTaxID~Calc_MXApprovalNumber~Calc_MXApprovalYear~Calc_MXCBB~Calc_MXCertificateSN~Calc_MXCertifiedTimeStamp~Calc_MXDigitalSeal~Calc_MXExpeditionAddress~Calc_MXFiscalFolio~Calc_MXFolio~Calc_MXOrigFiscalFolio~Calc_MXOrigFiscalFolioAmount~Calc_MXOrigFiscalFolioDate~Calc_MXOrigFiscalFolioSeries~Calc_MXOriginalString1~Calc_MXOriginalString10~Calc_MXOriginalString2~Calc_MXOriginalString3~Calc_MXOriginalString4~Calc_MXOriginalString5~Calc_MXOriginalString6~Calc_MXOriginalString7~Calc_MXOriginalString8~Calc_MXOriginalString9~Calc_MXOriginalStringTFD~Calc_MXPaidAs~Calc_MXPaymentAccountNum~Calc_MXPaymentMethod~Calc_MXPaymentNum~Calc_MXSATCertificateSN~Calc_MXSATSeal~Calc_MXSerie~Calc_MXTaxRegime~Calc_MXTotalPayments~Calc_NumRecordPerPage~Calc_NumTotalParts~Calc_PackNum~Calc_PELegalText~Calc_PeriodEndDate~Calc_PPAllowPcnt~Calc_PrintBottomAddress~Calc_RefInvAmt~Calc_RefInvNum~Calc_RevisionDate~Calc_RevisionNum~Calc_RUBTLegalName~Calc_RUCorrToInvDate~Calc_RUCorrToInvLegalNumber~Calc_RUCorrToInvRevDate~Calc_RUCorrToInvRevNum~Calc_RULessDeposits~Calc_RusNumber~Calc_SalesPerson~Calc_SalesTerms~Calc_SEBankRef~Calc_ShipDate~Calc_ShipTo~Calc_ShipToAddressList~Calc_ShipToContactName~Calc_ShipVia~Calc_ShpConNum~Calc_SoldToAddressList~Calc_SubTotal~Calc_TaxMethod~Calc_TotalMiscChrg~Calc_vDocPayAmounts~Calc_VoucherString~Calc_Voucher-String~Calc_vPayAmounts~Calc_vPayDates~CurrencyCode_CurrDesc~CurrencyCode_CurrName~CurrencyCode_CurrSymbol~CurrencyCode_DocumentDesc~Customer_BTName~Customer_CustID~Customer_Name~Customer_TradingPartnerName~FOB_DeliveryTerms~FOB_Description~Plant_Address1~Plant_Address2~Plant_Address3~Plant_City~Plant_Company~Plant_CountryNum~Plant_Name~RMANum_RMADate~RMANum_RMANum~RMANum_ShipToNum~RMANum_XRefRMANumber~TermsCode_Description~TermsCode_NumberOfPayments~TermsCode_TermsCode~

If I put this line in a file by itself and try to import it into Excel, the option to select the delimiter disappears (not disabled, it's gone from the UI completely) and it's treated as a single column. OO Calc imports the single line or the complete file with no issue.

  • 1
    Are you sure they're being truncated and it's not blank columns being treated as one column? Check to make sure you don't have 'Treat consecutive delimiters as one' checked in the Text Import Wizard. – angelofdev Jun 20 '18 at 21:40
  • Office 365, is hardly the cause, but I'm not using it so can't test. Or, were you able to import the file in other Excel versions? Anyway in earlier offices I can not reproduce the problem (tested with a hundred column file). – Tom Brunberg Jun 20 '18 at 22:01
  • @TomBrunberg It imports correctly in OpenOffice Calc. – StackOverthrow Jun 20 '18 at 22:02
  • @angelofdev I used Data > From Text/CSV. There is no such option there. – StackOverthrow Jun 20 '18 at 22:06
  • 1
    Can you edit your answer to include a couple of lines of a tilde-delimited text file? (Open the file in a text editor and copy-paste the lines.) – robinCTS Jun 21 '18 at 2:36
1

From a little bit of testing it seems the Data > From Text/CSV doesn't prompt you to parse the file and split it out into columns (without another manual step).

I've tested this method and it works with Excel 365 (version 1805; thanks for the note on the Browse button):

  1. In Excel, navigate to File > Open
  2. Select Browse so the file selection dialog pops up
  3. Change the File Type drop down from All Excel Files to "All Files (*.*)"
  4. Navigate to and select your tilde-delimited file

At this point the Text Import Wizard should display

  1. In the Original Data Type section ensure Delimited is selected and click Next
  2. In the delimiters section uncheck Tab and check Other; type ~ into the box next to Other, then click Next

Other comments mentioned the 'Treat consecutive delimiters as one' option, it is found on this screen. Leave it unchecked but if you don't get the results you are expecting, try again with this checked

  1. The 3rd and last step of the Text Import Wizard gives you a preview of the parsed data and lets you change columns' data types. Make any changes you'd like then click Finish
  2. You should see your data in an Excel worksheet as you'd expect with all the columns you were expecting.
| improve this answer | |
0

Try this short VBA macro:

Sub tildaReader()
    FilesToOpen = Application.GetOpenFilename
    Close #1
   Open FilesToOpen For Input As #1

   j = 1
   Do While Not EOF(1)
        Line Input #1, TextLine
        arr = Split(TextLine, "~")
        Cells(j, 1).Resize(1, UBound(arr) + 1).Value = arr
        j = j + 1
   Loop

   Close #1
End Sub

typical file:

enter image description here

result:

enter image description here

EDIT#1:

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

enter image description here

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from the Excel window:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

| improve this answer | |
  • Everything MS is completely alien to me. Can you explain where that code snippet would go? – StackOverthrow Jun 21 '18 at 16:59
  • @TKK See my EDIT#1 – Gary's Student Jun 21 '18 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.