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

gkar55

New Member
Joined
Apr 19, 2019
Messages
2
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)
 
Upvote 0
Thank you for looking at this...

Code:
[/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]

 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top