Splitting a large file into two and mining the data

I'm after editing the heading but it doesn't seem to have changed. Sorry

can you please explain again what output would you like to have because it quiet confusing on my side, it is best if you have a sheet for the final output.

Hey James,

Thanks for replying. Basically I'd like to split the original excel sheet into 2 csv files. The first CSV file will have the columns B2:B11 as their headings with the following stipulations:
JUST the number in cell B2 (in this case 0714)
JUST the information that says MyAddress in cell B11.

The second CSV file will have all the info from cells A13:I13 with Column I JUST being blank and column J having the info from cell B7 from the original document.

The problem with this is that the original file generated may be 15 rows or 1500 rows, each file is generated differently. The first 11 rows will always have the same format, the 12th row will always be blank but the rows underneath it will contain different data (although will always remain the same number of columns)

Sorry about the caps and underlining, it's just habit!!!

Any help would be very much appreciated.

Originally Posted by mick02

Hello,

I've been scouring the Internet for a few days now trying to get a macro working with varying degrees of success. It's driving me mad and I'm really hoping that someone out there can help me out. I'm trying to write a macro that will ...
1. Take a .xls file
2. Split the file in two
3. Take cells B2:B11 and paste them in to a csv file (to be called Upload1.csv) using the rows as column headers (i.e. B2:B11 now become A1:J1)
4. Take cells A13-J13 downwards (this file will have different numbers of rows each time it's generated) and paste them into a different csv file (to be called Upload2.csv) populating all the rows (except all rows in column I which are to be left blank) and all rows in Column J which are to contain the info in cell B7 of the original document.
5. In cell B11 of the original document I must only take the information that says MyAddress:xxxxx and discard all the other info
6. I must also loop through the second document until I find the words Total and copy everything UNTIL that row into the new document.

I know this probably sounds pretty awkward but in practice it's not that difficult to understand, it's probably the way that I've explained it

So far I've managed to take the original document and split it, populate both CSV files but I'm having problems with looping the macro until it sees the word Total and discards it and also populating the appropriate rows with the info from B7 ... I don't know how to make it populate only the rows that have info on them.

I've attached the original Excel file that is generated and also my attempt at the 2 csv files. I've also written in RED what needs to go where in each document.

I'm really really hoping that someone can help me with this as I've been playing around with regular expressions and text to columns and VB Scripts for almost a week and I'm getting kind confused now

Any macro writing gurus out there that can help me???

Thanks in advance,

Mick

Hi,

I think the following two procedures do what you want. Incidentally I think you need to format the B3 Phone number cell as text. At the moment it's general, and since XL thinks it's a number, and since it can't display numbers >15 characters, it's showing an overflow.

You only need to run the first 'CreateCSV1' macro below since it automatically calls the second.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I wasn't sure whether there was an implication that some of the rows may be empty, and hence those shouldn't be copied. The macro above assumes there are no empty rows, but if there are a small modification would overcome this. If that's the case, then post back and confirm whether the order of the rows is significant. The easiest way to get rid of blank rows is just to sort the data, and if the original order is required after a sort, we'd have to make a temporary note in the file of the original order so that the dat could be sorted back.

HTH

Richard, thank you very much for the reply the macro is almost doing what I want it to do unfortunately the Create CSV1 macro is not giving me any oputput when I try to open it in Excel and when I open it in notepad it's all jumbled. (please see attached file)

I appreciate you taking the time out to help with this.

Many thanks again,

Mick

Originally Posted by mick02

Richard, thank you very much for the reply the macro is almost doing what I want it to do unfortunately the Create CSV1 macro is not giving me any oputput when I try to open it in Excel and when I open it in notepad it's all jumbled. (please see attached file)

I appreciate you taking the time out to help with this.

Many thanks again,

Mick

Hello Mick,

Try adding the ,FileFormat:=xlcsv to the back end of the following line.

Please Login or Register  to view this content.

Let me know if that cures it.

Rgds

Hey Richard, tried doing as you suggested but it made no difference. The file is being saved as Upload1.csv alright but there's no data in it, Upload2.csv is working perfectly though.

Any ideas?

Originally Posted by mick02

Hey Richard, tried doing as you suggested but it made no difference. The file is being saved as Upload1.csv alright but there's no data in it, Upload2.csv is working perfectly though.

Any ideas?

Hi Mick,

No, I'm puzzled at the moment since I don't experience the same problem. Admittedly I'm using a Mac, but I've never known this be a difference before and I must have saved dozens of these things.

Can you put a break on the 'ActiveWorkbook.SaveAs Filename:="Upload1.csv" line that saves the workbook so that the macro halts before saving. F9 will toggle the break on/off.

When it has halted check that the data has in fact copied into a new workbook which will still be open in memory. Assuming there is data, start the macro recorder and save that file manually as a csv file, then examine the code that the recorder gives. Assuming you can save it manually then we just need to see what the syntax is and incorporate that.

Let me know.

Rgds

Mick, im glad other people help you sort out this problem. I wasn't able to get back at you right away.
Anyways, with your problem, trying adding this

Please Login or Register  to view this content.

try using xlText instead of using the xlCSV, get back to us if it works fine.

Hey folks, thanks for the reply and apologies for not replying sooner.

Unfotunately those suggestions did not work for me. I'm still getting a blank page as my Upload1.csv however the Upload2.csv is working perfectly

Originally Posted by mick02

Hey folks, thanks for the reply and apologies for not replying sooner.

Unfotunately those suggestions did not work for me. I'm still getting a blank page as my Upload1.csv however the Upload2.csv is working perfectly

Hi Mick,

Did you try halting the macro and manually saving the file with the macro recorder as I suggested a couple of posts ago?
http://www.excelforum.com/showpost.p...2&postcount=10

Rgds

Hey Richard,

Apologies but yes I tried this. When the file is saved to memory it shows no information although it has the cells A1:J1 highlighted but nothing populates the cells.

Originally Posted by mick02

Hey Richard,

Apologies but yes I tried this. When the file is saved to memory it shows no information although it has the cells A1:J1 highlighted but nothing populates the cells.

Mick,

I suppose the question I'm asking is, does the new workbook that the macro creates, and to which it copies the data, have any data in it before the macro saves it? Hence the need to halt the macro before the save.

If no data exists at this stage then we can look to see why that is, since on my test data the data does exist - as indeed it does after the file is saved as a csv file.

If however the data does exist then we need to know the syntax your version of Excel uses when saving a csv file. Clearly it must be slightly different to the macro I suggested since it doesn't work on your system.

Simpler still I suppose, can you create any old workbook with a few cells populated with text data, and record what happens when you save this as a .csv file.

Rgds

Hey Richard,

When this macro is saving the file Upload1.csv it will be overwriting an existing Upload1.csv file all the time. When I put the break in the macro and ran it there was no information populating the cells.

Here is what I get when I record a macro and save it as a csv file. (P.S. I've already tried changing the FileFormat to match below to no avail

Please Login or Register  to view this content.

Originally Posted by mick02

Hey Richard,

When this macro is saving the file Upload1.csv it will be overwriting an existing Upload1.csv file all the time. When I put the break in the macro and ran it there was no information populating the cells.

Here is what I get when I record a macro and save it as a csv file. (P.S. I've already tried changing the FileFormat to match below to no avail

Please Login or Register  to view this content.

Hello Mick,

I find this most odd. It seems therefore that the problem isn't with the save csv aspect, but with the data copy into the added workbook. And I don't experience the same problem running this on my Mac.

I've just noticed that there's a line in there:

Please Login or Register  to view this content.

I'm by no means sure now why I put that in, and because it doesn't seem to affect the way this works on the Mac I've not really thought about it before.

Would you comment that line out, put a ' (single inverted comma) as the first character on the line - or delete the line entirely and try running the macro again.

Let me know,

Rgds

Hey Richard, still the same unfortunately. A blank Upload1.csv file. I'm going to try to record the first part of the macro myself then call the Upload2 macro that you wrote and combine them and then hopefully I'll get the two files that I'm looking for unless you have any suggestions

Originally Posted by mick02

Hey Richard, still the same unfortunately. A blank Upload1.csv file. I'm going to try to record the first part of the macro myself then call the Upload2 macro that you wrote and combine them and then hopefully I'll get the two files that I'm looking for unless you have any suggestions

Hello Mick,

Good idea and worth a try. Let me know how it works.

I'll dig out an old PC and see if I can reproduce the problem you have. It's all so odd since obviously the second file works OK. The only difference is the Transpose element which changes the B2:B?? to A1:K1 or whatever. It might be worth taking the "Transpose:=True" bit out of the code and seeing if that produces a file with data in it.

If it does at least we'll then understand what's causing the problem and then it would be fairly trivial to add a few lines of code to turn the columnar data into linear data on Row1 without using the inbuilt Transpose.

Regards

Richard

Hey Richard,

Even when I removed the transpose = true line it still didn't make any difference, still nothing in the file.

I'll try manually record a macro and see what happens.

Thanks for all your time and help on this.

Richard,

I've modified the macro and now I'm getting information in both sheets. In the CreateCSV1 macro there was a line that read

Please Login or Register  to view this content.

I removed the Sheet1 to

Please Login or Register  to view this content.

and it worked perfectly.

Once again I want to thank you for all your help over the past few days, It is and was much appreciated.

OK there's one or two more things that perhaps you could help me with ...

On Upload1.csv file, cell B2 that says 0714 = My Excel Document.

Is there a way to remove everything after the equals sign so it just leaves a number???

And one last thing ... after the field MyAddress there is another line that says SELF ... is there a way to read everything UP TO the word SELF but not that line???

Still hope people aren't getting too pissed off reading this post!!!!

Thanks

Originally Posted by mick02

OK there's one or two more things that perhaps you could help me with ...

On Upload1.csv file, cell B2 that says 0714 = My Excel Document.

Is there a way to remove everything after the equals sign so it just leaves a number???

And one last thing ... after the field MyAddress there is another line that says SELF ... is there a way to read everything UP TO the word SELF but not that line???

Still hope people aren't getting too pissed off reading this post!!!!

Thanks

Hello Mick. Still not sure why removing the reference to Sheet1 made that difference. Did you have more than one sheet in the original by any chance, one without any data?

Anyway, glad you've got it to work. The following amendment to the first procedure should do the additional stuff you want. Let me know if not.

Please Login or Register  to view this content.

Rgds

Richard,

That is working a treat now. Once again many thanks for your time and help with this macro. It was very very much appreciated.

Take care,

Mick