+ Reply to Thread
Results 1 to 5 of 5

Import Text file with more than one text separator to excel

  1. 10-01-2017, 08:45 PM #1
    bennyys
    bennyys is offline
    Registered User
    Join Date
    07-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    41

    Import Text file with more than one text separator to excel

    Hi,
    I am working on input text file in to excel.
    I got code from Cpearson to this purpose

    Please Login or Register  to view this content.
    Vba Code for calling ImportTextFile

    Please Login or Register  to view this content.
    My question is if I want to import textfile with more than one separator character, in my case "tab"(chr(9) ascii) and "space"(chr(32) ascii)?
    I guess that this part need to modify but so far I failed it
    Please Login or Register  to view this content.
    Thank you in advance for all your help.

    Regards,
    Benny
    Reply With QuoteRegister To Reply
  2. 10-03-2017, 02:57 PM #2
    Tsjallie
    Tsjallie is offline
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Import Text file with more than one text separator to excel

    To assign tab+space to the variable try this:
    Please Login or Register  to view this content.
    You can't do it with an inputbox. That is, not with the correct result.
    Cheers!
    Tsjallie



    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...
    Reply With QuoteRegister To Reply
  3. 10-03-2017, 10:25 PM #3
    bennyys
    bennyys is offline
    Registered User
    Join Date
    07-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Import Text file with more than one text separator to excel

    Hi Tsjallie,
    Thank you for your reply.
    I try your suggestion on using 
    Please Login or Register  to view this content.
    I am using chr(32) as replacement to your chr(20) on my case. BUT it seems not working.

    here is the input file I am using right now

    2017-09-20 12:11:42.500 1.444 1534.204 1.453 23.713 54.950 37.462 1025.598
    2017-09-20 12:11:43.000 2.204 1534.030 2.218 23.696 54.924 37.456 1025.603
    2017-09-20 12:11:43.500 2.929 1533.839 2.948 23.683 54.954 37.490 1025.635
    2017-09-20 12:11:44.000 3.451 1533.806 3.473 23.679 54.960 37.498 1025.645
    2017-09-20 12:11:44.500 3.783 1533.766 3.807 23.670 54.960 37.505 1025.655
    2017-09-20 12:11:45.000 4.066 1533.743 4.092 23.658 54.924 37.488 1025.646
    2017-09-20 12:11:45.500 3.912 1533.742 3.937 23.654 54.966 37.524 1025.674
    2017-09-20 12:11:46.000 3.764 1533.743 3.788 23.650 54.968 37.528 1025.678
    2017-09-20 12:11:46.500 3.693 1533.735 3.716 23.652 54.986 37.541 1025.686
    2017-09-20 12:11:47.000 3.665 1533.735 3.688 23.645 54.958 37.525 1025.676
    Just additional info for you.
    1. If I use only Tab (chr(9))
    Please Login or Register  to view this content.
    I will get this follow on excel(just left date and time together in colum A but in excel only display as date but actually the full content was something like 20/09/2017 12:11:42)


    09/20/17 1,444 1534,204 1,453 23,713 54,95 37,462 1025,598
    09/20/17 2,204 1534,03 2,218 23,696 54,924 37,456 1025,603
    09/20/17 2,929 1533,839 2,948 23,683 54,954 37,49 1025,635
    09/20/17 3,451 1533,806 3,473 23,679 54,96 37,498 1025,645
    09/20/17 3,783 1533,766 3,807 23,67 54,96 37,505 1025,655
    09/20/17 4,066 1533,743 4,092 23,658 54,924 37,488 1025,646
    2. if I use only Space(chr(32))
    Please Login or Register  to view this content.
    I will get in excel as follow
    09/20/17 "12:11:42.500 1.444 1534.204 1.453 23.713 54.950 37.462 1025.598"
    09/20/17 "12:11:43.000 2.204 1534.030 2.218 23.696 54.924 37.456 1025.603"
    09/20/17 "12:11:43.500 2.929 1533.839 2.948 23.683 54.954 37.490 1025.635"
    09/20/17 "12:11:44.000 3.451 1533.806 3.473 23.679 54.960 37.498 1025.645"
    09/20/17 "12:11:44.500 3.783 1533.766 3.807 23.670 54.960 37.505 1025.655"
    09/20/17 "12:11:45.000 4.066 1533.743 4.092 23.658 54.924 37.488 1025.646"
    3. If I use your suggestion
    Please Login or Register  to view this content.
    I will get as follow in excel(just import everything in column A)
    "2017-09-20 12:11:42.500 1.444 1534.204 1.453 23.713 54.950 37.462 1025.598"
    "2017-09-20 12:11:43.000 2.204 1534.030 2.218 23.696 54.924 37.456 1025.603"
    "2017-09-20 12:11:43.500 2.929 1533.839 2.948 23.683 54.954 37.490 1025.635"
    "2017-09-20 12:11:44.000 3.451 1533.806 3.473 23.679 54.960 37.498 1025.645"
    "2017-09-20 12:11:44.500 3.783 1533.766 3.807 23.670 54.960 37.505 1025.655"
    "2017-09-20 12:11:45.000 4.066 1533.743 4.092 23.658 54.924 37.488 1025.646"
    "2017-09-20 12:11:45.500 3.912 1533.742 3.937 23.654 54.966 37.524 1025.674"
    "2017-09-20 12:11:46.000 3.764 1533.743 3.788 23.650 54.968 37.528 1025.678"
    TEMPORARY solution from my side as of now is
    1. Import using TAB separator at first run
    Please Login or Register  to view this content.
    By the end of this step I will get everything in separate column EXCLUDED column A where date and time still in the same column
    2. Move column B and the rest 2 column to the right then running "textToColumn" on column A

    Regards,
    Benny
    Reply With QuoteRegister To Reply
  4. 10-04-2017, 03:57 PM #4
    Tsjallie
    Tsjallie is offline
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Import Text file with more than one text separator to excel

    Seeing the input data the problem becomes clear.
    The file is tab delimited. That's why you get the best result with chr(9) as separator.
    Date and time however is one data field where date and time are separated by a space.

    If you only want to see the date then you can solve that by formatting the column as yyyy-mm-dd.

    Or, if you want to (visually) split date and column you can import the file starting in column B.
    Then enter a formula in column A which just copies column B.
    Finally you can format column A as yyyy-mm-dd and column B as hh:mm:ss.

    But of course you're solution to (physically) split the column also works. It's just a little more work.

    Sorry abt the chr(20). That's the hex equivalent of chr(32). Keep mixing up these systems
    Reply With QuoteRegister To Reply
  5. 10-13-2017, 11:25 PM #5
    bennyys
    bennyys is offline
    Registered User
    Join Date
    07-25-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Import Text file with more than one text separator to excel

    Hi Tsjallie,
    Thank you for your suggestion, I will try using your approach and compare it with what I got now and see which one running faster.

    Regards,
    Benny
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Export to the text file with space separator
    By mgecelov in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-07-2017, 11:31 AM
  2. [SOLVED] Export specific range to text file and import the text file if needed
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-06-2015, 07:38 AM
  3. Convert Columns to text file with a separator of |
    By Mquattrone in forum Excel General
    Replies: 8
    Last Post: 08-14-2014, 11:14 PM
  4. Import text file into Excel and using a loop to count unique words within the text
    By mrgriff21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2013, 03:17 PM
  5. Macro to Parse Text - Import text to Excel from Multiple Text Files & Folders
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2012, 01:05 AM
  6. [SOLVED] Import text file from VBA code; import all numbers as text
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-19-2012, 05:19 PM
  7. VBA code to import text file to the fixed excel file
    By wangdian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2010, 10:13 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1