read CSV file and create XLSX file with certain formatting

Hello,

I am look for options how to read a CSV file with a certain structure (see attachment: csv_input.csv) and create a XLSX file from the data in the CSV with a specific formatting (see attachment: result_output.xlsx).

I'm having different CSV files which will always have the same number of columns, but a different number of rows, so I would want something which considers this. Also, the CSV file has some redundant data in the beginning, which I do not want in the result file. I only want to transfer the data starting from "Datum".

Some columns have a non-standard formatting (number or percentage - see result_output.xlsx) and special formatting (first row is bold and underlined and there is a line over the last row). Is it possible to include this in the code?

Thank you very much for your help.

1. Import CSV file ( using Text Import Wizzard)
2. Insert a row at row 1 (you will copy headings from other file on row 1)
3. Copy the formatting from Final file (A2:AA2) to the imported data
4. Clean up some of the fields that don't format correctly (like the dates with decimal points in them, or numbers with multiple decimal points)

I would start with excel open. Open the file from within excel (File / Open go to the proper folder where the file is saved ~ you may need to change the drop down box for file TYPE to ALL FILES(*.*)....it is next to the File Name in excel. The TEXT WIZZARD should open [you can change the "Start import at row to" 11 ~ If your new info always starts at row 11 as you indicated] , use DELIMITED type, NEXT button, put check mark in COMMA box, Next button again and then FINISH button. This will put the data into its own worksheet. Insert a row at row1. Copy formatting over your imported data.

Hi,

thank you for your suggestion. The problem is that I have to make the whole process (much) easier because the userbase consists of people who are seldom using excel and are also older. That's why I was thinking of an automated VBA solution, where you can start the code through a button. Ideally, the user has to punch just one a button which opens a "Open file"-menu and then VBA takes care of the data transfer and formatting.

I am very thankful for any kind of suggestions.

Got it. Here's the code for future reference:

Please Login or Register  to view this content.