+ Reply to Thread
Results 1 to 6 of 6

Trying to import a text file that contains commas and carraige returns within the records

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Trying to import a text file that contains commas and carraige returns within the records

    Good Afternoon,

    I'm brand new here and it's been a long time since I programmed in VBA.

    I'm trying to import a text file where one of the fields contains commas as well as carraige return within the field but the fields have double qoutes around them.


    Example of the data:

    Name, ID, message
    "John Smith","123456","Dear Mr. Smith, you are missing a, b, and C. ^p please reach out to us ^p. Thank you, Student Services^p"|
    "Tom Smith","123333","Dear Mr. Smith, you are missing X, and. ^p please reach out to us ^p. Thank you, Student Services^p"|
    "Jack Miller","145333","Dear Mr. Miller, you are missing G. ^pPlease reach out to us ^p. Thank you, Student Services^p"|

    I managed to make the entire row delimited with a pipe to help matters

    I have some code that's probably a discrace to anyone who know what they're doing. I'd greatly appreciate if someone can help me out. I am assuming there are 2000 rows in the file.

    =============================================
    Sub Import2()

    Dim Sh As Worksheet
    Dim FileName As String
    Dim FileNum As Integer
    Dim Data As String
    Dim Arr As Variant
    Set Sh = Worksheets("Sheet1")
    FileName = "C:\temp\MIL Data\Friday\MILDataFile.txt"
    FileNum = FreeFile
    Open FileName For Input As #FileNum
    Cells.ClearContents

    For i = 1 To 2000
    Line Input #FileNum, Data
    Arr = WorksheetFunction.Transpose(Split(Data, ","))

    Sh.Cells(1, i).Resize(UBound(Arr)).Value = Arr

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="|"

    Next i

    Close #FileNum
    ====================================
    Greatly appreciate any help.

    Thank you,
    Mike

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Trying to import a text file that contains commas and carraige returns within the reco

    If you open the file as a CSV with comma delimiters and Quotation marks as text qualifiers it should open the file ok.
    You can use the macro recorder to record the code.
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Trying to import a text file that contains commas and carraige returns within the reco

    Quote Originally Posted by AndyLitch View Post
    If you open the file as a CSV with comma delimiters and Quotation marks as text qualifiers it should open the file ok.
    You can use the macro recorder to record the code.
    Thanks for your response Andy, but unfortunately I spent hours pursuing that path to find out that Excel has it's share of quirks. The summary section is very long and for some reason the quotation/text qualifiers is not working. I also want to change the end of record/line delimiter - which you can only do using VBA.

    Any other help would be great.

    Thanks,

    Mike

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Trying to import a text file that contains commas and carraige returns within the reco

    Try this

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-26-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Trying to import a text file that contains commas and carraige returns within the reco

    Thank you Andy,

    I did try it and appears that it's blowing up as my input/txt file is too large (1.2mb)

    I did break up my file and I'm peicing apart the code you sent. I have to do some teaking with the delims too.

    It's a great help, thank you very much and I'll keep you posted as I figure out how to take in a larger file.


    Mike

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Trying to import a text file that contains commas and carraige returns within the reco

    It should handle a 1.2Mb file ok ....... The input/output variables are strings ... Hmmmm

    but try this

    Please Login or Register  to view this content.
    Last edited by AndyLitch; 04-29-2013 at 12:28 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1