Code to split CSV file fields and put in to a table

[dwinn86]

Hello Everyone,

I am trying to create a module in VBA within Access 2010 that when run, will open up a specific .csv file, and will split values up according to a spec I give (where to start, length of field etc) and put the information then in to an Access table.

E.g. I have a file that looks like:

Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6

Can anybody advise me on how I can achieve this task please as I am completely puzzled.

Many thanks in advance,

Dan

 

[James Dudden]

I would link the csv file as a table in access because you can then define how it splits the data e.g. by comma.

Then you could run an update query or use VBA to loop through the linked table and add to your database.

 

[dwinn86]

Hi James,

Thank you for your very quick response.

Ok then, I will try that and see how it goes.

Thank you

Dan

 

[dwinn86]

Hi James,

I am still a little confused as to what I am doing...would you mind explaining this a bit more please?

Thanks,

Dan

 

[James Dudden]

Sorry but I don't use Access 2010 to so I can't give you exact instructions but try this link for some useful tips:
http://www.functionx.com/access/Lesson05.htm