MisAligned .CSV conversion

Topic

New Reply

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!
I will attach both files if requested.
Thanks a Million.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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]).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

How would you know the correct order for column A with respect to the rest of the columns?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

The formulas in the .XLT that convert from the .CSV
Column A’s formula =IF(NOT(ISBLANK(mold_loc.csvI47)),TEXT(Mold_loc.csv!I47,””),””)
Columm B’s formula =mold_loc.csv!J82
Column C’s formula =IF(NOT(ISBLANK(mold_loc.csvK82)),TEXT(Mold_loc.csv!K82,””),””)
Column D’s Formula =IF(NOT(ISBLANK(mold_loc.csvL82)),TEXT(Mold_loc.csv!L82,””),””)
with columns E to G being the same sequence.

Reply | Quote

Under “view code” there is nothing. How would I post just the code for you.

Reply | Quote

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?

Reply | Quote

I could find no module so the formulas must be doing the work. As to why they dont line up? As Redmond! the formulas and Data are not so special they are the ‘rack location’ of molds so the mold changers know wher to look for them. the next columns are ‘mold number’, ‘customer’, ‘customer part number’, ‘propery part number, and ‘description’.

Reply | Quote

The cell #’s are not special, I just took the order that I tabed from.

Reply | Quote

Did you ever try to copy the contents (only the values, using paste special >> values) of the cells and then reorder them? Or did you reorder the original formulas (not the values or content of the cells) in the template and saved it as a template, that is, did you overwrite the original template after ordering it in the wrong way? In that case, Legare is right, there is no way to get the original order back in place.

Reply | Quote

That about sums it up. The .XLS is a template. Why it was created that way I do not fully understand. Especially since the end user wants everything in order by column A here , and Other colums for other reports (7 in all).
I do really appreciate all of your ideas and help.
To ya’ll

Reply | Quote