Import csv into Workbook and format cells with formula to change data

[gkar55]

I have a need to adjust a master excel file to accommodate a new process. I have a csv file that has data in A thru N with D thru K showing times in format h:mm:ss. This csv file is auto generated daily and will need to be imported into a master spreadsheet into a “data” tab keeping previously imported data in the sheet (new data would be added to the next available row below). Here is where I am running into issues. The master file is formatted differently.

• The master file has data in A thru M with F thru M showing time in secs (showing 1800 instead of 0:30:00). I cannot change the csv original file to show anything other than h:mm:ss but need the master sheet to show secs due to lookups of other sheets in the file.
• In the csv, column D and E need to be added together to import into H in the master file
• In the csv, column G and H need to be added together to import into J in the master file
• In the csv there is no date being pulled in. I need to be able to when the csv is imported to generate a date (currently doing this manually based on when the file is run).

Currently right now I am pasting the csv into a temp sheet in the master workbook and using formulas to copy, merge, convert, etc. the data into the “data” tab. Also I am manually entering a date into column A in the master file so that the data has a reference. (referenced in bullet 4 above). I would like to be able to create a macro that will do this much easier and with less steps. I don’t know if all this can be done with 1 macro or multiple of even if macros is the way to go. Any help would be appreciated. Thank you in advance.

 

[sijpie]

This can be easily done with a macro and save you tons of work. Can you post, say, 10 lines from the csv? (if you put the dump between code tags, it will keep the format, see below how to use code tags)

 

[gkar55]

Thank you for looking at this...

[/COLOR][COLOR=#0000cd][SIZE=2][SIZE=2][SIZE=2][SIZE=2] [/SIZE][/SIZE][/SIZE][/SIZE][/COLOR][TABLE="width: 813"]
<colgroup><col><col><col><col><col><col><col span="4"><col><col><col><col></colgroup><tbody>[TR]
[TD]start time[/TD]
[TD]login time[/TD]
[TD]calls[/TD]
[TD]talk time - In[/TD]
[TD]talk time - out[/TD]
[TD]hold[/TD]
[TD]wrap - in[/TD]
[TD]wrap - out[/TD]
[TD]idle[/TD]
[TD]ring[/TD]
[TD]release[/TD]
[TD]Agent ID[/TD]
[TD]Agent Number[/TD]
[TD]Agent Name[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD="align: right"]0:26:49[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0:26:26[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:11[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:03[/TD]
[TD="align: right"]0:00:07[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2762[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD="align: right"]8:30[/TD]
[TD="align: right"]0:02:28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:02:28[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2711[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD="align: right"]9:00[/TD]
[TD="align: right"]0:27:20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0:10:21[/TD]
[TD="align: right"]0:05:08[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:11[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:11:15[/TD]
[TD="align: right"]0:00:20[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2711[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD="align: right"]9:30[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0:22:43[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:22[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:06:34[/TD]
[TD="align: right"]0:00:21[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2711[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0:29:11[/TD]
[TD="align: right"]0:01:47[/TD]
[TD="align: right"]0:01:51[/TD]
[TD="align: right"]0:00:21[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:22[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2711[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD="align: right"]8:30[/TD]
[TD="align: right"]0:26:55[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:06:24[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:10[/TD]
[TD="align: right"]0:15:50[/TD]
[TD="align: right"]0:00:01[/TD]
[TD="align: right"]0:01:29[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2721[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD="align: right"]9:00[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:22:35[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:20[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:06:59[/TD]
[TD="align: right"]0:00:06[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2721[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD="align: right"]9:30[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0:21:21[/TD]
[TD="align: right"]0:01:15[/TD]
[TD="align: right"]0:01:42[/TD]
[TD="align: right"]0:00:32[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:07:39[/TD]
[TD="align: right"]0:00:27[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2721[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2721[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD="align: right"]8:30[/TD]
[TD="align: right"]0:25:59[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0:23:22[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:05:11[/TD]
[TD="align: right"]0:00:21[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:01:42[/TD]
[TD="align: right"]0:00:32[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2725[/TD]
[TD]Hector[/TD]
[/TR]
[TR]
[TD="align: right"]9:00[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:15:59[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:21[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:13:30[/TD]
[TD="align: right"]0:00:10[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2725[/TD]
[TD]Hector[/TD]
[/TR]
[TR]
[TD="align: right"]9:30[/TD]
[TD="align: right"]0:30:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0:16:16[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:13:38[/TD]
[TD="align: right"]0:00:06[/TD]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2725[/TD]
[TD]Hector[/TD]
[/TR]
</tbody>[/TABLE]
[COLOR=#0000cd][SIZE=2][SIZE=2][SIZE=2][SIZE=2] [/SIZE][/SIZE][/SIZE][/SIZE][/COLOR][COLOR=#ff0000]