Help/advice needed :-)
Quote from dglojnar on August 14, 2020, 6:41 pmSo, I have Excel files and some data are in wrong place - see attachment.
How can I move data from row 129,131,133 (all cells) etc. to empty cell in rows 128, 130,132 etc..
Unfortunatelly, this is not a pattern, some data have an extra empty row:-(
Can I do this with NeoBookDBPro or Neobook/VisualNeo ?
Original file is TAB delimited TXT file.
Any help/advices are welcome:-)
Best regards to all:-)
So, I have Excel files and some data are in wrong place - see attachment.
How can I move data from row 129,131,133 (all cells) etc. to empty cell in rows 128, 130,132 etc..
Unfortunatelly, this is not a pattern, some data have an extra empty row:-(
Can I do this with NeoBookDBPro or Neobook/VisualNeo ?
Original file is TAB delimited TXT file.
Any help/advices are welcome:-)
Best regards to all:-)
Uploaded files:
- You need to login to have access to uploads.
Quote from Gaev on August 14, 2020, 7:22 pm@dglojnar
How can I move data from row 129,131,133 (all cells) etc. to empty cell in rows 128, 130,132 etc..
It is not very clear (from looking at the screen shot) ... assuming we are looking at columns A, B, C and D ...
1) do you want to move (not copy) the contents of cell B129 to D128 ?
2) what do you want to do with C129 and D129 ?... and similarly for contents of cell B130, B131 etc. etc.
If I am mistaken, please express with similar example.
Unfortunatelly, this is not a pattern, some data have an extra empty row:-(
Can you show an example... and what action needs to be taken ?
How can I move data from row 129,131,133 (all cells) etc. to empty cell in rows 128, 130,132 etc..
It is not very clear (from looking at the screen shot) ... assuming we are looking at columns A, B, C and D ...
1) do you want to move (not copy) the contents of cell B129 to D128 ?
2) what do you want to do with C129 and D129 ?
... and similarly for contents of cell B130, B131 etc. etc.
If I am mistaken, please express with similar example.
Unfortunatelly, this is not a pattern, some data have an extra empty row:-(
Can you show an example... and what action needs to be taken ?
Quote from dglojnar on August 14, 2020, 7:46 pmHi Gaev, thank you for answer :-)
Yes, we are looking columns A,B,C and D
Complete row 129 (start with cell B129,C129,D129 ...) have to be moved on row 128 start with cell D128.
That mean B129 to D128, C129 to E128 etc...
In this case, all contents from other cells (B129,C129,D129...) fit correctly in rest of table.
So I have actually move content of complete row 129,131,133 etc... 2 cells on the right and one row up.
The other pattern is similar to this one except start with completely empty row and then come the data as described.
Here's the part of the file in the attachment - first 2 rows are correct, other rows have to look same as first 2 rows, the other pattern start at row 76.
Please rename TXT to XLSX.
I hope I clearify a little bit :-)
Regards!
Hi Gaev, thank you for answer :-)
Yes, we are looking columns A,B,C and D
Complete row 129 (start with cell B129,C129,D129 ...) have to be moved on row 128 start with cell D128.
That mean B129 to D128, C129 to E128 etc...
In this case, all contents from other cells (B129,C129,D129...) fit correctly in rest of table.
So I have actually move content of complete row 129,131,133 etc... 2 cells on the right and one row up.
The other pattern is similar to this one except start with completely empty row and then come the data as described.
Here's the part of the file in the attachment - first 2 rows are correct, other rows have to look same as first 2 rows, the other pattern start at row 76.
Please rename TXT to XLSX.
I hope I clearify a little bit :-)
Regards!
Uploaded files:- You need to login to have access to uploads.
Quote from Gaev on August 14, 2020, 10:49 pm@dglojnar
I hope I clearify a little bit :-)
Yes, very well; thank you.
Please rename TXT to XLSX.
I did that ... and then uploaded the file to Google Sheets (I stopped using MS Excel about 25 years ago).
Complete row 129 (start with cell B129,C129,D129 ...) have to be moved on row 128 start with cell D128.
That mean B129 to D128, C129 to E128 etc...
In this case, all contents from other cells (B129,C129,D129...) fit correctly in rest of table.So I have actually move content of complete row 129,131,133 etc... 2 cells on the right and one row up.
There are two ways of doing this ...
1) Use intermediary CSV files
a) Export your sheet to a CSV file
b) Read the file into a NeoBook variable (e.g. [rowsdata])
c) Split [allData] into an array of lines ([rowsdata1], [rowsdata2] etc.)
d) Loop through the lines, with each iteration of the loop ...
- split line data into array of columns/fields
- make the desired changes to the columns in the current and previous rows
e) merge the array of lines
f) write the data back to a CSV file
g) import the contents of the CSV file back into ExcelThis will work; but may take long if there are thousands of rows in your sheet ... can you tell approximate number of rows in sheet ?
2) Use neoDBPro and open the sheet (as a DB Table) ... more direct, but I have not used excel sheets as database Tables myself ... otherwise similar similar technique of looping through the rows (records).
The other pattern is similar to this one except start with completely empty row and then come the data as described.
Here's the part of the file in the attachment - first 2 rows are correct, other rows have to look same as first 2 rows, the other pattern start at row 76.So, when there is an empty row (like 76), you want the content from row 77 to be moved to row 75 ?
I hope I clearify a little bit :-)
Yes, very well; thank you.
Please rename TXT to XLSX.
I did that ... and then uploaded the file to Google Sheets (I stopped using MS Excel about 25 years ago).
Complete row 129 (start with cell B129,C129,D129 ...) have to be moved on row 128 start with cell D128.
That mean B129 to D128, C129 to E128 etc...
In this case, all contents from other cells (B129,C129,D129...) fit correctly in rest of table.So I have actually move content of complete row 129,131,133 etc... 2 cells on the right and one row up.
There are two ways of doing this ...
1) Use intermediary CSV files
a) Export your sheet to a CSV file
b) Read the file into a NeoBook variable (e.g. [rowsdata])
c) Split [allData] into an array of lines ([rowsdata1], [rowsdata2] etc.)
d) Loop through the lines, with each iteration of the loop ...
- split line data into array of columns/fields
- make the desired changes to the columns in the current and previous rows
e) merge the array of lines
f) write the data back to a CSV file
g) import the contents of the CSV file back into Excel
This will work; but may take long if there are thousands of rows in your sheet ... can you tell approximate number of rows in sheet ?
2) Use neoDBPro and open the sheet (as a DB Table) ... more direct, but I have not used excel sheets as database Tables myself ... otherwise similar similar technique of looping through the rows (records).
The other pattern is similar to this one except start with completely empty row and then come the data as described.
Here's the part of the file in the attachment - first 2 rows are correct, other rows have to look same as first 2 rows, the other pattern start at row 76.
So, when there is an empty row (like 76), you want the content from row 77 to be moved to row 75 ?
Quote from dglojnar on August 14, 2020, 11:50 pm@Gaev
Thank you very much for quick answer :-)
Very good solution, I'm going to try :-)
Sheet is about 25.000 rows - yeah, I know, but time is not crucial, it will make all done through the night:-)
So, when there is an empty row (like 76), you want the content from row 77 to be moved to row 75 ?
Yes :-)
Many, many thanks for your help, time and effort , really mean a lot to me :-)
Best regards and have a nice weekend:-)
Thank you very much for quick answer :-)
Very good solution, I'm going to try :-)
Sheet is about 25.000 rows - yeah, I know, but time is not crucial, it will make all done through the night:-)
So, when there is an empty row (like 76), you want the content from row 77 to be moved to row 75 ?
Yes :-)
Many, many thanks for your help, time and effort , really mean a lot to me :-)
Best regards and have a nice weekend:-)
Quote from Gaev on August 15, 2020, 1:18 am@dglojnar
Sheet is about 25.000 rows - yeah, I know, but time is not crucial, it will make all done through the night:-)
Some Tips:
1) it might be better (and easier to code in VisualNEOWin) if you don't have to worry about "empty rows" ... here https://www.businessinsider.com/how-to-remove-blank-rows-in-excel ... is a short tutorial on how to do a mass delete of empty rows in excel ... suggest you delete the empty rows BEFORE doing the export to csv (or tab delimited) file.
2) To ensure you don't have to wait a day or two for the program to finish processing ...
- do the FileRead with the "All" lines option ... to read all lines into memory in one shot (then do StrParse to create an array of lines)
- combine all the updated line variables using the | character ... the do FileWrite with the "replace" option (better still, write to a new file)3) Goes without saying, before you replace the contents of your spreadsheet, backup (to another folder), backup (to a USB drive), backup (to the cloud) ... just in case :-))
Sheet is about 25.000 rows - yeah, I know, but time is not crucial, it will make all done through the night:-)
Some Tips:
1) it might be better (and easier to code in VisualNEOWin) if you don't have to worry about "empty rows" ... here https://www.businessinsider.com/how-to-remove-blank-rows-in-excel ... is a short tutorial on how to do a mass delete of empty rows in excel ... suggest you delete the empty rows BEFORE doing the export to csv (or tab delimited) file.
2) To ensure you don't have to wait a day or two for the program to finish processing ...
- do the FileRead with the "All" lines option ... to read all lines into memory in one shot (then do StrParse to create an array of lines)
- combine all the updated line variables using the | character ... the do FileWrite with the "replace" option (better still, write to a new file)
3) Goes without saying, before you replace the contents of your spreadsheet, backup (to another folder), backup (to a USB drive), backup (to the cloud) ... just in case :-))