Import a comma delimited file & match records
I am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one for
every month of the year January, February … December.. This file is called montly_totals.xls and is located in P:\DAM\WI_FTP. There are a total of 200 accounts set up in each of these monthly tabs. Each of these accounts starts with a code in A1 & B1 . These are 4 character codes like below: Code1 Code2 0845 MTRA 1016 BBBB 1017 LEGL 1605 1605 1605 OSLA 7777 0001 7777 0002 Every month I download a comma delimited file named as WI_JAN_2011, WI_FEB_2011 and so on till WI_DEC_2011. This file is located in P:\DAM \WI_FTP This file may contain 10 to 150 accounts with updated information for that specific month. They also have the same codes as above. The format of the comma delimited file is : Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amoun t 0010,BBBB,11,01,Iron works ,Grills , 000000130,0000003448635, Currently every month I have to open each monthly tab and manually copy and paste the correct Information from the comma delimited file into the exact columns in my montly_totals.xls spreadsheet. The columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount I want a macro that would read this comma delimited file and insert these new monthly updated values to the correct account numbers. I was thinking that it could key on Code1 & Code2 fields. Once there is a match between the codes i.e Code1+Code 2 in the montly_totals spreadsheet and the comma delimited file than the macro could insert the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) into seperate cells in the montly_totals.xls. Thanks in advance, Dave |
Import a comma delimited file & match records
If possible pls send me one sample monthly file and one consolidated
file. |
Import a comma delimited file & match records
It happens that Dave formulated :
Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amoun t Is this the first line in the file. If so then I suggest using ADO to grab the data into recordset[s] and write the appropriate values to your worksheet. Each recordset could be unique for each Code# pair, OR you could process each record of a recordset containing all data in the file. If the field names are not the first line in the file then this can be prepended (via VB file I/O functions) so ADO knows what these field names are. Just curious.., why do you not copy the 2 Code# when copying the other data? How do you handle a new month entering the first data onto its sheet? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Import a comma delimited file & match records
Javed, I am sorry I can not load the file to big and contains info I
can not load. Gary, No that is not the first line of data I was trying to show what the data lebels were. the .csv file just contains row after row of data like 0010,BBBB,11,01,Iron works ,Grills , 000000130,0000003448635 Each code pair is unique. So far I have been manuall copying and pasting the data. I entered the initial 200 codes by hand & now I want to be able to compare and insert the updated values in a macro, thanks in advance again. On Feb 15, 10:03*am, GS wrote: It happens that Dave formulated : Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amoun t Is this the first line in the file. If so then I suggest using ADO to grab the data into recordset[s] and write the appropriate values to your worksheet. Each recordset could be unique for each Code# pair, OR you could process each record of a recordset containing all data in the file. If the field names are not the first line in the file then this can be prepended (via VB file I/O functions) so ADO knows what these field names are. Just curious.., why do you not copy the 2 Code# when copying the other data? How do you handle a new month entering the first data onto its sheet? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
All times are GMT +1. The time now is 09:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com