Hi,
I'm willing to delete trailing spaces in my file, but my file is huge: 15 col, 1 million lines. I know it's crazy, it will go very soon in a stat software.
For column A try this...
I found that code, but only worked for col A: what can I do ?
Also I found that:HTML Code:
But it does not work.HTML Code:
I have often a problem of capacity (error 6), since the file is huge.
Someone got an idea for that ?
thanks a lot,
Aya
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Hi Aya
Have no idea if this will work on that much Data but you can try this...
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Hi! thanks for your macros
it works, but not for all the file, at a certain point, excel get blocks. MAc says: excel does not respond. So I stop it
weird, isnt ?
Should I wait even 30min when it says "excel does not answer" ?
Hi Aya
If the message is "Excel is not responding", it's my experience that it doesn't mean it's not working. You have a LOT of cells that are being massaged.
Do ALL Columns require trimming or only certain Columns? If ALL Columns it may take hours to complete...if certain Columns perhaps we can address that and speed things up.
Hi Aya & jaslake,
How about:
Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
I see
it's interesting. I think i'll wait. Now i'm not in my home town, in my home town i usually use Kutools for PC and it's very quick. For the moment, I'll make a test tomorrow letting your macro (which works well) acting on the file for 1 or 2 hours. I'm sure it will work.
The problem is that I'll receive lots of files, with maj and accents and trailling issues, somethines in 10 columns, sometimes in 3, so it will depend....
Anyways i'm happy that you said that:
HTML Code:
thanks! i'll let u know!
15 columns and 1m rows is a no-go area for a loop, even an array loop will struggle. Perhaps, you can divide your data in to manageable rows and once the data are clean, you can re-merge them in to a single sheet. But there is a fundamental issue which you need to address. Why are using excel up to 1m rows? Excel is not designed for storing data, not surely for a string type of data. My guess is these data are probably imported from another system.
Good catch Winon...wrote the Code on the fly...should have added Calculation and Screen Updating to the Code.
Hi Aya
Are they ALWAYS the SAME 3 Columns (or 10 Columns)?
Hi !
These are data on corporations provided by some central banks. i'm sure these data are in CSV or .dta originally, and they exported them to excel in order to send me. And indeed it will be an issue because they will continue sending me such datas.
Before, the data were divided by year in several worksheets, 30 in one of the files. I merged them all together before beginning to clean the accents etc.
And no it will not always be the same columns i guess. Usually the problematic columns are the one with the sector, that's all.
What other softwares and process options would you recommend to clean these data ?
The final objective is actually to put that in Stata, but before, i have to clean the data, and probably do a lot of SearchV to add infos to these data. And wel, sometimes search something in the excel fiel, but mainly use stata.
Last edited by Ayadin; 08-11-2016 at 04:04 AM.
If it is a csv file, it would be better/faster to edit csv directly then open it with Excel.
Ok. What I can do is to transfer excel to csv, edit, and go back to excel ? What do you think ?
How do we edit a csv ? With the simple replace tool ?
You have answered my question. Just clean the data before you merge them.
This will create a new csv file, so you can open it from excel.
Please Login or Register to view this content.
Hi created the csv file using "register as csv file" actually. Is it the same ?
I don't understand what you mean.Originally Posted by Ayadin
If you run the code and select the CSV file that you want to trim, it will create a new csv file in the same folder under the name of
"YourFileName_Trimmed.csv".
Where "YourFileName" is from original file name.
Then you can open it from Excel.
Is it lighter to open that csv fle in excel than to work with the former excel file i got ?
Is there any automation i can use to edit the csv file ?
OOps, 2011...
You are on Mac, so my code will not work.
Sorry.
it's fine, i have a simpler way to make the csv file!
Should i now open the csv file in excel to edit it ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks