Appending csv's in Excel 2007

[peterleconte]

I have an 1000 row(records) excel file as follows:

Each row (record) record has 5 columns (fields) one unique numeric key field, two fields are variable - i.e. are potentially in the .csv file, the last two fields are calculated.

I have a daily produced .csv which has fields with the identical field names but not the calculated fields.

As you'd expect not every record from the daily .csv has a value.

Is there any way to create an import routine into the Excel file?

I've wasted a day on Access - I could do this in minutes years ago in dBase, FoxPro but Access?? I can't handle the program at all!

Thanks if anyomne kind enough to look at this.


Peter

 

[Domenic]

The following will import the first field from the .csv file to Column A of the Excel file, the second field to Column B, and the third field to Column C...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Field1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Field2 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Field3 [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]'Change the path accordingly[/color]
    strPath = "C:\Users\Domenic\Desktop\"
    
    [color=green]'Change the workbook name accordingly[/color]
    strFile = "Book1.csv"
    
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    [color=darkblue]Open[/color] strPath & strFile [color=darkblue]For[/color] [color=darkblue]Input[/color] [color=darkblue]As[/color] #1
    
        [color=darkblue]Do[/color] [color=darkblue]Until[/color] EOF(1)
            [color=darkblue]Input[/color] #1, Field1, Field2, Field3
            Cells(NextRow, "A").Value = Field1
            Cells(NextRow, "B").Value = Field2
            Cells(NextRow, "C").Value = Field3
            NextRow = NextRow + 1
        [color=darkblue]Loop[/color]
        
    [color=darkblue]Close[/color] #1
                
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[peterleconte]

Off to buy VBA for Dummies time then....

I'm sure your answer is elegant and spot on.
It doesn't work for me without a few hours investment in understanding the basics of the VBA interace.


Something that I could have written in moments in an ancient database application ( FoxPro) oh well the price of progress!

Appreciate your answer however, thanks.

 

[Domenic]

What do you mean by "it doesn't work for me..."? If you'd like we can take it step by step.

 

[peterleconte]

You're too kind, but it's resolved.

Having found that I needed (!) to activate the developer tab all is clear. If I could write fine and dandy apps in dBase and FoxPro 20 years ago I'm not totally brain dead.

( It's not documented anywhere on the software but Google was my friend. The way that MS produce apps with crass interfaces never ceases to amaze me.)

 

[peterleconte]

Actually there is still a problem.

The code correctly imports the data from the csv and creates a new row for each and every record.

What its not doing is appending new records and updating existing ones. I couldn'y see how the code could, as there's no key index field.

Maybe I have to take the blue pill and ask an access guru....

Thanks for your help Domenic, very kind.

 

[Domenic]

What its not doing is appending new records and updating existing ones.

Somewhat unclear... Can you please clarify?

The code, as it stands, opens the specified CSV file and appends its data to the active sheet of your Excel file. Then it closes the CSV file. When the code is run again, the process repeats with the data from the CSV file being appended to the active sheet of your Excel file. So if the active sheet already contains data, the new data is placed after the old data.

 

[peterleconte]

Sure.

Field1 is a product number, unique.
Field2 is the volume sales
Field3 is narrative
Field4 is a value ( can vary 0
Filed5 is a calculated field ( product of Field2, Field4)

The first time we run the code with just field headers in the exel file all is well.

The next time we run it with fresh data contained in the csv it just appends the rows. (Instead of replacing the values which have changed in the csv)

So... if Field1, row1 had 11 sales and the next time new data was imported, this imported data would be cumulatively updated to say eg 16.

I'd be looking for that value to be replacing the 11 which was the prior result.
-----------------------------------------------------------------
I havent checked to see how the routine copes with varying ranges of data - i.e. if I make field1, rows 1- 500 contain the product codes, the data import should of course update the rows records.

I can't see how Excel can do that. But as my dislike for Access is greater than Excel, if you have a work around.... thank-you!

 

[Domenic]

It should be possible but unfortunately I don't have the time required to come up with a solution. Maybe someone else might be able to help. Also, you may want to start a new thread. This time, though, include a small sample of a CSV file, and a sample of the Excel file after your first importation. Then, provide a sample of a second CSV file, and a sample of the Excel file after your second importation. This way your desired result is clear.

 

[peterleconte]

Thank you for your time Domenic.
I really do appreciate your help, sorry if I wasn't clear.

Its so maddening that software that fitted on 4 floppy disks could be coded to do this in moments but Excel needs too much time. ( If FoxPro ran on Windows 7 I'd code it there, but its legacyware and doesn't!)

Have a nice evening.