Creating a database of sales, whereas each daily sale is a separate CSV file

The data in the CSV files is in a funny format. I had to add some columns and do a piecemeal text-to-columns on the data before importing them.

The report takes data from a To Do directory, this is where the CSV files get collected originally, and it moves them to a Done directory.

Both of these directories are subdirectories of the same root directory.

You tell the program where you want the root directory to be in cell B2 on the control panel sheet. Then provide the names of the To Do and Done directories in cells B3 and B4.

The program looks at each CSV file in the To Do directory, puts the data on the data sheet and then moves the CSV file to the Done directory.

Do you need to parse out the date from the CSV file name and add it to the data?

In cells B3 and 4, don't include the "X:\Dropbox\T4_Praca magistrerska\Copy CCS" - this part is added to the path by the code. Just use "To Do" and "Done." You can change these names to their Polish equivalents as long as you do it in the directory structure and in these two cells.

I'll work on parsing out the date from the file names and get you a version that does this most likely later today.

This turned out to be easier than I thought.

The script works wonderfully and after putting the data into a pivot table I can work miracles. But before we begin with that, there's one more problem to solve: within exactly 190 CSV files there's a value in 'nazwa' (product name) column that contains a comma, so when the scripts reads that value it splits the name into two values in two different columns, thoroughly messing up the final data structure. Either we have to change the values in original CSV's with some grep-like utility or teach the script to skip commas in 'nazwa' column. I will be grateful for any help. ; ]

I've got my hands on another 690 CSV files I've got to assemble but this kind is a little bit trickier. What has to be done during assembling is:
- skip rows 2 and 3
- retain original data format (percentage, decimals separated with comma OR commas changed into dots, negative values)
- and finally create a 'data' column with filename parsed into a date format, just like before

I attach sample data and screens of that comma issue.

I don't think I can help you with this issue. Excel expects the CSV file to use the comma as a delimiter, so when it opens it, it opens it into columns sorted by the delimiter. I can see several ways around this.

One is to scrub the data using another utility such as grep or awk (old Unix man here). Put it into a PERL shell to produce a clean CSV file.

Another is to rename the CSV files with a *.txt extension. Open them, loop through a table of exceptions that you maintain manually, make the substitutions, save the files. rename the file sback to CSV, then open them again and *then* start the program I've already provided.

Or get a cleaner source of data. I am not quite sure what the designer of this system had in mind with this output.

I am looking into that now.

I have another question. If I replace all the commas with dots and then separate the data into columns based on semicolons, would that give me the correct data? I might have a different plan of attack if this is the case.

Does this look correct?

In that case, I have a different way of doing this. I'll respond in the other thread since it has some additional requirements.