Transforming Data for import into Access 2007

[squirrely]

Hello,

I have countless .csv files in a folder which i want to import into access 2007.
The structure of all files are the same, however in a difficult format for me to rearrange and import into Access 2007 where the access table will make sense.

I have attached the .csv file

I would like the data to look like the excel spreadsheet i have attached.

Cheers,

[Norie]

Do you have code or a method to get the table like that?

The only way I can think of doing it would be to use VBA file I/O to read the file line by line and extract the data.

If you were going to do that then you could miss out Excel and do it in Access, appending the data to a table.

[squirrely]

Hello Norie,

No unfortunately i dont have a code to do that. I manually created that table to show how i would like data to look like.
If it were possible to do this in access - that would be great..i dont know how to do this though...can you help?

[squirrely]

Ignore this post an error

[squirrely]

Ignore this post - an error

[nilem]

Hi Squirrely,
maybe something like this (in a first approximation)

Please Login or Register  to view this content.

[squirrely]

The code works! It actually works! I put the code in a module within one of the .csv files and ran it. That is great!

How would i implement this so it would look at each .csv file in a folder? and put the output into the excel spreadsheet?

[nilem]

Have all csv the same data format?

[squirrely]

Hi Nilem,

Yes they do, the naming stucture of the file is the same also - just incrementing by date.

E-01102012
E-02102012
E-03102012
E-04102012

and so on

Regards,

[nilem]

Ok, let's try
it is assumed that you have a folder with csv files. Save this file (attached) to the folder.
Open the xlsm file and press the green button.

Please Login or Register  to view this content.

[squirrely]

Hi Nilem,

I will try this first thing tomorrow morning and update you on how this went. Many Thanks - update you tomorrow.

Regards,