Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Truncate a csv (97-->)

    I sometimes get csv downloads that are in excess 65536 lines. As you will see I cannot open the full set in Excel, my preferred application for quality checking of data. The problem is this data download varies in number of records and I am not sure if it exceed the 65536 no and then it cuts off and I cannot see the final part of the data. Is there away I could truncate this file for view when it hits the limit?

    If push comes to shove I could import it to Access, but that is just another step process.
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Truncate a csv (97-->)

    Your chainsaw should do the job nicely... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Truncate a csv (97-->)

    More seriously, see these posts by Steve: <post#=396487>post 396487</post#> and <post#=321190>post 321190</post#>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    Does the routine in <post#=396487>post 396487</post#> help you?

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Thanks Steve

    I am running the code at the moment and it is taking a very long time. As I look at the CPU time it is reaching 9 mins. The download is a particularly long one, is this normal and should I be more patient?
    Jerry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    I have never tried it with a real large file so do not how long it may take. My tests were all with smaller files.

    You might try it with a smaller file and see how it goes...

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Thanks Steve

    It worked fine, eventually. I think it was a case of me doubting myself too much after I wrote the code [make note to myself: "Trust Steve, Trust yourself more"]
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Hi Steve

    I know this is quite an old post, but I have had to revisit this after a gap in time. I have run the code you suggest for a sample set of data.

    When I open the csv file for a quick view( obviously it won't open fully because it is out of range) and look at the lay out of the data it looks fine. When I run this code fron an excel module it drops in a long string in the in column A of the spreadsheet which is the concatanated string with all the commas and speach marks...I was hoping that the data would be placed in each ceel as deoicted in the csv file format. Can you advise please?
    Jerry

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    It should be separated into columns after it runs the text-to-columns conversion.

    How does it "fail"?

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Hi Steve

    I run the code and it imports to column A but in, say, cell A1 you get

    "1","123 Acaicia Ave","London","SW5 5FG","England" etc down through column A for each record.

    It is basically picking up each line of the csv file and dumping all the string into the cell and not formatting to see the commas as new cells and the speech marks as strings....
    Jerry

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    Could you attach a CSV example file that does not work?

    I created one with your example text ran the code from <post#=396487>post 396487</post#> and it parsed the data as expected into separate columns.

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Sadly not, I am at home at the mo. One thing I have not clarified though is I am running it on XP at work, would that make a difference to the way the code works?

    I have another thing that may have caused the problem and it was an ID Ten T moment, just before I was leaving work I stripped out a load of char(13) characters from the strings of data (which may have affected the code read it). This was after I wrote to this thread. I will test it again tomorrow with a fresh head and get back to you with the example if necessary.
    Jerry

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    I don't think that should matter.

    Steve

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Truncate a csv (97-->)

    Just to update you Steve.

    In the fresh light of day, I ran this code and it went brilliantly.

    The legacy system I was working with had a char(13) characters tucked up in one particular field ( they were to create a message box with line feeds to display an address in a nice format!!)

    When I was running your code with them in Excel was line feeding the string to various lines and at one point missing loads of lines as there were about 4 at the end of some, the page could not format itself correctly with them in!!!!

    Anyway, the long and short of it is that it works. I had approx. 170,000 records to import and this took the time it takes to make a cup of coffee, so quite impressive, thanks
    Jerry

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Truncate a csv (97-->)

    Glad you got your puzzle solved and I was happy to help.

    Steve

Posting Permissions

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