Changing the structure of the files

Hello members,
I would appreciate if you could help me to change the structure of all my *.csv files to the following format and write them all in a single .xls file.
I tried to solve it by myself, but I didn't know even how to record a macro for this specific case.

INPUT FILE STRUCTURE (*.csv)
2-15-2015 4-03-41 PM.png

OUTPUT FILE STRUCTURE
** for the dates that there is no value (e.g. 30 & 31 Feb), you can leave it blank or fill it with "?" or "-999" as missing value.
2-15-2015 4-05-04 PM.png

Thank you.

Could you post an example CSV file for us to play with - perhaps with dummy data? What is the max number values for any given day? Could it be anything?

Originally Posted by Brendan_Floyde

Could you post an example CSV file for us to play with - perhaps with dummy data? What is the max number values for any given day? Could it be anything?

Sure, I'll update the main post by adding a sample file.
The max number of records in different files are not same and could be anything.
But, the structure of the input files is similar.
Thank you

Just to clarify in your output example the column headings Val1 / val2 are effectively the months?

As your data is nice and square adodb is way quicker than looping through each line. Requires a reference in the VBE go tools / references then tick microsoft active data objects 6.1 library (or the highest / nearest you have) - you only have to do this once.

this code assumes 1334108.csv is saved in the same directory as the file with the macro in.

This just transforms the one input csv file. You mention you have multiple in a folder. It's not clear how you want the data consolidated. Should the values be added together or new line for each input folder? Can you expand the output file example above to show what you want and also perhaps post a second input file?

Please Login or Register  to view this content.

Originally Posted by Brendan_Floyde

This just transforms the one input csv file. You mention you have multiple in a folder. It's not clear how you want the data consolidated. Should the values be added together or new line for each input folder? Can you expand the output file example above to show what you want and also perhaps post a second input file?

2-15-2015 8-17-21 PM.png
is it clear? No header no blank row in between.
Just added another sample file to the original post.
Thank you.

Amended the above for multiple files. Opens a dialogue box for you to select the files in the folder then cycles through and dumps them at the bottom of the table each time. There is no error checking - assumes all the input files are identical.

Please Login or Register  to view this content.

Are all the files the same layout, with same headers etc?

Could you run it again and when it error hit debug
then type ?strfilenamez
This will then tell you the file it errors out on - could you then post that file? Or rerun excluding that file and see it it gets further? I want to work out if it is a particular file that is the issue

Ahh so it's the files where the 1st record value is ? that is causing the issue as MS then thinks that field is text field rather than a value field.?

Hmm thinking cap on .... sure i've come across this before.....

Got the file - replicated the error....working on it. It is the problem I suspected....

added an error handling - 1534001 - has 59 initial records of ? . MS (jet) uses the first 10 records to judge the field type so in this instance it thinks it is a text field - hence the issue. There are other fancier ways of adjusting like adjusting the registry so jet looks at the whole column but this is fast and good enough

Please Login or Register  to view this content.