Changing the structure of a csv file

Dear members,

I need your help to change the structure of csv file, A, to B in a new csv file, please.

I tried to record macro but it was not useful. There are some missing values in dataset (blank cells), but I couldn't fill them in format A because of blank cells in Feb and other non-31-day months.

I know how to run the code for the files in a folder, but for this part I need your assistance.
Reagrds,
Mori

[File A]
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4
A5
>> change structure to >>
[File B]
A1
A2
A3
A4
A5
B1
B2
B3
C1
C2
C3

changing structure.png

Please post at least one copy of each CSV, BEFORE.csv and AFTER.csv

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

This does all the formatting in memory so it should be pretty fast.

Please Login or Register  to view this content.

Wow, Thank you for the time. The program is so fast. But there are only two small issues, if you consider it please.

1. The code crawls data by row rather than column. I.e. it reads Jan 1,1950 then Feb 1,1950 instead of Jan 1, 1950 then Jan 2, 1950 ...

2. There are some blank cells, as missing value, other than months with less than 31-day. The code is ignoring the whole months with missing values. (e.g., Jun 1951)
11-23-2015 2-15-30 PM.png
I've used this code after running your code and it has inserted the missing dates. Since it is for 3 columns dataset, so I just inserted a blank column between columns date and value.

Please Login or Register  to view this content.

1) I don't see a problem with #1. It is running row by row so the initial array created is in the monthly format, day by day, but at the end of the macro the data is sorted by date ascending putting the output back in calendar date ascending.

2) The macro skips any cell with NO VALUE. It does not skip 0 value cells. What is the need in that regard?

I have edited one line of code above in red. That seems to fix #2.
Attachment updated as well.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hello again,
I have a question, if you don't mind.

How to do it for multiple files in a folder?
Thanks.

So instead of picking a file at the beginning, you want to select a folder and have it process every Excel file in that folder?

Yes please.
I have actually recorded a macro, and I am trying to add your code to the end of that. Then I am going to add another macro to merge all created files by your code into one workbook.

This version will make the original macro I wrote loop through all CSV files found in a chosen folder:

Please Login or Register  to view this content.

Thank you. I think there is a problem in my side. The program cannot find csv files in the folder.

My bad, a few typos, I've corrected them in the post above and tested, it is finding the CSV files now. Apologies.