Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MisAligned .CSV conversion

    I have data I need to manipulate. It comes from MaintenceMate V5.1 is converted to a .csv file then a routine run to make it a .xls file (in Excel 2k). Back when I was first assigned the task, I was to order things by column A in alphabetical order. I did and it only moved column A leaving B through G as imported. There are over 625 rows of data. I need to get column A back in line with the rest of the data then re alphabetize.
    Help! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    I will attach both files if requested.
    Thanks a Million.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    You have to select all columns A to G and order according to column A, not just select A. Or do I miss something here?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    That is what I should have done last March. Unfortunately, I sorted by Row A and then saved that file. Now the file Imports into the wrong order. I have tried to shrink the file without losing integrity. It is over 400K and butchered up is still just under 200k so I cannot attach it.
    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    Can't you import the original CSV fle once more? From a backup tape? Otherwise, I am afraid I cannot help you. I don't think there is a way to reorder or undo your ordering step?

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    I have re-imported. Apparently when I saved the file, whatever VB code that organized it in the first place was altered. The code is in the XLS file, it somehow does a import from .csv conversion. I know no VB (strange that I have been in computers sine '82 and still know no VB [ I am a hardware guy]).
    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: MisAligned .CSV conversion

    How would you know the correct order for column A with respect to the rest of the columns?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    This, as Catharine said, - of course - is essential to solve your problem. The code can be found in the VB editor (via tools >> macro >> Visual Basic Editor). Maybe you can copy and post it.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    Did you by any chance save the sorted file back to the .CSV file so that now the .CSV file has the incorrect data in it?

    Sorting and then saving the .XLS file would not have changed the VBA code. If it is not doing things correctly, then you have incorrect data comming in, the code is operating incorrectly because of the data that is in the sheet, or something else has modified the code.

    If you sorted the data and then saved the file, there really is no way to get the original order back if you don't have a backup.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    When I open the .CSV file it appears in order. The way I can tell the data is messed up is the reference in the .XLT file. The .CSV data is "Pure" the .XLS data in column A looks like this {=IF (NOT(ISBLANK(mold_loc.csv!I47)),TEXT(Mold_loc.csv! I47,""),"")} and the data in column B looks like this {=mold_loc.csv!J82} column D through G are similar to A but have the location as _82 where _ is K to O accordingly. I have added the {} for you. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    The formulas in the .XLT that convert from the .CSV
    Column A's formula =IF(NOT(ISBLANK(mold_loc.csvI47)),TEXT(Mold_loc.cs v!I47,""),"")
    Columm B's formula =mold_loc.csv!J82
    Column C's formula =IF(NOT(ISBLANK(mold_loc.csvK82)),TEXT(Mold_loc.cs v!K82,""),"")
    Column D's Formula =IF(NOT(ISBLANK(mold_loc.csvL82)),TEXT(Mold_loc.cs v!L82,""),"")
    with columns E to G being the same sequence.
    <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    I opened the "editor" and on the Left of the screen is "Project-VBAProject" with two projects under it. "VBAProject (Mold_loc.csv)" and "VBAProject (Mold_loc.xls)". Under them are properties, BUT!!! I know not what they mean all wise ones. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    Under "view code" there is nothing. How would I post just the code for you. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    OK, if you sorted a column with formulas, there is no way I know of to get that column back in the original order except to manually recreate it. In addition, it looks like those formulas reference cells on another sheet and probably in another workbook. The last time I tried sorting data that contained relative formulas referencing cells on other sheets, I ended up with a mess. I am not sure what Excel did with the formulas, but I did not get what I expected.
    Legare Coleman

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    Maybe there is no code. Therefore, you have to see if there is a module in the VBAproject - Mod_Loc.csv or Mod_loc.xls - and then select 'code'. If there is no code, the formulas in the template are doing the job. This looks as a real puzzle: what is special about the cells A47 and A82?

  15. #15
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Nixa, Missouri, USA
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MisAligned .CSV conversion

    I believe I am going to go and edit each cell in column A to have the correct cell reference. at 625+ I was hoping I didn't do something too not so smart.
    oops, Oh well! <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26>

Page 1 of 2 12 LastLast

Posting Permissions

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